CREATE OR REPLACE PROCEDURE "UPDATE_ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB_PROC" AUTHID CURRENT_USER IS
BEGIN
  /*
      The procedure writes to source SRC table and target ARCHIVE table.
      Ensure that:
          This procedure runs only once at the time.
          No other writes uploads (writes) are running.
          No similar or dependant procedures are running on the same tables.
  */

  DECLARE
    l_limit PLS_INTEGER := 10000000; -- limit how many rows can be added into one batch

  BEGIN
    /* 1) Stage and lock a stable set of rows for this run */
    INSERT INTO STAGE_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB (
      RID, DOC_COUNT, "TIMESTAMP", METADATA_REGION, SACTION, SRECORDTYPE, SACCOUNTTYPE, SCOMPID, STYPE, INDEX_, KINTEGRATIONAPPLICATIONDEF, SAUTHENTICATIONMETHOD, SINTEGRATIONAPPLICATIONNAME, SDCID, SAPIVERSION, SSTATE, NRESPONSECODE, SSERVICETYPE, SINTEGRATIONAPPLICATIONID, SINTEGRATIONAPPLICATIONSCRIPTID
    )
    SELECT
      -- ROWID
      r.ROWID AS RID,
      -- Columns to be summarized
      r.DOC_COUNT,
      -- Timestamp for aggregation
      r."TIMESTAMP",
      -- PKs
      r.METADATA_REGION, r.SACTION, r.SRECORDTYPE, r.SACCOUNTTYPE, r.SCOMPID, r.STYPE,
      -- Top hits columns
      r.INDEX_, r.KINTEGRATIONAPPLICATIONDEF, r.SAUTHENTICATIONMETHOD, r.SINTEGRATIONAPPLICATIONNAME, r.SDCID, r.SAPIVERSION, r.SSTATE, r.NRESPONSECODE, r.SSERVICETYPE, r.SINTEGRATIONAPPLICATIONID, r.SINTEGRATIONAPPLICATIONSCRIPTID
    FROM SRC_OSEARCH_WEBSERVICESEXECUTION_RABI_TB r
    WHERE r.METADATA_AGG_FLAG = 'N' AND ROWNUM <= l_limit;

    /* 2) Mark exactly those staged rows as aggregated */
    UPDATE SRC_OSEARCH_WEBSERVICESEXECUTION_RABI_TB r
    SET r.METADATA_AGG_FLAG = 'Y',
        r.METADATA_AGG_TS = SYSTIMESTAMP
    WHERE r.ROWID IN (SELECT s.RID FROM STAGE_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB s);

    /* 3) Merge stage rows into daily table */
    MERGE INTO ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB d
     USING (
       SELECT
          -- PKs
          METADATA_REGION,
          SACTION,
          SRECORDTYPE,
          SACCOUNTTYPE,
          SCOMPID,
          STYPE,
          -- Timestamp aggregation
          TRUNC("TIMESTAMP") AS "TIMESTAMP",
          -- Summarized columns
          SUM(DOC_COUNT) AS DOC_COUNT,
          -- Top hits columns
          MAX(INDEX_) AS INDEX_,
          MAX(KINTEGRATIONAPPLICATIONDEF) AS KINTEGRATIONAPPLICATIONDEF,
          MAX(SAUTHENTICATIONMETHOD) AS SAUTHENTICATIONMETHOD,
          MAX(SINTEGRATIONAPPLICATIONNAME) AS SINTEGRATIONAPPLICATIONNAME,
          MAX(SDCID) AS SDCID,
          MAX(SAPIVERSION) AS SAPIVERSION,
          MAX(SSTATE) AS SSTATE,
          MAX(NRESPONSECODE) AS NRESPONSECODE,
          MAX(SSERVICETYPE) AS SSERVICETYPE,
          MAX(SINTEGRATIONAPPLICATIONID) AS SINTEGRATIONAPPLICATIONID,
          MAX(SINTEGRATIONAPPLICATIONSCRIPTID) AS SINTEGRATIONAPPLICATIONSCRIPTID
       FROM STAGE_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB
       GROUP BY METADATA_REGION, SACTION, SRECORDTYPE, SACCOUNTTYPE, SCOMPID, STYPE, TRUNC("TIMESTAMP")
     ) src
     ON (
       d.METADATA_REGION = src.METADATA_REGION AND
       d.SACTION         = src.SACTION AND
       d.SRECORDTYPE     = src.SRECORDTYPE AND
       d.SACCOUNTTYPE    = src.SACCOUNTTYPE AND
       d.SCOMPID         = src.SCOMPID AND
       d.STYPE           = src.STYPE AND
       d."TIMESTAMP"     = src."TIMESTAMP"
     )
     WHEN MATCHED THEN UPDATE SET
       d.DOC_COUNT = d.DOC_COUNT + src.DOC_COUNT
     WHEN NOT MATCHED THEN INSERT (
       METADATA_REGION, SACTION, SRECORDTYPE, SACCOUNTTYPE, SCOMPID, STYPE, "TIMESTAMP", DOC_COUNT, INDEX_, KINTEGRATIONAPPLICATIONDEF, SAUTHENTICATIONMETHOD, SINTEGRATIONAPPLICATIONNAME, SDCID, SAPIVERSION, SSTATE, NRESPONSECODE, SSERVICETYPE, SINTEGRATIONAPPLICATIONID, SINTEGRATIONAPPLICATIONSCRIPTID
     ) VALUES (
       src.METADATA_REGION, src.SACTION, src.SRECORDTYPE, src.SACCOUNTTYPE, src.SCOMPID, src.STYPE, src."TIMESTAMP", src.DOC_COUNT, src.INDEX_, src.KINTEGRATIONAPPLICATIONDEF, src.SAUTHENTICATIONMETHOD, src.SINTEGRATIONAPPLICATIONNAME, src.SDCID, src.SAPIVERSION, src.SSTATE, src.NRESPONSECODE, src.SSERVICETYPE, src.SINTEGRATIONAPPLICATIONID, src.SINTEGRATIONAPPLICATIONSCRIPTID
     );

    COMMIT;
  END;
END;
/