Implement an update algorithm for Data Vault in PL / SQL

Question

I'm currently working on modeling a data warehouse using Data Vault modeling. My problem is the following: I want to define for each satellite the last state according to a business key. Here are the elements and the algorithm that I want to put in place:

I have two tables.

The first table (SAT) contains a history of events whose primary key is composed of a business key and a date of update of this business key. This table is powered in real time.

The second table (LAST_SAT) contains the last state of each event whose primary key consists only of a business key. The update date is the maximum update date of the first table. This table is fed once a day.

Here is the algorithm that I want implemented in PL / SQL:

For each satellite pair (SAT), and last state satellite (LAST_SAT):

1. Identify the last update date of LAST SAT (if the return is zero because the table is empty, then value 01/01/1900): MAX_DATE
2. Select the data in the SAT table updated after MAX_DATE.

3. Compare selected data (2) with LAST_SAT data to update LAST_SAT with new and updated data.

   3.a. Delete data from LAST_SAT present in SAT (according to business key)

   3.b. Insert the selected data (2) in LAST_SAT in an orderly fashion.

Here is the program I started writing in PL / SQL, but my level is too low to achieve this.

/* Formatted on 29/07/2019 15:25:07 (QP5 v5.185.11230.41888) */

DECLARE
   v_maxVal       VARCHAR2 (200);
   requete        VARCHAR2 (200);
   TYPE TabCur IS REF CURSOR;
   v_tab_cursor   TabCur;
BEGIN
   FOR sat IN (SELECT table_name, column_name
                 FROM USER_TAB_COLUMNS
                WHERE column_name LIKE '%UPDATE%' AND table_name = 'S_SALE'
                                                                       )
   LOOP

      EXECUTE IMMEDIATE 'SELECT MAX(' ||sat.column_name || ') FROM ' || 'LS_'|| substr(sat.table_name,1,29) INTO v_maxVal;

      requete := 'SELECT * FROM '|| sat.table_name || ' WHERE '|| sat.column_name || ' >= ''' || v_maxVal || '''';

      OPEN v_tab_cursor FOR requete;

      LOOP

            FETCH v_tab_cursor INTO rec;

        EXIT WHEN v_tab_cursor%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(rec.CONT_CONT_CONTRAT_REF);

      END LOOP;

      CLOSE v_tab_cursor;



      --DBMS_OUTPUT.put_line (sat.table_name || ' -> ' || sat.column_name || ' : ' || v_maxVal);

   --DBMS_OUTPUT.put_line (sat.table_name || ' : ' || v_maxVal);

   END LOOP;

END;

Can you help me in my goal?

Thank you in advance,

Guillaume.