CREATE OR REPLACE PROCEDURE "UPDATE_ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_MONTHLY_TB_PROC" AUTHID CURRENT_USER IS
BEGIN
  BEGIN
    /* 1) Merge daily table rows into monthly table */
    MERGE INTO ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_MONTHLY_TB d
     USING (
       SELECT
          -- PKs
          METADATA_REGION,
          SACTION,
          SRECORDTYPE,
          SACCOUNTTYPE,
          SCOMPID,
          STYPE,
          -- Timestamp aggregation
          TRUNC("TIMESTAMP", 'MM') AS "TIMESTAMP",
          -- Summarized columns
          SUM(DOC_COUNT) AS DOC_COUNT,
          -- Top hits columns (all nulls)
          NULL AS INDEX_,
          NULL AS KINTEGRATIONAPPLICATIONDEF,
          NULL AS SAUTHENTICATIONMETHOD,
          NULL AS SINTEGRATIONAPPLICATIONNAME,
          NULL AS SDCID,
          NULL AS SAPIVERSION,
          NULL AS SSTATE,
          NULL AS NRESPONSECODE,
          NULL AS SSERVICETYPE,
          NULL AS SINTEGRATIONAPPLICATIONID,
          NULL AS SINTEGRATIONAPPLICATIONSCRIPTID
       FROM ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB
       WHERE SACCOUNTTYPE = 'PRODUCTION' AND SCOMPID NOT LIKE '%TSTDRV%'
       GROUP BY METADATA_REGION, SACTION, SRECORDTYPE, SACCOUNTTYPE, SCOMPID, STYPE, TRUNC("TIMESTAMP", 'MM')
     ) 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 = 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;
/