This discussion is archived
12 Replies Latest reply: Oct 11, 2013 5:31 AM by Haisa.M RSS

Update the record in another row when date is changed

Haisa.M Newbie
Currently Being Moderated

Hi Everybody,

 

As all of you know, In the Apex,  when you create a form with report in the page, you are able to insert and edit data. But when you edit the data, the data will be modified in the same row. In other word, you loose the old data. What I need to do is:

 

I have revised_num field and production_date field.

I want to create a form with report and insert and edit data as is in the apex and insert 0 to the revised num until production date is null.

But when  production date is not null, then from that point, I want to insert data to another row and modify revised num to 1. and I want the revised num be incremented by 1 each time the user modifies the data after the production date is not null.

I don't know where I should start. I appreciate your help.

 

Thanks,

  • 1. Re: Update the record in another row when date is changed
    TexasApexDeveloper Guru
    Currently Being Moderated

    You should look at a trigger in the database then, that fires whenever you update the specific row/column in your table..

     

    Thank you,

     

    Tony Miller

    LuvMuffin Software

  • 2. Re: Update the record in another row when date is changed
    Mike Kutz Expert
    Currently Being Moderated

    This requirement:

    ... and I want the revised num be incremented by 1 each time the user modifies the data after the production date is not null.

    ... if not handled correctly, will put you into a world of trouble.

     

    What database version are you on? (I need all 4 digits)

    EE, SE, or XE?

    Apex version?  Listener version? (and/or .. how do you connect to apex?)

     

    I'll post a 2nd reply regarding the above requirement (to keep things organized).

     

    MK

  • 3. Re: Update the record in another row when date is changed
    Haisa.M Newbie
    Currently Being Moderated

    If I create a trigger, still it is going to update the same row. Doesn't it? It does not insert to another row.

  • 4. Re: Update the record in another row when date is changed
    TexasApexDeveloper Guru
    Currently Being Moderated

    Whoops Missed part of the conversation.. Yes, you will have issues.. Why exactly are you wanting to touch multiple records in an insert?

     

    You have Row 1, when you update it, you POSSIBLY want to update a column in Row 3.. Is this correct?  What relationship do they share?? Shouldn't you have the associated row in another table??

    How exactly will you determine what row to touch and update?

     

    In hindsight, you could use a function call in an after submit process, if you have some sort of key to use in an update statement..

     

     

    Thank you,

     

    Tony Miller

    LuvMuffin Software

  • 5. Re: Update the record in another row when date is changed
    Mike Kutz Expert
    Currently Being Moderated

    (why oh why do I make things more complicated?)

    What I was thinking:

    If Haise is updating a 'revision number', Haise may want to keep track of the data changes.

     

    we'll need Haise to supply more details on that

     

    MK

  • 6. Re: Update the record in another row when date is changed
    Haisa.M Newbie
    Currently Being Moderated

    Yes, your understanding is correct. I need to keep track of changes and also I have a unique number for each row that it will make the record unique.

    Here is the information you asked:

     

    Data base version: EBS  11.2.0.3

    Apex version: 4.2.2

    listener version: 2.0.2

  • 7. Re: Update the record in another row when date is changed
    TexasApexDeveloper Guru
    Currently Being Moderated

    Okay, so will you know WHAT row to update in the table??

     

    You are on row 1, and need to update another row, how would you know what row to update?  is there another column in the row with an id of the row to update??  ( must say at this point, your database design needs some improvement, if this is how you have it implemented..)

     

    Thank you,

     

    Tony Miller

    LuvMuffin Software

  • 8. Re: Update the record in another row when date is changed
    Haisa.M Newbie
    Currently Being Moderated

    Okay let me describe it this way:

     

    I have table which looks like this:

    custom _id ( which is pk and unique)

    report_name.

    custom_report_id  ( fk from another table)

    report_purpose,

    revised_num,

    production_date

     

    Now, I insert data to this table with revised num = '0'. I can modify the data as much as i want until the production_date is null with no problem. When production date is not null and I want to modify the same data, then I need to insert the same data to another row with revised_num='1' and custom_report_id will be the same which we know which data we are talking about.

     

    I hope this is more clear now.

  • 9. Re: Update the record in another row when date is changed
    Mike Kutz Expert
    Currently Being Moderated

    my understanding,

    if date is null, update the row with matching "master_id" and "rev#=0"

    if date is not null, insert with new rev#

     

    If this is correct:

    You will need two tables and a view.

    Your reports will be ran against the view.

    You'll also need to get rid of the 'automatic DML' and replace it with PL/SQL code.

     

    From a coding standpoint:  put all that pl/sql code in a package.

     

    MK

  • 10. Re: Update the record in another row when date is changed
    Haisa.M Newbie
    Currently Being Moderated

    Your understanding is correct but what do you mean by two table and one view. You mean I need to have to tables with the same features and one view?  I actually have two tables which I am getting my fk ( custom report_id)but it just carries id and name and all other in formation are in the second table with revised_num.

  • 11. Re: Update the record in another row when date is changed
    Mike Kutz Expert
    Currently Being Moderated

    let me back up some before I go to far ahead.

    (tony -- double check my logic, please...)

     

    Basically, we will calculate the 'revised number' on-the-fly.  (this is always the preferred method.)

    create table stuff (

      custom_id int primary key

      custom_report_id int, -- FK

      production_date  date,

      date_entered timestamp NOT NULL,

    );

     

    A view will be used to produce the revision number.  (the revision number will need to be a display-only data type)

    select custom_id, custom_report_id, production_date,

      row_number() over (partition by custom_report_id order by date_entered) revised_num

    from stuff;

     

    You'll still need to remove the Automatic DML and use a custom process.

    But, it will be much simpler.

     

    Updating would contain something like this:

    merge into stuff S

    using (select to_number(:p5_custom_id) as custom_id

              ,to_number(:p5_custom_report_id) as custom_report_id

              ,to_date(:p5_production_date, '......') production_date

              -- etc.

             from dual ) N

    on ( S.custom_report_id = N.custom_report_id and S.production_date is null and N.production_date is null)

    when matched then update -- both current and new production_date values must be null to update

      set ....

    when not matched then insert ( .... ) values ( ... );

     

    this is a more simpler idea than what i had earlier.

    do you understand?

     

    MK

  • 12. Re: Update the record in another row when date is changed
    Haisa.M Newbie
    Currently Being Moderated

    I do understand what you are saying and I knew I should create insert and update process but I did not know which way is easier. I will give it try and let you know. Thanks for your reply.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points