---

create or replace package body xxautocdc_autocdc_utils_pkg is
  -- ------------------------------------------
  procedure autocdcLog(p_message      varchar2 := null,
                       p_batch_id     varchar2,
                       p_status       varchar2,
                       p_table_name   varchar2,
                       p_commodity_id varchar2 := null,
                       p_source_id    varchar2 := null,
                       p_propogate_id varchar2 := null) is
  begin
    if p_status != 'Error' then
      insert into xxautocdc.autocdc_log
      (table_name, table_key)
      values (
        p_table_name,
        '{"COMMODITY_ID":'||p_commodity_id||',"SOURCE_ID":'||p_source_id||',"PROPOGATE_ID":'||p_propogate_id||',"BATCH_ID":"'||p_batch_id||'"}'
      );
	else
      insert into xxautocdc.autocdc_log
      (table_name, table_key) values (p_table_name, p_message);
	end if;
  end;
 
  -- ------------------------------------------
  procedure log(p_batch_id varchar2) is
    l_commodity_id varchar2(1000);
    l_source_id    varchar2(1000);
    l_propogate_id varchar2(1000);
  begin
    for curs in (
      select commodity_id, source_id, propogate_id
      from xxautocdc.madab_content_stable
      where batch_id = p_batch_id
    ) loop
      autocdcLog(
        p_batch_id      => log.p_batch_id,
        p_status        => 'Succes',
        p_table_name    => c_table_name,
        p_commodity_id  => curs.commodity_id,
        p_source_id     => curs.source_id,
        p_propogate_id  => curs.propogate_id
      );
    end loop;
  end;
 
  -- ---------------------------------------------------------------
  procedure logIce(p_batch_id varchar2) is
    l_commodity_id varchar2(1000);
    l_source_id    varchar2(1000);
    l_propogate_id varchar2(1000);
  begin
    for curs in (
      select commodity_id, source_id, propogate_id
      from xxautocdc.madab_content_stable_ice
      where batch_id = p_batch_id
    ) loop
      autocdcLog(
        p_batch_id      => logIce.p_batch_id,
        p_status        => 'Succes',
        p_table_name    => c_table_name_ice,
        p_commodity_id  => curs.commodity_id,
        p_source_id     => curs.source_id,
        p_propogate_id  => curs.propogate_id
      );
    end loop;
  end;
 
  -- ---------------------------------------------------------------
  procedure fullUpdateLog(p_period_from date) is
    l_commodity_id varchar2(1000);
    l_source_id    varchar2(1000);
    l_propogate_id varchar2(1000);
  begin
    for curs in (
      select batch_id, commodity_id, source_id, propogate_id
      from xxautocdc.madab_content_stable
      where period_from >= p_period_from
    ) loop
      autocdcLog(
        p_batch_id      => curs.batch_id,
        p_status        => 'Succes',
        p_table_name    => c_table_name,
        p_commodity_id  => curs.commodity_id,
        p_source_id     => curs.source_id,
        p_propogate_id  => curs.propogate_id
      );
    end loop;
  end;
 
  -- ---------------------------------------------------------------
  procedure fullUpdateLogIce(p_period_from date) is
    l_commodity_id varchar2(1000);
    l_source_id    varchar2(1000);
    l_propogate_id varchar2(1000);
  begin
    for curs in (
      select batch_id, commodity_id, source_id, propogate_id
      from xxautocdc.madab_content_stable_ice
      where period_from >= p_period_from
    ) loop
      autocdcLog(
        p_batch_id      => curs.batch_id,
        p_status        => 'Succes',
        p_table_name    => c_table_name_ice,
        p_commodity_id  => curs.commodity_id,
        p_source_id     => curs.source_id,
        p_propogate_id  => curs.propogate_id
      );
    end loop;
  end;
 
  -- ---------------------------------------------------------------
  procedure error(p_message varchar2, p_batch_id varchar2, p_table_name varchar2) is
    pragma autonomous_transaction;
  begin
    autocdcLog(p_message => error.p_message, 
               p_batch_id => error.p_batch_id, 
               p_status => 'Error', 
               p_table_name => p_table_name);
    commit;
  exception
    when others then
      rollback;
  end;
 
  procedure postUpdateWH is
  begin
    begin
      log(p_batch_id => xxwh.etlRuntime.currentBatch);
      commit;
    exception
      when others then
        rollback;
        error(p_message => '{"Error":"'||DBMS_UTILITY.format_error_backtrace||', '||xxwh.etlRuntime.currentBatch||'"}',
              p_batch_id => xxwh.etlRuntime.currentBatch,
              p_table_name => c_table_name
        );
    end;
 
    begin
      logIce(p_batch_id => xxwh.etlRuntime.currentBatch);
      commit;
    exception
      when others then
        rollback;
        error(p_message => '{"Error":"'||DBMS_UTILITY.format_error_backtrace||', '||xxwh.etlRuntime.currentBatch||'"}',
              p_batch_id => xxwh.etlRuntime.currentBatch,
              p_table_name => c_table_name_ice
        );
    end;
  end;
 
  -- --------------------------------------- ------------------------
  procedure fullUpdateWH(p_period_from date) is
  begin
    fullUpdateLog(p_period_from => p_period_from);
    commit;
  exception
    when others then
      error(p_message => '{"Error":"'||DBMS_UTILITY.format_error_backtrace||', Full update failed for '||c_table_name||'"}',
            p_batch_id => 'FULL_UPDATE',
            p_table_name => c_table_name
      );
      rollback;
  end;
 
  -- --------------------------------------- ------------------------
  procedure fullUpdateWHIce(p_period_from date) is
  begin
    fullUpdateLogIce(p_period_from => p_period_from);
    commit;
  exception
    when others then
      error(p_message => '{"Error":"'||DBMS_UTILITY.format_error_backtrace||', Full update failed for '||c_table_name_ice||'"}',
            p_batch_id => 'FULL_UPDATE',
            p_table_name => c_table_name_ice
      );
      rollback;
  end;
  -- --------------------------------------- ------------------------
end;
/


create or replace package body xxwh_xxautocdc_utils_pkg is
  -- ---------------------------------------------------------------
  procedure postUpdateWH is
  begin
    xxautocdc.xxautocdc_autocdc_utils_pkg.postUpdateWH;
  end;
  -- ---------------------------------------------------------------
end;
/