Resetting alteredrecord status in update procedures

[Deskin, Bob]

QUICK was designed to do its updating in the UPDATE procedure. There's a
check that ensures that all PRIMARY, DETAIL, and SECONDARY records that were
changed going in are updated.

There is no direct way to set record status. I see two options for getting
around this. Maybe someone has others.

1) Have the data entered into temporary items. That way you don't get record
status changes. You can check whether you need an update based on whether a
value was entered.

2) Do a re-retrieval after the SQL CALL is successful. This resets all
records. If you don't want the data and want to go directly back to Entry,
you can PUSH ENTRY.

Bob Deskin              
PowerHouse Web Product Manager, Application Development Tools
Cognos Inc. 3755 Riverside Drive, Ottawa ON K1G 4K9 CANADA
bob.deskin@cognos.com (613) 738-1338 ext 7268

-----Original Message-----
From: SAWYER, Anthony [mailto:tony.sawyer@mfi.co.uk]
Sent: February 5, 2002 5:48 AM
To: powerh-l@lists.swau.edu
Subject: Resetting alteredrecord status in update procedures


Good Morning,

We are working on PH8.20D on VMS with an Oracle 8i database.

We have got a screen which retrieves a set of data from the database in a
cluster.  

When any of the data is changed, we would rather use a stored procedure to
update the database than use the update procedure to do this for us.  The
reason for this is that there are several other procedures which need to be
applied to other tables during the update and are not included in the
triggers for the table being updated because of the 'mutating table' issue
you get when referencing the table being updated in a trigger.

We also use other GUI front ends which call the stored procedure in the
database, so we would like to maintain only one procedure rather than code
in many screens.

We have tried to approach this issue by hard-coding the update procedure to
call the stored procedure in the database for every occurence which has an
alteredrecord status set to "Y".  Unfortunately one drawback by doing this
is that the alteredrecord status is not reset to "N" by the stored procedure
and so the screen displays the error "*d* Not all changed records have been
updated." even though the data appears to have been updated correctly.

The update procedure is shown below:

...
set database REORDER_DB
...
procedure UPDATE
begin
    for each REORDER_POINTS
    begin
        if alteredrecord of REORDER_POINTS
        then
        sql call REORDER_POINTS_PKG.ROPADD (SKU of REORDER_POINTS,  &
                                            BRANCH_NO of REORDER_POINTS, &
                                            CURRENT_ROP, OVERIDE_ROP, &
                                            OVERIDE_ENABLED_FLAG,  &
                                            OVERIDE_START_DATE, &
                                            OVERIDE_END_DATE)

    end
end

Has anyone ever managed to reset the alteredrecord status on records in a
clustered screen?

Regards

Tony Sawyer


****************************************************************************
******************************************
The information in this e-mail is confidential and is intended solely for
the use of the individual
or entity to which it is addressed.  Any views or opinions presented are
those of the author 
and do not necessarily represent those of MFI Furniture Group PLC or its
associated companies. 
****************************************************************************
****************************************** 

= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.

This message may contain privileged and/or confidential information.  If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so.  Thank you.