---

declare
  DOCUMENT_NAME   constant varchar2(128) := 'GOODS_IN_TRANSIT_BRAZIL_DAILY';
  JIRA_TASK       constant varchar2(128) := 'REPSCML-47';
  l_batch_id      etl.batchId;
begin
  l_batch_id := etl.startBatch(JIRA_TASK || ' : logistic plant fields backfill', etl.BATCH_BLOCK_UPDATE);
  etl.lockDocumentBy(DOCUMENT_NAME, l_batch_id);
  etl.userChainCode(
    p_batch_id      => l_batch_id,
    p_document_name => DOCUMENT_NAME,
    p_description   => 'backfill logisticPlant, logisticPlantName, logisticWarehouseCode; update logisticSite, logisticCorridor, logisticCorridorName',
    p_code          => q'[
update "goodsInTransitBrazilDaily" t
set ( t."logisticPlant"
    , t."logisticPlantName"
    , t."logisticWarehouseCode"
    , t."logisticSite"
    , t."logisticCorridor"
    , t."logisticCorridorName"
    ) =
    ( select lp."logisticPlant"
           , lp."logisticPlantName"
           , case when t."companyCode" = 'BRFTO'
                  then lp."logisticPlant" || ' - ' || lp."logisticPlantName"
             end
           , lc."site"
           , lc."corridor"
           , lc."corridorName"
      from ( select case when t2."incotermsLocation2" = 'MT - RONDONOPOLIS'
                         then 'P60G'
                         else t2."plantId"
                    end as "logisticPlant"
                  , ( select p."sapName1"
                      from   xxwh."mdgSapPlants" p
                      where  p."sapCode" = case when t2."incotermsLocation2" = 'MT - RONDONOPOLIS'
                                                 then 'P60G'
                                                 else t2."plantId"
                                            end
                        and  p."source"  = 'SAP_SA'
                      fetch first row only
                    ) as "logisticPlantName"
             from   "goodsInTransitBrazilDaily" t2
             where  t2.rowid = t.rowid
           ) lp
      left join ( select c."sapCode"
                       , c."site"
                       , c."corridor"
                       , c."corridorName"
                       , row_number() over (partition by c."sapCode" order by c."site") rn
                  from   xxmdg."logisticCorridors" c
                ) lc
        on lc."sapCode" = lp."logisticPlant"
       and lc.rn       = 1
    )
]'
  );
  etl.finalizeBatch(l_batch_id);
end;
/