I'm making a web app in Oracle Apex for a harbor (for my final project). I don't know how to handle a situation. So I made a calendar and I have 2 tables: 1) VEZ table that has ID_VEZ(number), VEZ_NUMBER(number), VEZ_MAX_LENGTH(float), VEZ_STATUS(varchar2, its a radio group with option 1) 'FREE' and option 2) 'TAKEN'. The other table is called BROD and it contains columns ID_BROD(number), BROD_REGISTRATION(varchar2), DATE_OF_ARRIVAL (date) , DATE_OD_DEPARTURE(date), ID_VEZ_FK (number, also in the form as a LOV that shows only ID_VEZ where VEZ_STATUS = 'FREE'. Now my questions are:

1. How to make it so when the form for my table BROD is submitted and the data sent to the database that the VEZ_SATUS of the ID_VEZ_FK that was selected in the form is set to 'TAKEN' again so if I open another form for BROD i cant pick that ID_VEZ_FK again.

2. After that how do I change the VEZ_STATUS to 'FREE' once DATE_OD_DEPARTURE passed and/or the reservation is deleted.

TABLE VEZ describes a Harbor place and TABLE BROD describes a boat that arrived at that place.

I have no idea how to approach this problem. Do I need to do that in the SQL query or make a dynamic action or some third option?

I'm aware that my explanation is underwhelming but I will try to clear any misunderstanding in the comments so please comment away!

CodePudding user response：

As of 1: create a page process which updates vez status:

update vez v set
  v.vez_status = 'TAKEN'
  where v.id_vez = :P1_ID_VEZ_FK;

As of 2: create list of values that displays only free vez numbers:

select v.vez_number as display_value,
       v.id_vez     as return_value
from vez v
where v.status = 'FREE'
order by v.vez_number;

I don't think that in reality you can rely on someone leaving on DATE_OF_DEPARTURE; what if someone dislikes that place and leaves earlier (so that vez is free several days earlier than it was planned) or later (so that vez isn't free on DATE_OF_DEPARTURE)? I think that you should create another form (or reuse existing one, if possible) so that you'd manually free that vez.

But, if you insist, then modify LoV WHERE clause to e.g.

where v.status = 'FREE' 
   or v.date_of_departure < sysdate

On the other hand, you should actually update vez status from "taken" to "free". If you do it manually, no problem. To automate it, create a stored procedure and schedule it using the DBMS_SCHEDULER built-in package.