Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interactive Grid APEX$ROW_STATUS value

B_BrockJul 23 2021

I'm using an interactive grid to record student absences in the database.
The grid is being populated by an sql query with a left outer join in it so that I can list all students, regardless if there is an associated absence in the database already. The source query is included below.
When saving the grid, I wish to insert or update only when the student has been marked absent - no need to insert a record if the student is present. I am therefore using my own PL/SQL code to handle this. That code is also included below.
I have to main questions:

  1. How / when are the values (C, U, D) assigned to APEX$ROW_STATUS? When the Interactive Grid first loads from the DB, what value would APEX$ROW_STATUS have per line? Is a 'C' value assigned only when a a new row has been added via the IG add row button? Is the 'U' value assigned only when something within the row changes?
    The end user will never add a new row into the IG, but rather change the "Presence" column from 'Present' to 'Absent'. When the PL/SQL runs, it should insert into the ATTENDANCE table the information regarding the absence. So, even though an insert is ultimately occurring, from the interactive grid's perspective, is that considered an update? (APEX$ROW_STATUS = 'U')?
  2. In the insert or update statements, what is the name of the piece of data that will be inserted into the database table? Is it the Heading defined for each column in the IG, or should I use the alias declared in the source sql?

--- SOURCE QUERY ---
SELECT
A.JDC_ATTENDANCE_ID,
S.JDC_STUDENT_ID AS STUDENT_ID,
S.LAST_FIRST AS Student,
NVL(A.ATTENDANCE_CODE, (SELECT CODE FROM ADS_CODES WHERE ELEMENT_NAME = 'JDC_ATT_CODE' AND SYSDATE BETWEEN STARTDATE AND NVL(ENDDATE, SYSDATE) AND SORT_ORDER = 1)) AS Presence,
A.NOTE
FROM JDC_STUDENTS S
LEFT OUTER JOIN JDC_ATTENDANCE A ON S.JDC_STUDENT_ID = A.STUDENT_ID AND A.ATTENDANCE_DATE = :P40_ATTENDANCE_DATE
WHERE
EXISTS (SELECT 1 FROM JDC_DETENTIONS d WHERE d.student_id = S.jdc_student_id
AND :P40_ATTENDANCE_DATE BETWEEN d.start_date AND nvl(d.end_date, sysdate) )

--- PL/SQL SAVE PROCESS ---
DECLARE
v_att_date jdc_calendar_days.calendar_date%TYPE;
v_calendar_day_id jdc_calendar_days.jdc_calendar_day_id%TYPE;

BEGIN

v\_att\_date := V('P40\_ATTENDANCE\_DATE');  
v\_calendar\_day\_id := V('P40\_CALENDAR\_DAY\_ID');  

CASE :APEX$ROW\_STATUS  
    WHEN 'C' THEN   
        IF :PRESENCE != 'P' THEN  
            INSERT INTO JDC\_ATTENDANCE ( STUDENT\_ID, ATTENDANCE\_DATE, ATTENDANCE\_CODE, NOTE )  
            VALUES ( :STUDENT\_ID, v\_att\_date, :PRESENCE, :NOTE );  
            --RETURNING rowid INTO :ROWID;  
        END IF;  
    WHEN 'U' THEN  
        UPDATE JDC\_ATTENDANCE  
            SET ATTENDANCE\_CODE  = :PRESENCE,  
                NOTE = :NOTE  
        WHERE JDC\_ATTENDANCE\_ID = :JDC\_ATTENDANCE\_ID;       --rowid  = :ROWID;  
END CASE;  

END;

Comments
Post Details
Added on Jul 23 2021
2 comments
6,022 views