---
Hello everyone,
I'm relatively new to Oracle APEX and currently using version 22.2.1. I have three tables in my database setup:
- **INVENTAIRE** (idInventaire NUMBER, Inv_Date TIMESTAMP, ...)
- **ARTICLE** (idArticle NUMBER, Code_Article VARCHAR2, ...)
- **ARTICLE_HAS_INVENTAIRE** (Inventaire_idInventaire NUMBER, Article_idArticle NUMBER, ...)
My goal is to create a form for operators to plan their periodic inventories. The process involves operators specifying the date for the next inventory and selecting the articles relevant to that inventory.
To achieve this, I've created a form based on the above tables using the following SQL Query:
```sql
SELECT
i.idInventaire,
i.Inv_date,
idArticle,
ar.code_article
FROM
Inventaire i
JOIN
Article_has_Inventaire ai ON i.idInventaire = ai.Inventaire_idInventaire
JOIN
Article ar ON ai.Article_idArticle = ar.idArticle;
```
The `idInventaire` and `idArticle` columns are hidden because I've set up a trigger to automatically increment them. The `Inv_date` is configured as a date picker, and `Code_Article` as a Popup LOV displaying all `Code_Article` options from the ARTICLE table.
I've also added a "Save" button. When clicked, it should insert a new record into the INVENTAIRE table with the specified `INV_DATE`, and for each selected `Code_Article`, new records should be inserted into the ARTICLE_HAS_INVENTAIRE table.
Here's the server-side code I'm using in the button's dynamic action:
```sql
BEGIN
-- Insert a new inventory with the specified date
INSERT INTO Inventaire (Inv_Date)
VALUES (:P_DATE_PICKER);
-- Retrieve the ID of the newly inserted inventory
DECLARE
v_idInventaire NUMBER;
BEGIN
SELECT MAX(idInventaire) INTO v_idInventaire FROM Inventaire;
-- Insert selected articles into Article_has_Inventaire
FOR rec IN (SELECT Code_Article FROM ARTICLE WHERE Code_Article IN (:P_POPUP_LOV))
LOOP
INSERT INTO Article_has_Inventaire (Article_idArticle, Inventaire_idInventaire)
VALUES (rec.Code_Article, v_idInventaire);
END LOOP;
END;
END;
```
when i check my tables , new records ar added to my table INVENTAIRE , but no record is inserted to the ARTICLE_HAS_INVENTAIRE table , does someone can figure out why ?
I would appreciate any guidance or suggestions on improving this setup or addressing any potential issues with my approach. Thank you!