insert records from table to a form

thanx flow
it is really very good scenario
but , i'm sorry
i a little confused
could you please help wright the codes above
i'll be very thanking you

I will not be writing your code. If you experience specific problems in understanding the scenario or writing the code , name/describe them and I will be glad to help.

great
i will write my code and post here to review it
but please my question here is :
after calling the function of insert new data , you said i should not commit within the function
ok , now how let the data preview in the screen once its inserted without commit ?
sorry for my misunderstanding

Once you insert records in an Oracle session that records are available within the session it self , but not for other sessions. Within the session they are thus queryable.
Once  a server-side commit is done, that records also are available from other sessions.
So you could do a commit after the replication of data in the function, but from that moment the data are available for every other sessions while the user that initiated the action did not perform a commit on those inserts and also can't choose to not commit those inserts.
So unless you want the behaviour just mentioned , don't do a commit within the function and let the user decide.

ok
now i'm facing problem in using synanom inside the function
i created public synonym in user A , to be use it in user B
i could select data from that synonym
but always it not accept it within the function
it says table or view doesn't exist

here is my function

create or replace FUNCTION Ent_h2002_Exists(pEnt master.tran_no2%TYPE) RETURN integer
 
 IS
  lDummy INTEGER; 
 BEGIN
   SELECT count(*)
   INTO lDummy
   FROM master     --- it is public synonum for table of user oracle A
   where
  year=curr_y
   AND tran_no2 = pent
   and doc_date between st_month and en_month;
 
   RETURN lDummy;
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RETURN 0;
 END Ent_h2002_Exists;

Select allOpen in new window

The problem will likely not be in the synonym but in the grant:
i assumed you granted select access on the table to either a role (that B has) or public.
 
To use a grant  in a function, procedure or package that B ownes the select grant has to be given explicitely on the user B.

so with user A
grant select on table? to B;

yes , it is ok now for the data
but i face another problem with inserting data
look at this procedure please

create or replace
PROCEDURE Get_Mast(D IN VARCHAR2) IS
v_tran_no master.tran_no%type;
v_tr_code master.tr_code%type;
v_year master.year%type;
v_period_no master.period_no%type;
v_doc_date master.doc_date%type;

    Cursor c_ent IS
 select * From master
 Where tran_no2=D  and (doc_date between st_month and en_month)
    and tr_code not in(10,11,13,23,43,90,91,15,66)
   and not (create_alpha_user = 'resty' and tr_code= 12);
   
 Cursor c_dent IS
 select * From gled
 Where 
  tran_no= v_tran_no and
  tr_code=v_tr_code and
   year= v_year and
  period_no = v_period_no and
  doc_date = v_doc_date ;
   
    vk  c_ent%rowtype;
    vd  c_dent%rowtype;
    tr_id integer;
     BEGIN
   select nvl(max(tran_no),0)+1 into tr_id from MASTER_GEN_LEDGER where year=curr_y;
      OPEN c_ent;
        loop
         fetch c_ent into vk;
         exit when c_ent%notfound;
                  
   INSERT INTO master_gen_ledger (YEAR, DOC_DATE, HEAD_DESC, CHK_NO, TRAN_NO2, tran_no,PERIOD_NO, TR_CODE, tr_code_orcl,CREATED_BY, CREATED_DATE, CREATE_ALPHA_USER, APPROVE_FLG, JOUR_BAL_FLG )
   values( curr_y, vk.doc_date, vk.HEAD_DESC, vk.CHK_NO, vk.TRAN_NO2 , tr_id ,vk.PERIOD_NO , 20 , vk.tr_code,'GL_INT' , sysdate, vk.CREATE_ALPHA_USER , 1 , 1 );
  dbms_output.put_line(vk.tran_no2);
   v_tran_no:= vk.tran_no;
  v_tr_code:=vk.tr_code;
   v_year:=vk.year;
   v_period_no:= vk.period_no;
   v_doc_date:= vk.doc_date;

     end loop;
       CLOSE c_ent;
       
   open c_dent;
     loop   
     FETCH c_dent INTO vd;
     EXIT WHEN c_dent%NOTFOUND;
INSERT INTO gen_ledgers (year,period_no,tr_code,TRAN_NO,line_no,acc_no,doc_date,cost_center,describt,debit,credit,l_debit,l_credit,created_by,created_date,create_alpha_user)
values(curr_y, vd.period_no ,20,tr_id,vd.line_no,vd.acc_no,vd.doc_date,vd.cost_center, vd.describt,vd.debit,vd.credit,vd.l_debit,vd.l_credit,'GL_INT' , sysdate, vd.create_alpha_user  );     
     end loop;   
close c_dent;      
              
  EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
      
 	end;

Select allOpen in new window

first of all , my procedure contail inserting the main data only , it works well
but when i added the 2nd cursor to insert the details , it compiled ok , but it gives me error no data found
but the data is there ,
when i select details data for the 5 creteria it gives data
i think i have to print message for each step to discover the error
i don't know how to print message for each step within the procedure
if you look : dbms not working

what do you mean dbms_output is not working ?
what  do you use to test the procedure (sqlplus/plsql-builder/sql-developer/toad ? or in the forms execution it self/ other way) ?

If using sqlplus did you do
set serveroutput on ?
Other tools might need to enable dbms_output
begin
  dbms_output.enable(100000);
  ..

<<when i select details data for the 5 creteria it gives data>>
do you select from gled or  gen_ledgers  ? select from both and determine if the count is the same : it gives you information if 0, n or all detailrecords are inserted  
be sure to be in the same database-session as you executed the procedure.

sorry for delay in replay , cause it was my hollyday yesterday
1-i use oracle sql developer
2-yes , i select from gen_ledger on user A , and from gled on user B , it returne same records
i'm worry about the variables of the second cursor , please ceck it , is it ok , cause at the beginning on the declare those variables are null , they take their values when the 1st cursor fetched the main records

yes now after printing variables values , i got it
you advise me before not to commit within the function
but we forget that the detailes could't be inserted if there is no head record
so , i added commit after the 1st curosr , another one after the 2nd cursor
now after exucuting the procedure , main record where always succesfully inserted
but the detaile no , it give no data found

sorry , it is my mistake
i checked well , i found some data not fitted with the basic tables in user B
so , now it is ok , the inserting is working well
only 4 comments :
1- shall i keep commit within the procedure or you have another solution
2-in the form , is "key_next_item" is the best trigger to call the procedure ?
    cause some times it gives me error message after calling it "functional key problem"
3-what is the command to execute the query for more than one criteria ? cause according            to the structure of the main table , the field tran_no2 is not primary key , they build it as every year has different serial , so to let execute_query working well , i should give it both of than_no2 & year
4-what is the command to cancel the query mode to new , when the trigger checked the entry and not found it , and should be posted manually
thanx alot

1. You can eleminate the commit in the procedure:  
within your current session an inserted   parent -record is "there" , and children can be added to it.  If you make no changes in forms however forms will not ask for a commit : if you need that behaviour see my first post.
2. Depends on what your user interface should look like.  What happens if you use previous key or navigate by using the mouse ? Is that intented ? You could consider adding a button with a when-button_pressed-trigger.
"functional key problem" does not trigger me, is there an exception handling in the trigger if the procedure goes wrong ?
3. Try setting year in the pre-insert trigger same way you set tran_id.
4. when an execute_query gives no records and the block is defined as insertable you will end up at an empty 'NEW'  row automatically : don't you ?
when you want to give a message  either return the information about the existence from the procedure/function and message depending on its value: (you can also skip the execute-query if you know there is no record).
or
check the record-status of the first record of the parent-block after the execute-query : if its empty  message

actually i love your logic , i really like to commmit the new inserted record from the form , not from the database , but i didn't know what do you mean by :
You can eleminate the commit in the procedure:  
do you mean to keep commit for the 1st cursor only in the procedure , not to the 2 one ?

No commit at all in the procedure : try it, you will not need it there.

i hope , but sorry again , the detail records via database will not be inserted if the main record not committed first
sorry for lack of understanding
but really i appricate that i learned alot from you

Please try :  detailrecords can be inserted without a commit .  
I have an application running that insert details of details 6 levels deep for different parent-records without a commit.
Without a commit nobody else (=other session) can insert a child of that parent, but the session where the masterrecords are inserted can do it.

Committing makes changes "fixed":  until then a 'rollback'  undoes all changes ,
 after a commit the changed data are available to other sessions.  

The behaviour in forms is different :
if you insert a record in a form , you can't  select that record in a database procedure because the record was not inserted in the database yet.
However if you use the forms build-in "POST"  the record wil be inserted in the database and will be available for select in a database procedure . Others sessions still can't see it.
If you use the "COMMIT" of forms , all changes in the blocks are "POST"ed  and a database commit is performed. From then change are available to other sessions.

I have an application running that insert details of details 6 levels deep for different parent-records without a commit.
i really loves that
but i don't know to solve it
i think i'm going to split the procedure into 2
1st for the main without commit
2nd for the details without commit
the form will commit
is it ok ?

You can split the procedure , but why do you want to do so ?

<but i don't know to solve it>
what problem should be solved ?

ya , it is how to let the procedure works without commit
what is the logic to let details inserted while the main record not yet commited

I keep repeating : once you insert the master-record you can insert the detailrecords without commit.
You even don't  have to believe me , omit the commit and check for yourself that the procedure stills works. It only can take you minutes.

once you insert the master-record you can insert the detail records without commit.

omit the commit and check for yourself that the procedure stills works

there is a trigger on the details table on insert to check if there is master record or not
if not , it will raise

but any how , either you do any thing or not , i really appreciate your help , i learned alot from you , you are really an expert . the points increased to be 500 ,

sir , you there ?
could you guide me for the logic about your solution ( no commit in the database , in regard with a trigger before insert on the details table ?)

I'm back after an  a days work and an evening out with the members of the project i am in.

-- there is a trigger on the details table  to check -  
if used in the same Oracle-session that trigger will be able to read the parent-record :
only if the trigger uses a procedure with a discrete transaction pragma  or for example a web-service to do the check that check wil occur in a different session and it will not see the parent record you just inserted.
but a straightforward select in the triggerbody wil be no problem

did you try it already ?

Is there a special reason for you using that trigger :  is defining a foreign key constraint on the detail-table not enough ?

yes yes yes
you are right 100 %%%%%%%%%
it works
the mistake was not in the commit
it was in the data itself
great
i really very happy
now one comment only ,
here is my code in the form

 

 if  ent_h2002_exists(:master_gen_ledger.tran_no2) = 1  then
   get_mast(:master_gen_ledger.tran_no2);
     execute_query;   

Select allOpen in new window

i surprise it is saved in the database even there are no commit in the database , nor in the form
think i dislike to save the entry , what i should do ?
i mean , i need to require save through the form , else it should not save

i 'll accept the solution cause it is too great
but still i'm looking for solution of no saving in the form
if you have time to post it i 'll appriciate
thanx

please post a replay to my last comment "avoid auto saving through the form"
thanx

You mean :  you know there have been inserts and you can leave the form without being ask to commit and you checked that the data are still available after leaving the form? (*1)
Then there has to be a explicit or implicit commit somewhere: either in forms or in your dataprocedure.
1. If you change the data in the form after querying and you exit the form without saving do you get the question to save the data ? If not the problem is in the form.
2. in forms-builder : search for commit  (edit/find and replace pl/sql) , or use of the exit_form(do_commit) build in. If not found check for the existence of an  key-exit trigger and inspect the code.  If you use for example a push-button to leave the form, inspect its code.
If it's not in the form check the database side  
3. in the procedure : search for commit.  If not found  add a rollback-statement as the last statement in the procedure (if you made it a function the last statement before the return).
If after adding you can see still the inserted data, you did miss a commit in the databaseprocedure.

*1) The default action of exit-form is ask-commit, and if there is nothing to commit rollback to savepoint.  The behaviout i would expect if you exit the form immediately after the added data are displayed :
a) forms checks if some of the block data are changed
b) because the changes where done in the database and not in forms it decides there are no changes to commit
c) because of b) there wil no question be asked
d) but the data are rollbacked to the state where the form was entered (=savepoint).

i checked it ,
the reocrd inserted in the database once the trigger call the procedur from database
but when i try to change something through the form , it alert for the changes do you want to save it ?
so , shall i add rollback to the procedure as you menstioned lastly ?

Yes, but only temporary for debugging this problem.

when i rollback , nothing queried in the form
then , i test this one : i run the procedure through sql server , it completed , but nothing inserted in the database
than means the problem in the form , as you mentioned above

Ok, then the forms behaviour is at some point not  what I expected.
( i assume you already did the checks in forms I mentioned).

Remove the rollback in the procedure.

Add or change the post-query of block A like I suggested in my first post.
For cause of checking the method , do it without condition.

Check wether you will be asked to commit if there are new records inserted, but you did not change anything in the form.

yes , i did as you mentioned , post_query
i changed one item to itself
but same result , it saved in the database
it quit without asking me to save ,
i tried also to manually change one item , here it asked me to save changes
i select no , but it actually asked to save the new changed data , not the inserted data

1)
Do you mean, you got the question to save, selected no and the inserted data are there after you left the form , and the manually changed item has still the value when inserted ?

2)
<yes , i did as you mentioned , post_query>
Is the item an item marked as updateble in the block ?

3)
<yes , i did as you mentioned , post_query>
Did you also check for commit-processing ?

4)
change the item to itself in a when-new-record-instance trigger on block A, do it conditionally
IF :system.record_status = 'QUERY' THEN
    ..
END IF;

1-when i select no , the inserted data still there , but the manually changed item not saved
2-no , i changed another one , database item
3-yes , there is no commit at all in any part of pl/sql trigger ,
4- i will do tomorrow

add a
message('SYSTEM.FORM_STATUS:' || :SYSTEM.FORM_STATUS,ACKNOWLEDGE);
before and after changing the item to itself  (both in post-query and new-record-instance).
what are the displayed values ?  
We are trying  to achive a "CHANGED" instead of  "QUERY")

The result of 1 (combined with loss of data when issuing a rollback in the procedure) , hints to a commit in the form, but since you already looked for it, I'm thinking what other build-in or action could result in a commit.
could you create a pre-commit trigger with a message in it ?  Are there calls to other database procedures that could have a commit in it.

1- once form open , the status is : new
2-after calling the procedure , the status is : query
3-after performing the procedure the status is : changed
- it send alert to save changes , i select no , the data is existing after quit
4- no message displayed on : pre_commit

4- no message displayed on : pre_commit  
that is only if i didn't change any thing
but when change , it alert

<3-after performing the procedure>
  Does  this mean after changing the item to itself in the post-query trigger ?

>> hate to ask , but you do remove the inserted data from the previous attempt before doing a next or you do choose another testcase? <<
   
next attempt to localize the commit
1)  once the data in the form are shown, check (from another session and or tool if the inserted data are already visible) (if they are, you can omit 2) and 3))  

2) create a key-exit trigger
      message(  ' key-exit_trigger system.form_status:' ||:system.form_status,acknowledge);
      exit_form;  -- to keep the default behaviour

3)  once the commit-question is asked, check (from another session and or tool if the inserted data are already visible)  
   check the message from the key-commit trigger

leave the form without changes

<3-after performing the procedure>
  Does  this mean after changing the item to itself in the post-query trigger ?

yes , exactly

1)  once the data in the form are shown, check (from another session and or tool if the inserted data are already visible) (if they are, you can omit 2) and 3))  

yes , i checked the data from the main form in application (the original purchased application
)

but i worry if there are any trigger in the application on insert , it might be the reason of auto_save , but in same time , i test it : when i run the procedure through the sql server , the code performed succesfully , but the record doesn'i saved in the database

sorry to ask another question , i know i asked too much here , you present too much favors for me , is it ok to open additional issue with additional points ? i feel shame to ask more

while testing the code , you remember i call the procedure through , key_next_item
but key enter dosnet move to next item
i mean , i typed the number i wish to insert it , on key enter , it perform the procedure and the data displayed , when i wish to another enter to move to the next item to change it , but it not , it performed the same procedure agian , as another call for the procedure
shall i put the code in another trigger else of : key_next_item

You may open another question for the next_item.

<yes , i checked the data from the main form in application (the original purchased application
)>  

you did that check in another forms session and the form we are working on was still open ?
and the check showed  that the inserted data where already visable ?
(i keep verifying to avoid going a wrong road)

1) create a function counting the detail records in an AUTONOMOUS_TRANSACTION
    that function will not "see" the inserted detailrecords until the commit an guide us to the place to be (i hope)
change below example to count your detailrecords

create or replace function check_inserted_records (p_id number)
return number
is
pragma AUTONOMOUS_TRANSACTION;
v1 number;
begin
  select count(*) into v1 from table1 where id = p_id; -- change it from table1 to the details-table where the records are insert
  rollback;  -- don't worry : this is doesn't influance your form because of the AUTONOMOUS_TRANSACTION
  return number;
end;
/

show the results the function  before and after the execution of the procedure , in the post-query trigger and in the when-new-record trigger.  
At what point does the result get another value then 0 ?

yes , i investigated in the same session , once in my form while still open , in same time the record apper inserted in the form of application and before closing my form

i tested your function
1- return v1  instead of return number
2-it gives 0 in all triggers , when new record instance , post query
also before & after calling the procedure , always gives 0
but i note the following
1- before calling the procedure , it gives 0 , and nothing display in the form
2-after calling the procedure and before code "execute_query"  , it gives 0 , and the head record only displayed in the form
3-after the code "execute-qeury" , it gives 0 , and now both of head/details are displayed

Lets verify if "session"  means the same for both of us.
If you start 2 different applications there are 2 session.
If you start the same application twice there are 2 sessions.
< i investigated in the same session>
Taking my notion of "session" would you still call  your investigation in the same session ?

Is the the form we are talking about called from the the original purchased application :
if yes you are really in the same session but that does not mean there was a commit if you can query the inserted data in another form, it could mean that the inserted records where not "rolled back" when leaving the form.

So back to a question
you did't answer  previous post
>> hate to ask , but you do remove the inserted data from the previous attempt before doing a next or you do choose another testcase? <<

Or to ask it different if after doing the test without saving and, you leave all applications involved and log in again to your purchased application  and query the detailrecords involved are they still there ?

i agree with you regarding the session
my form designed on same tables of the purchased application
it means if i open my form , and the application form , we are in the same session

>> hate to ask , but you do remove the inserted data from the previous attempt before doing a next or you do choose another testcase? <<

Or to ask it different if after doing the test without saving and, you leave all applications involved and log in again to your purchased application  and query the detailrecords involved are they still there ?

i'm sorry i really didn't understand your question , but i will clear the case to make sure that the data committed already in the database , not only dispayed
just now , i'm in the same form , i just called the procedure , the form still open
i didn't press any key
i go to the sql developer , i select the record , it is there , in the database

And what is the answer to the second question from the previous post ?
(that is to verify -  it is there - , but it was not there before you opened the form).

Also you can execute the function  check_inserted_records from sql-developer ,
in the same situation as you  selected the record. Is the result <> 0 ?

And what is the answer to the second question from the previous post ?
(that is to verify -  it is there - , but it was not there before you opened the form).

could you please simplify it , i didn't get the question , sorry

Also you can execute the function  check_inserted_records from sql-developer ,
in the same situation as you  selected the record. Is the result <> 0 ?

i'll do it tomorrow  , cause the database is in my office @ work

again , i really don't know how to thank your big effort
the issue now become too long , i really appriciate

do you remove the inserted data from the previous attempt before doing a next or do you choose another testcase?

now i think it is more clear
i found i had mistake in the variable of the function
now it gives 0 in all sessions , except after code : execute_query   , it return the numbers of records
i did another thing
i disable execute_query  , then run the form
before & after executing the procedure , no records found in the database at all
nor in the application form
but when i close the form , i select the data in sql , i found it

i tested now to remove record from database
i disabled the statement : execute_query
i create procedure to delete the records , without commit
i called this procedure , with no execute_query , still in same form , check in database
record not found
when i close the form , record found in database
now i add commit to the procedure of delete
i call the procedure after the original procedure of insert , still in same form
check in database , record is found
i can't understand , why the insert  committed in spite of no commit
while the delete not committed in spite of commit

Nice idea that deleting procedure.

But the result does not make sense indeed.
Verify first your procedures are doing what you expect them to do.

add a new program-unit to the form

PROCEDURE message_dmbs_output;
IS
   return_value VARCHAR2(255);
   get_status INTEGER;
BEGIN
   LOOP
   DBMS_OUTPUT.GET_LINE (return_value, get_status);
     IF get_status <>  0 THEN
       EXIT;
     END IF;
     MESSAGE(return_value,acknowledge);
   END LOOP;
END;

add to the trigger before invoking the database procedures
  dbms_output.enable;
 
add to the database procedures a dbms_output informing of the changes immediate after the insert/delete statement
    insert into tablep ...
    dbms_output.put_line(sql%rowcount || ' records of ..tablep.. inserted');  
    insert into tablec ...
    dbms_output.put_line(sql%rowcount || ' records of ..tablec.. inserted');  
   
    delete from tablec ...
    dbms_output.put_line(sql%rowcount || ' records of ..tablec.. deleted');  
   
   
add to the trigger after invoking the database procedures
    message_dmbs_output;
   
Check again    :  are the numbers of inserted/deleted records what you expected ?

wonderfull logic
i did , the rsult is :
when i call the delete procedure before "execute_query" it return 0 records deleted
but when i move it to after "execute_query" it return same number of inserted records
and the data really moved from the database
that means commiting insert works after executing the query , is it ?

<when i call the delete procedure before "execute_query" it return 0 records deleted>
1  you did call the delete procedure after the insert procedure and it did not remove the inserted records ?
or
2 is the call to the inserting procedures in the pre-query trigger : that will be fired by  the execute_query trigger : then deleting 0 records before execute query makes sence.

>>>>>>>>>>>>>>>
remove the commit from the delete procedure
remove the delete procedure call after the execute_query;
message instead the result of : function check_inserted_records  at that place

change exit-form
      message(  ' key-exit_trigger system.form_status:' ||:system.form_status,acknowledge);
     message the result of : function check_inserted_records
     call your delete procedure;
     message_dmbs_output;
     message the result of : function check_inserted_records
     exit_form;  -- to keep the default behaviour

call your delete procedure from sql-developer and commit the result to be sure the start-situation is empty

do the test (don't save/commit)  , leave the forms-application and get the result of check_inserted_records from sql-developer

1  you did call the delete procedure after the insert procedure and it did not remove the inserted records ?

yes , exactly

or
2 is the call to the inserting procedures in the pre-query trigger : that will be fired by  the execute_query trigger : then deleting 0 records before execute query makes sence.

no . it is called through key_next_item

do the test (don't save/commit)  , leave the forms-application and get the result of check_inserted_records from sql-developer

here is the results

message instead the result of : function check_inserted_records  at that place

0 records inserted , in both places , before & after statement execute_query

now , when closing the form :

message(  ' key-exit_trigger system.form_status:' ||:system.form_status,acknowledge);

system status= changed
 

   message the result of : function check_inserted_records

   result = 1

 

  call your delete procedure;
 message_dmbs_output;

   3 records of details deleted
   1 record of master deleted
   
 

  message the result of : function check_inserted_records

   1 record delete
 
the record exist after closing the form

The commit-proces is between the result 0 and result 1.
And not saving  in the form still means not committing (= records exists after closing the form).
Almost becomes a queste to find the commit-proces.

1)
One more to narrow the spot.  (especially because in previous post there where some reports of committed results after the execute_query and in this one (=0 records) not .
After a commit I  sometimes noticed a delay before the committed data are visible to other sessions.

So after the message result immediate after the execute_query add

dbms_lock.sleep(3);  -- to wait for 3 seconds
here same message as before

2)
I would like to be able to look at your code: is the a way to provide information without violating your companies secrecy measures ?
If not can you tell me what triggers are defined on both the master and child block ?

3)  Is there any use of the 'FORMS_DDL'  build-in ?  (executing a DDL statement for example to create a temporary table means an implicit commit)

I would like to be able to look at your code: is the a way to provide information without violating your companies secrecy measures ?
If not can you tell me what triggers are defined on both the master and child block ?

if you are asking for my form , it is ok , i will attach it for you


i fail to attache the file
the extension of the file is not allowed
if you have email , i will send it

try to make it a zip-file or rename it to a *.txt file.

the file should be < 50Mb

see http://support.experts-exchange.com/customer/portal/articles/383720

I will be offline for a few days.

i zipped it , but also fail

i reneame it to txt
check if it is ok
Journal-Entry.txt

Ok , i can read it.

What does the procedure reset_seq do ?

Let's guess:
  execute immediate 'alter sequence ...'   ?

there is one item in the details : gen_ledgers.line_no
this should be serialized
so , i created one sequence in database
and give the name by this code

      select line_seq.nextval into :GEN_LEDGERS.line_no from dual;

then , in every new head record , i will zero the sequance again by the code

reset_seq

create or replace procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                          ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;

Select allOpen in new window

The 'alter sequence' - statement (as almost all other DataDefinitionStatements in Oracle)  does an implicit commit.  

Option 1
If there theres always 1 "MASTER_GEN_LEDGER" record and it is always fetched by execute query started from the next_item_trigger then the solution is simple : move the reset_seq procedure call  to the next_item_trigger and place it before calling the insert-procedure.


Else you have to create another way to serialize :  
option 2 : create a package + package body either as program unit in your form or in the database

create or replace package  line_seq_pck
is
function next return number;
procedure reset;
end;
/

create or replace package body line_seq_pck
is
pck_seq pls_integer := 0;

function next return number
is
begin
  pck_seq := pck_seq + 1;
  return pck_seq;
end;

procedure reset
is
begin
    pck_seq := 0;
end;

end;
/

change the code callling reset_seq and line_seq.nextval  to use the package

option3 : check if you can use the  :system.cursor_record  variable instead of line_seq.nextval  (it will only work if the post_text_item trigger is only fired if the focus is on the detail-record)

ok , i changed it
i used :system.cursor_record
it is ok now

sir , any process ?

<<it is ok now >>   ?  Do you expect something from EE ?

oh sorry
i stay working without checking the process
it is ok now
i really thank

sorry again
now , after it is working well , and i can save records only after i press push button
i notice i can adjust any thing in the master record , but for details records :
 i can't insert any new record ,
that is only in case of running the procedure of insert
i investigate :
1- when i insert new record manually , it works well
2-i investigate the value of system.cursor_record it gives correct on each record
3-i temporary change the code , remove value of system.cursor_record & gives the item "line_no" any number , then it accept all insert & change after calling the procedure

what do you thing the cause of system.cursor_record disabling update or insert

sorry , it is my mistake again
the problem was , i wrongly set the value of "lin_no"  through procedure as taken from the original transaction from the other user
so , when work in the form , some value of that items were more than the value of the cursor record
i corrected it , i gives the value through the procedue as serialiazed number
it is ok now
thanx and sorry again