."I have 2 tables "contracts" and "contracts_history

What i need is to create a button and write code inside it to make me able to move the data from table contracts into contracts_history
.after the end date of the contract. so i can add the new contract and have a history of the contracts for that client

Im using Forms 10g

If there is any possible way to do that.Please tell me about it

Regards

Ahmed

insert contracts_history values
( Select * from contracts(

,Dear Siren
Lets say the
the values for the Contracts_history are"Contract_id,Contract_number,descrption".Can you now write me the code probably so that i can add it to the "when button pressed trigger" thanks

also are you sure that code will work normally from the forms.. because that is my target to do it from the forms not the ISQL page

Waiting for your reply .

insert contracts_history values
( Select * from contracts(

eng. Ahmad

there are many ideas to read from table and inset in another ,, they are depend on your form ,like , read data from table1 according to conditions and desplay if on the form then inset it.

,Please put an image for you form

تم تعديل 13 ديسمبر 201015 سنة بواسطة Sireen

Dear Sireen
I attached 2 Pics , untitled is for the contracts page and untitled1 is for the contract history page

What i need is to move the data from the contracts page into contract history page. so i can add the new contract for the client and have all his old contracts we did with him in the contract history table

Also in title image you will find me signing to a disply item .. i want to add in that display item the number of active contracts for each client. i mean by that "the contracts we have for the client on the contracts page only".Thanks

Looking forward to hearing from you

eng. Ahmad

there are many ideas to read from table and inset in another ,, they are depend on your form ,like , read data from table1 according to conditions and desplay if on the form then inset it.

,Please put an image for you form

untitled.bmp

untitled1.bmp

this is a better img to be able to see the contract_history page

Also the flieds in the contract_history table are exactly the same fields of the Contracts table

untitled1.bmp

acoording to
1- contract table has only one row for each client
2- contranct contains Client_id,Contract_number,descrption , ...
3- contract_history contains Client_id,Contract_number,descrption

in form contract---> execute query where clint_id = ##### then
add a button to --> insert into contract_history values (:Client_id_feild , :Contract_number_feild , :descrption_feild) ; --> then

commit;
then ---> enter the new data for the same clint



or write the cilnt_id in its fiels and in the button write this code
select Contract_number,descrption
into :Contract_number_feild,:descrption_feild
from ontract where clint_ id =:Client_id_feild;

delete from contract where clint_id =:Client_id_feild;

insert contains Client_id,Contract_number,descrption intoontract_history values
(:Client_id_feild , :Contract_number_feild,:descrption_feild) ;


commit ;


then enter the new data

Tell me if it works

تم تعديل 13 ديسمبر 201015 سنة بواسطة Sireen

NO its not working Sireen .. also the client id is hidden on my form . because im using the select max client_id +1 on pre insert trigger .
so no client __ID or Contract _id appears on my form

acoording to
1- contract table has only one row for each client
2- contranct contains Client_id,Contract_number,descrption , ...
3- contract_history contains Client_id,Contract_number,descrption

in form contract---> execute query where clint_id = ##### then
add a button to --> insert into contract_history values (:Client_id_feild , :Contract_number_feild , :descrption_feild) ; --> then

commit;
then ---> enter the new data for the same clint



or write the cilnt_id in its fiels and in the button write this code
select Contract_number,descrption
into :Contract_number_feild,:descrption_feild
from ontract where clint_ id =:Client_id_feild;

delete from contract where clint_id =:Client_id_feild;

insert contains Client_id,Contract_number,descrption intoontract_history values
(:Client_id_feild , :Contract_number_feild,:descrption_feild) ;


commit ;


then enter the new data

Tell me if it works

Ahmad you have to display client_id or contract_num

acoording to what you make a query ??? and how you can sum number of contract for this personr ????

disply the client_id and make if protectedd against updateing

السلام عليكم
بامكانك عمل بروسيجر على مستوى الداتا بيز كالتالي:

Create Or Replace Procedure Historical_Contracts_Info Is
Cursor C Is
Select Contract_No,
       Contract_Name,
       Contract_Date
  From Contracts
 Where Contract_Date < Sysdate;
Begin
For Rec In C Loop
  Insert Into Contracts_History(Contract_No,
                                Contract_Name,
                                Contract_Date)
                         Select Rec.Contract_No,
                                Rec.Contract_Name,
                                Rec.Contract_Date
                          From Contracts                         
                         Where Contract_No = Rec.Contract_No;
 Commit;
 
 Delete Contracts 
  Where Contract_No = Rec.Contract_No;
 Commit;    
End Loop;
End;

ولكي تستغني عن الزر في الفورم بامكانك عمل التشييك بشكل آلي بعمل Job كالتالي:

Declare
 X Number;
Begin
 Sys.Dbms_Job.Submit
   (
     Job        => X
    ,What       => 'Historical_Contracts_Info;'
    ,Next_Date  => To_Date('12/13/2010 16:40:37','mm/dd/yyyy hh24:mi:ss')
    ,Interval   => 'TRUNC(SYSDATE+1)'
    ,No_Parse   => False
   );
:Jobnumber := To_Char(X);
End;

وهكذا..سيتم التشييك على العقود كل يوم وفي حال كانت هنالك عقود منتهية سيتم ادخالها على جدول الـ History.


بالتوفيق.

Thanks Ahmed . But can we change the condition in the where clause from sysdate .. to be only the record that im standing on now . because i want it to be manually. i want the user to do that by himself with each contract renewal process with the client

I mean that i want the user to go by himself to take the ended contract and press the button so the ended contract will go to the contract history and the record will be clear to add the renewed contract

السلام عليكم
بامكانك عمل بروسيجر على مستوى الداتا بيز كالتالي:

Create Or Replace Procedure Historical_Contracts_Info Is
Cursor C Is
Select Contract_No,
       Contract_Name,
       Contract_Date
  From Contracts
 Where Contract_Date < Sysdate;
Begin
For Rec In C Loop
  Insert Into Contracts_History(Contract_No,
                                Contract_Name,
                                Contract_Date)
                         Select Rec.Contract_No,
                                Rec.Contract_Name,
                                Rec.Contract_Date
                          From Contracts                         
                         Where Contract_No = Rec.Contract_No;
 Commit;
 
 Delete Contracts 
  Where Contract_No = Rec.Contract_No;
 Commit;    
End Loop;
End;

ولكي تستغني عن الزر في الفورم بامكانك عمل التشييك بشكل آلي بعمل Job كالتالي:

Declare
 X Number;
Begin
 Sys.Dbms_Job.Submit
   (
     Job        => X
    ,What       => 'Historical_Contracts_Info;'
    ,Next_Date  => To_Date('12/13/2010 16:40:37','mm/dd/yyyy hh24:mi:ss')
    ,Interval   => 'TRUNC(SYSDATE+1)'
    ,No_Parse   => False
   );
:Jobnumber := To_Char(X);
End;

وهكذا..سيتم التشييك على العقود كل يوم وفي حال كانت هنالك عقود منتهية سيتم ادخالها على جدول الـ History.


بالتوفيق.

.Thanks Ahmed for your efforts to help.. the code is working probably now

Regards

Thanks Ahmed . But can we change the condition in the where clause from sysdate .. to be only the record that im standing on now . because i want it to be manually. i want the user to do that by himself with each contract renewal process with the client

I mean that i want the user to go by himself to take the ended contract and press the button so the ended contract will go to the contract history and the record will be clear to add the renewed contract

السلام عليكم
بامكانك عمل بروسيجر على مستوى الداتا بيز كالتالي:

Create Or Replace Procedure Historical_Contracts_Info Is
Cursor C Is
Select Contract_No,
       Contract_Name,
       Contract_Date
  From Contracts
 Where Contract_Date < Sysdate;
Begin
For Rec In C Loop
  Insert Into Contracts_History(Contract_No,
                                Contract_Name,
                                Contract_Date)
                         Select Rec.Contract_No,
                                Rec.Contract_Name,
                                Rec.Contract_Date
                          From Contracts                         
                         Where Contract_No = Rec.Contract_No;
 Commit;
 
 Delete Contracts 
  Where Contract_No = Rec.Contract_No;
 Commit;    
End Loop;
End;

ولكي تستغني عن الزر في الفورم بامكانك عمل التشييك بشكل آلي بعمل Job كالتالي:

Declare
 X Number;
Begin
 Sys.Dbms_Job.Submit
   (
     Job        => X
    ,What       => 'Historical_Contracts_Info;'
    ,Next_Date  => To_Date('12/13/2010 16:40:37','mm/dd/yyyy hh24:mi:ss')
    ,Interval   => 'TRUNC(SYSDATE+1)'
    ,No_Parse   => False
   );
:Jobnumber := To_Char(X);
End;

وهكذا..سيتم التشييك على العقود كل يوم وفي حال كانت هنالك عقود منتهية سيتم ادخالها على جدول الـ History.


بالتوفيق.