How to reproduce the code of the process : Form - Automatic Row Processing (DML)

Question

Since one of my last post about iterating trough apex items (here's the link), I had to create a Process which allow me to loop through my items and do multiple insert.

Here's my process code :

BEGIN
FOR i IN apex_application.g_f01.FIRST..apex_application.g_f01.LAST
LOOP
    INSERT INTO vue_articles
(
    nom_article, 
    fk_categorie_article,
    fk_amortissement, 
    prix_unitaire_article,
    pk_fk_etage_bureau,
    commentaire_article 
    )
VALUES
(
    :P24_NOM_ARTICLE, 
    :P24_FK_CATEGORIE_ARTICLE, 
    :P24_FK_AMORTISSEMENT,
    TO_NUMBER(:P24_PRIX_UNITAIRE_ARTICLE,'FM999G999G999G999G990D00C'),
    :P24_PK_FK_ETAG_BUREAU, 
    apex_application.g_f01(i)
    );
END LOOP;
END;

Everything's working fine, BUT there's one problem remaining : there's no constraint check, let me explain. Usually, when I click on the basic button to create one row on my form, if my inputs are empty or some values are too high or too low, I would be stuck and would see a pop-up error message which shows me the errors, just like this :

I guess, this is because the basic CREATE button perform a Submit Page action with a Database Action of SQL INSERT Action. This will then call the default Process : Form - Automatic Row Processing (DML), this one :

So, this leads me to my main question : How can I reproduce the code of this automatic default process to be able to do the same check of the constraint, the values, etc... With my actual custom process, if the users do a type of insert that call this custom process with false values, they will not see an error message, and the insert will not execute. So, they don't have any informations on what's going on.

Is it possible to reproduce the code or how can I adapt the code of my process to check every inputs and fields of my form before the insert process ?

Thank you again for your time, do not hesitate to ask question for furthers details,

Thomas

EDIT :

---

First of all, I have a table of items

CREATE TABLE inv_tb_article(
   pk_article INT,
   date_ajout_article DATE DEFAULT SYSDATE CONSTRAINT ct_nn_date_ajout_article NOT NULL,
   nom_article VARCHAR(50) CONSTRAINT ct_nn_nom_article NOT NULL,
   prix_unitaire_article NUMBER(7,2) CONSTRAINT ct_nn_prix_unitaire_article NOT NULL,
   montant_amorti_article REAL,
   prix_net_article REAL,
   fk_amortissement INT,
   fk_categorie_article INT CONSTRAINT ct_nn_fk_categorie_article NOT NULL,
   commentaire_article VARCHAR(50),
   CONSTRAINT ct_pk_article PRIMARY KEY(pk_article),
   CONSTRAINT ct_fk_amortissement FOREIGN KEY(fk_amortissement) REFERENCES inv_tb_amortissement(pk_amortissement),
   CONSTRAINT ct_fk_categorie_article FOREIGN KEY(fk_categorie_article) REFERENCES inv_tb_categorie(pk_categorie),
   CONSTRAINT ct_ck_prix_unitaire_article CHECK(prix_unitaire_article BETWEEN 0 AND 100000)
);
ALTER INDEX ct_pk_article RENAME TO ind_pk_article;
CREATE INDEX ind_fk_amortissement ON inv_tb_article(fk_amortissement);
CREATE INDEX ind_fk_categorie_article ON inv_tb_article(fk_categorie_article);
CREATE INDEX ind_nom_article ON inv_tb_article(nom_article);

I also have a view which takes values from other different tables, and the insert is made on the view :

CREATE OR REPLACE VIEW VUE_ARTICLES AS 
  select PK_ARTICLE, 
    DATE_AJOUT_ARTICLE, 
    NOM_ARTICLE, 
    FK_CATEGORIE_ARTICLE, 
    FK_AMORTISSEMENT, 
    PRIX_UNITAIRE_ARTICLE,
    MONTANT_AMORTI_ARTICLE, 
    PRIX_NET_ARTICLE, 
    PK_FK_SERVICE, 
    PK_FK_ETAGE_BUREAU,
    CODE_SERVICE,
    code_active_directory,
    fk_batiment,
    nom_departement,
    commentaire_article,
    ctrl_aj_date,
    ctrl_aj_user,
    ctrl_mo_date,
    ctrl_mo_user,
    ctrl_mo_jeton,
    nom_categorie
 from   INV_TB_ARTICLE
 INNER JOIN inv_tb_posseder ON inv_tb_article.pk_article = inv_tb_posseder.pk_fk_article
 INNER JOIN inv_tb_service ON inv_tb_posseder.pk_fk_service = inv_tb_service.pk_service
 INNER JOIN inv_tb_departement ON inv_tb_service.fk_departement = inv_tb_departement.pk_departement
 INNER JOIN inv_tb_etage_bureau ON inv_tb_posseder.pk_fk_etage_bureau = inv_tb_etage_bureau.pk_etage_bureau
 INNER JOIN inv_tb_batiment ON inv_tb_etage_bureau.fk_batiment = inv_tb_batiment.pk_batiment
 INNER JOIN inv_tb_categorie ON inv_tb_article.fk_categorie_article = inv_tb_categorie.pk_categorie;

I have an INSTEAD OF, but cannot paste it here because it's way too big, but nothing very specific, consider it a basic INSTEAD OF trigger.

Is there another way of checking validations ? Maybe something like a Javascript function which display error under the fields like in the first picture, or like a "freezed" or "locked" validate button that can't be pressed until you solve the errors on your field ?

---

UPDATE 2

Sooo, I created a validation function in JavaScript. I have a button which display an apex.message.confirm, if the user press "OK", the process of inserting is launching.

My validation is checking if the values of the fields are empty and if the Price is between 1 and 99'999.99

Here's the code of the button :

apex.message.clearErrors();

var chkErr = 0;
var arr = [
   'P24_NOM_ARTICLE', 
   'P24_FK_CATEGORIE_ARTICLE',
   'P24_BATIM', 
   'P24_PK_FK_ETAG_BUREAU', 
   'P24_PRIX_UNITAIRE_ARTICLE'
];
var arrLabel = [
    "Intitulé de l'article",
    "Catégorie",
    "Bâtiment",
    "Étage - bureau",
    "Prix unitaire"
];

//Check des NULL
for (var i in arr) {
  if ($v(arr[i]).length == 0) {
    apex.message.showErrors([
      {
        type: apex.message.TYPE.ERROR,
        location: ["inline", "page"],
        pageItem: arr[i],
        message: arrLabel[i] + " doit contenir une valeur",
        unsafe: false
      }
    ]);
    chkErr = 1;
  } 
}
var str = $v("P24_PRIX_UNITAIRE_ARTICLE");
//2 lines below remove the 'CHF' format mask currency
str = str.slice(0, -3); 
if (parseFloat(str.replace(/[^\d\.]/g,'')) > 99999.99 || parseFloat(str.replace(/[^\d\.]/g,''))<1){
    apex.message.showErrors([{
        type: apex.message.TYPE.ERROR,
        location: ["inline", "page"],
        pageItem: "P24_PRIX_UNITAIRE_ARTICLE",
        message: "Prix unitaire n'est pas entre les valeurs min et max.",
        unsafe: false}
    ]);
    chkErr = 1;
}


if ( chkErr == 0 ) { 
  /* Custom dynamic action call when no error occurred */
  apex.message.confirm("Vous êtes sur le point d'insérer "+ apex.item('P24_NB_MULT').getValue() + " articles, confirmez-vous que les données insérées sont correctes ?", function(okPressed) {
    if (okPressed) {
        $.event.trigger("insertionMultipleComm");
    }
});
}

Now, it's working, just like in the first picture above.

Answer 1

The downside of using apex_item is that you have to do all the work yourself in pl/sql. It seems this question is specifically about validations, so I'll focus on that. The solution is to use a validation and loop through your apex_application.g_f01 in that validation and call apex_error.add_error for any errors you encounter. If there are errors then the page process will not be executed.

Create a validation of type "Function body returning Error text" and do something like this - note that this is not your code, but just an example:

DECLARE
BEGIN
  FOR i IN 1 .. apex_application.g_f01.COUNT LOOP
    IF VALIDATE_CONVERSION(APEX_APPLICATION.G_F04(i) AS NUMBER) = 0 THEN
        apex_error.add_error (
            p_message          => 'Salary must be a number',
            p_display_location =>  apex_error.c_inline_in_notification);
    END IF;   
    -- add other validations for other columns if needed. 
  END LOOP; 
  RETURN NULL;
END;

Now, in addition to validations and dml processes, there is another feature that you're missing if you do everything manually and that is the lost update detection. User 1 opens the form and goes for a coffee, then user 2 opens the form on the same data, makes some changes and saves. Then user 1 comes back, makes some changes on the data he already opened and overwrites the changes that user 2 made. You don't have to code for that, but realize that this is a real-world problem. In apex you could do this using collections with the md5_checksum attribute. Here is a forum entry that explains how to solve it