create or replace PROCEDURE            "UPDATE_ON_DEMAND_COST_AND_USAGE_ARCHIVES_PROC" 
    AUTHID CURRENT_USER
IS


BEGIN
  -- Daily aggregation by DAY
  MERGE INTO ARCHIVE_OCI_COST_AND_USAGE_DAILY_TB d
  USING (
    SELECT
      AVAILABILITYZONE,
      BILLINGACCOUNTID,
      CHARGEDESCRIPTION,
      PRICINGUNIT,
      REGIONID,
      REGIONNAME,
      RESOURCEID,
      RESOURCETYPE,
      SERVICECATEGORY,
      SERVICENAME,
      SKUID,
      SUBACCOUNTID,
      SUBACCOUNTNAME,
      CONSUMEDUNIT,
      X_COMPARTMENTID,
      X_COMPARTMENTNAME,
      LISTUNITPRICE,
      TRUNC(CHARGEPERIODSTART) AS CHARGEPERIODSTART,  -- Day-level truncation
      SUM(EFFECTIVECOST) AS EFFECTIVECOST,
      SUM(LISTCOST) AS LISTCOST,
      SUM(PRICINGQUANTITY) AS PRICINGQUANTITY,
      SUM(CONSUMEDQUANTITY) AS CONSUMEDQUANTITY,
      SUM(X_ATTRIBUTEDUSAGE) AS X_ATTRIBUTEDUSAGE,
      SUM(X_ATTRIBUTEDCOST) AS X_ATTRIBUTEDCOST
    FROM SRC_OCI_COST_AND_USAGE_TB
    WHERE AGGREGATED_DATE IS NULL
    GROUP BY
      AVAILABILITYZONE, BILLINGACCOUNTID, CHARGEDESCRIPTION, PRICINGUNIT, REGIONID, REGIONNAME, RESOURCEID, 
      RESOURCETYPE, SERVICECATEGORY, SERVICENAME, SKUID, SUBACCOUNTID, SUBACCOUNTNAME,
      CONSUMEDUNIT, X_COMPARTMENTID, X_COMPARTMENTNAME, LISTUNITPRICE, 
      TRUNC(CHARGEPERIODSTART)
  ) src
  ON (
    d.AVAILABILITYZONE = src.AVAILABILITYZONE AND
    d.BILLINGACCOUNTID = src.BILLINGACCOUNTID AND
    d.CHARGEDESCRIPTION = src.CHARGEDESCRIPTION AND
    d.PRICINGUNIT = src.PRICINGUNIT AND
    d.REGIONID = src.REGIONID AND
    d.REGIONNAME = src.REGIONNAME AND
    d.RESOURCEID = src.RESOURCEID AND
    d.RESOURCETYPE = src.RESOURCETYPE AND
    d.SERVICECATEGORY = src.SERVICECATEGORY AND
    d.SERVICENAME = src.SERVICENAME AND
    d.SKUID = src.SKUID AND
    d.SUBACCOUNTID = src.SUBACCOUNTID AND
    d.SUBACCOUNTNAME = src.SUBACCOUNTNAME AND
    d.CONSUMEDUNIT = src.CONSUMEDUNIT AND
    d.X_COMPARTMENTID = src.X_COMPARTMENTID AND
    d.X_COMPARTMENTNAME = src.X_COMPARTMENTNAME AND
    d.CHARGEPERIODSTART = src.CHARGEPERIODSTART
  )
  WHEN MATCHED THEN
    UPDATE SET
      d.EFFECTIVECOST = d.EFFECTIVECOST + src.EFFECTIVECOST,
      d.LISTCOST = d.LISTCOST + src.LISTCOST,
      d.CONSUMEDQUANTITY = d.CONSUMEDQUANTITY + src.PRICINGQUANTITY,
      d.PRICINGQUANTITY = d.PRICINGQUANTITY + src.PRICINGQUANTITY,
      d.X_ATTRIBUTEDUSAGE = d.X_ATTRIBUTEDUSAGE + src.X_ATTRIBUTEDUSAGE,
      d.X_ATTRIBUTEDCOST = d.X_ATTRIBUTEDCOST + src.X_ATTRIBUTEDCOST
  WHEN NOT MATCHED THEN
    INSERT (
      AVAILABILITYZONE, BILLINGACCOUNTID, CHARGEDESCRIPTION, PRICINGUNIT, REGIONID, REGIONNAME, RESOURCEID,
      RESOURCETYPE, SERVICECATEGORY, SERVICENAME, SKUID, SUBACCOUNTID, SUBACCOUNTNAME, CONSUMEDUNIT,
      X_COMPARTMENTID, X_COMPARTMENTNAME, LISTUNITPRICE, CHARGEPERIODSTART, EFFECTIVECOST,
      LISTCOST, PRICINGQUANTITY, CONSUMEDQUANTITY, X_ATTRIBUTEDUSAGE, X_ATTRIBUTEDCOST
    )
    VALUES (
      src.AVAILABILITYZONE, src.BILLINGACCOUNTID, src.CHARGEDESCRIPTION, src.PRICINGUNIT, src.REGIONID, 
      src.REGIONNAME, src.RESOURCEID, src.RESOURCETYPE, src.SERVICECATEGORY, src.SERVICENAME, src.SKUID, 
      src.SUBACCOUNTID, src.SUBACCOUNTNAME, src.CONSUMEDUNIT, src.X_COMPARTMENTID,
      src.X_COMPARTMENTNAME, src.LISTUNITPRICE, src.CHARGEPERIODSTART, src.EFFECTIVECOST, src.LISTCOST, 
      src.PRICINGQUANTITY, src.CONSUMEDQUANTITY, src.X_ATTRIBUTEDUSAGE, src.X_ATTRIBUTEDCOST
    );

  -- Monthly aggregation by MONTH
  MERGE INTO ARCHIVE_OCI_COST_AND_USAGE_MONTHLY_TB m
  USING (
    SELECT
      AVAILABILITYZONE,
      BILLINGACCOUNTID,
      CHARGEDESCRIPTION,
      PRICINGUNIT,
      REGIONID,
      REGIONNAME,
      RESOURCEID,
      RESOURCETYPE,
      SERVICECATEGORY,
      SERVICENAME,
      SKUID,
      SUBACCOUNTID,
      SUBACCOUNTNAME,
      CONSUMEDUNIT,
      X_COMPARTMENTID,
      X_COMPARTMENTNAME,
      LISTUNITPRICE,
      TRUNC(CHARGEPERIODSTART, 'MM') AS CHARGEPERIODSTART,  -- Month-level truncation
      SUM(EFFECTIVECOST) AS EFFECTIVECOST,
      SUM(LISTCOST) AS LISTCOST,
      SUM(PRICINGQUANTITY) AS PRICINGQUANTITY,
      SUM(CONSUMEDQUANTITY) AS CONSUMEDQUANTITY,
      SUM(X_ATTRIBUTEDUSAGE) AS X_ATTRIBUTEDUSAGE,
      SUM(X_ATTRIBUTEDCOST) AS X_ATTRIBUTEDCOST
    FROM SRC_OCI_COST_AND_USAGE_TB
    WHERE AGGREGATED_DATE IS NULL
    GROUP BY
      AVAILABILITYZONE, BILLINGACCOUNTID, CHARGEDESCRIPTION, PRICINGUNIT, REGIONID, REGIONNAME, RESOURCEID, 
      RESOURCETYPE, SERVICECATEGORY, SERVICENAME, SKUID, SUBACCOUNTID, SUBACCOUNTNAME,
      CONSUMEDUNIT, X_COMPARTMENTID, X_COMPARTMENTNAME, LISTUNITPRICE, 
      TRUNC(CHARGEPERIODSTART, 'MM')
  ) src
  ON (
    m.AVAILABILITYZONE = src.AVAILABILITYZONE AND
    m.BILLINGACCOUNTID = src.BILLINGACCOUNTID AND
    m.CHARGEDESCRIPTION = src.CHARGEDESCRIPTION AND
    m.PRICINGUNIT = src.PRICINGUNIT AND
    m.REGIONID = src.REGIONID AND
    m.REGIONNAME = src.REGIONNAME AND
    m.RESOURCEID = src.RESOURCEID AND
    m.RESOURCETYPE = src.RESOURCETYPE AND
    m.SERVICECATEGORY = src.SERVICECATEGORY AND
    m.SERVICENAME = src.SERVICENAME AND
    m.SKUID = src.SKUID AND
    m.SUBACCOUNTID = src.SUBACCOUNTID AND
    m.SUBACCOUNTNAME = src.SUBACCOUNTNAME AND
    m.CONSUMEDUNIT = src.CONSUMEDUNIT AND
    m.X_COMPARTMENTID = src.X_COMPARTMENTID AND
    m.X_COMPARTMENTNAME = src.X_COMPARTMENTNAME AND
    m.CHARGEPERIODSTART = src.CHARGEPERIODSTART
  )
  WHEN MATCHED THEN
    UPDATE SET
      m.EFFECTIVECOST = m.EFFECTIVECOST + src.EFFECTIVECOST,
      m.LISTCOST = m.LISTCOST + src.LISTCOST,
      m.CONSUMEDQUANTITY = m.CONSUMEDQUANTITY + src.PRICINGQUANTITY,
      m.PRICINGQUANTITY = m.PRICINGQUANTITY + src.PRICINGQUANTITY,
      m.X_ATTRIBUTEDUSAGE = m.X_ATTRIBUTEDUSAGE + src.X_ATTRIBUTEDUSAGE,
      m.X_ATTRIBUTEDCOST = m.X_ATTRIBUTEDCOST + src.X_ATTRIBUTEDCOST
  WHEN NOT MATCHED THEN
    INSERT (
      AVAILABILITYZONE, BILLINGACCOUNTID, CHARGEDESCRIPTION, PRICINGUNIT, REGIONID, REGIONNAME, RESOURCEID,
      RESOURCETYPE, SERVICECATEGORY, SERVICENAME, SKUID, SUBACCOUNTID, SUBACCOUNTNAME, CONSUMEDUNIT,
      X_COMPARTMENTID, X_COMPARTMENTNAME, LISTUNITPRICE, CHARGEPERIODSTART, EFFECTIVECOST,
      LISTCOST, PRICINGQUANTITY, CONSUMEDQUANTITY, X_ATTRIBUTEDUSAGE, X_ATTRIBUTEDCOST
    )
    VALUES (
      src.AVAILABILITYZONE, src.BILLINGACCOUNTID, src.CHARGEDESCRIPTION, src.PRICINGUNIT, src.REGIONID, 
      src.REGIONNAME, src.RESOURCEID, src.RESOURCETYPE, src.SERVICECATEGORY, src.SERVICENAME, src.SKUID, 
      src.SUBACCOUNTID, src.SUBACCOUNTNAME, src.CONSUMEDUNIT, src.X_COMPARTMENTID,
      src.X_COMPARTMENTNAME, src.LISTUNITPRICE, src.CHARGEPERIODSTART, src.EFFECTIVECOST, src.LISTCOST, 
      src.PRICINGQUANTITY, src.CONSUMEDQUANTITY, src.X_ATTRIBUTEDUSAGE, src.X_ATTRIBUTEDCOST
    );

  -- Update source to mark as aggregated
  UPDATE SRC_OCI_COST_AND_USAGE_TB
     SET AGGREGATED_DATE = SYSDATE
   WHERE AGGREGATED_DATE IS NULL;

  COMMIT;
END;
