I have APEX page that does updates to a table(SAMPLE_TAB). This table has multiple columns(ID, BADGEID, USERID, NAME, CITY, ZIP, EFTDT, TERMDT, STATUS) ID is distinct number for the records.
BADGEID and USERID are related and if either one record is changed it should automatically create New record and expire old record.
If there is a change in NAME, CITY or ZIP then record update should do.
For Example: See below table
ID BADGEID USERID NAME CITY ZIP EFFDT TERMDT STATUS
1 2345 54623 XYZ NY 00000 8/31/2020 12/31/2199 Active
2 5678 90876 DEF NJ 00001 8/31/2020 12/31/2199 Active
If BADGEID 2345 is changed to 2354 for same USERID(54623) On SEP 1 2020 Then there should be a New Record As below
3 2354 54623 XYZ NY 00000 09/01/2020 12/31/2199 ACTIVE
And update old record to below
1 2345 54623 XYZ NY 00000 8/31/2020 09/01/2020 INACTIVE
I tried below PL/SQL code in update processing but getting error
DECLARE ID_PARM = (SELECT BADGEID FROM SAMPLE_TAB WHERE ID = :P1_ID)
BEGIN IF (:P1_BADGEID = ID_PARM) THEN UPDATE ....... ELSE INSERT and UPDATE
I believe you are looking for something like this. Fill in the "..." with additional columns/page items/conditions: