create or replace package PACK_ACN_IMPORT is

  -- Author  : MARIUS MUTU
  -- Created : 4/10/2019 4:00:22 PM
  -- Purpose : IMPORT RORIS-ROA

  Procedure import_roris_job(ttLastGenerationTime date default null);

  procedure import_roris(ttLastGenerationTime date default null);

end PACK_ACN_IMPORT;
/
CREATE OR REPLACE PACKAGE BODY "PACK_ACN_IMPORT" is

  -- 25.09.2019
  -- import_roris: import ips_vessel_types

  -- 11.02.2020
  -- import_roris
  -- Nu import nimic inainte de 01.01.2020
  -- Am schimbat in ips_voyages.generation (data declaratiei) cu notificati (data avizare) pentru ca erau incurcate
  -- Am facut modificarea pentru convoaiele incepand cu 01.01.2020 sa nu afectez rapoartele pe anii precedenti

  -- 28.02.2020
  -- import_roris
  -- ips_voyage_locks + chamber_nr, chamber_name

  -- 04.03.2020
  -- import_roris
  -- ips_voyage_locks - tin cont ca vms_id poate fi null daca nu este completat in RORIS (nu se stie de ce nu este completat la unele tranzitari)

  -- 22.04.2020
  -- import_roris
  -- nu mai sterg din ips_voyage_members. mi-a dat eroare ca mai exista referinte in ips_voyage_members_vanzari

  -- 08.05.2020
  -- import_roris
  -- sterg din ips_cargoes daca id nu mai este in rorissql, in loc de vms_id

  -- 16.06.2020
  -- import_roris
  -- nu sterg din ips_voyage_locks id-urile care incep cu '*NUSTERGE'

  -- 28.07.2021
  -- import_roris
  -- nu sterg din ips_berthings id-urile care incep cu '*NUSTERGE'

  -- 14.12.2022
  -- import_roris
  -- ips_berthings.tip (1=port, 2=dana asteptare, 9=altele)

  -- 08.04.2024
  -- import_roris
  -- ips_vessels, ips_voyages, ips_cargoes + .country_id

  -------------------------------------------------
  -- Creeaza si lanseaza job IMPORTRORIS_JOB cu executia imediata
  -------------------------------------------------
  Procedure import_roris_job(ttLastGenerationTime date default null) is
  begin
    dbms_scheduler.create_job(job_name            => 'IMPORT_RORIS_JOB',
                              job_type            => 'STORED_PROCEDURE',
                              job_action          => 'PACK_ACN_IMPORT.IMPORT_RORIS',
                              number_of_arguments => 1,
                              start_date          => systimestamp,
                              end_date            => null,
                              repeat_interval     => '',
                              enabled             => false,
                              auto_drop           => true,
                              comments            => 'Lanseaza import din RORIS asincron.');

    DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE(job_name          => 'ACN.IMPORT_RORIS_JOB',
                                         argument_position => 1,
                                         argument_value    => sys.anydata.convertDate(ttLastGenerationTime));

    dbms_scheduler.enable(name => 'ACN.IMPORT_RORIS_JOB');

  end import_roris_job;

  procedure import_roris(ttLastGenerationTime date default null) is
    -- ttLastGenerationTime data de la care se preiau date din RORIS (implicit 6 luni in urma = data curenta - 180 zile)
    ltLastGenerationTime date;
  begin

    ltLastGenerationTime := case
                              when ttLastGenerationTime is null then
                               trunc(sysdate) - 30
                              else
                               ttLastGenerationTime
                            end;

    -- Nu import nimic inainte de 01.01.2020
    -- Am schimbat in ips_voyages.generation (data declaratiei) cu notificati (data avizare) pentru ca erau incurcate
    -- Am facut modificarea pentru convoaiele incepand cu 01.01.2020 sa nu afectez rapoartele pe anii precedenti
    ltLastGenerationTime := greatest(ltLastGenerationTime, to_date('01012020', 'ddmmyyyy'));

    pinfo(tcmesaj   => '1. Import data >= ' ||
                       to_char(ttLastGenerationTime,
                               'dd/mm/yyyy hh24:mi:ss'),
          tclocatia => 'import_roris');

    merge into ips_contacts a
    using (Select trim(id) as id,
                  trim(substr(acronym, 1, 6)) as acronym,
                  trim(name) as name
             From IPS_CONTACTS@RORISSQL) b
    on (a.id = b.id)
    when not matched then
      insert (id, acronym, name) values (b.id, b.acronym, b.name);

    pinfo(tcmesaj   => '2. ips_contacts ' || sql%rowcount ||
                       ' inregistrari noi',
          tclocatia => 'import_roris');

    merge into ips_countries a
    using (Select trim(id) as id,
                  trim(acronym) as acronym,
                  trim(name) as name
             From IPS_COUNTRIES@RORISSQL) b
    on (a.id = b.id)
    when not matched then
      insert (id, acronym, name) values (b.id, b.acronym, b.name);

    pinfo(tcmesaj   => '3. ips_countries ' || sql%rowcount ||
                       ' inregistrari noi',
          tclocatia => 'import_roris');

    merge into ips_goods a
    using (Select trim(id) as id,
                  trim(name) as name,
                  trim(alternativ) as alternativ,
                  trim(dangerous) as dangerous,
                  trim(nstr_code) as nstr_code,
                  trim(nstr_name) as nstr_name,
                  trim(code) as code,
                  trim(imoclass) as imoclass,
                  trim(imoclass_name) as imoclass_n,
                  '2007' as standard
             From IPS_RISGOODS@RORISSQL) b
    on (a.id = b.id)
    when not matched then
      insert
        (id,
         name,
         alternativ,
         dangerous,
         nstr_code,
         nstr_name,
         code,
         imoclass,
         imoclass_n,
         standard)
      values
        (b.id,
         b.name,
         b.alternativ,
         b.dangerous,
         b.nstr_code,
         b.nstr_name,
         b.code,
         b.imoclass,
         b.imoclass_n,
         b.standard);

    pinfo(tcmesaj   => '4. ips_goods ' || sql%rowcount ||
                       ' inregistrari noi',
          tclocatia => 'import_roris');

    merge into ips_vessels a
    using (Select trim(id) as id,
                  vtmis_id,
                  trim(name) as name,
                  lbd,
                  length,
                  breadth,
                  trim(ctt_id) as ctt_id,
                  gross_tonn,
                  horsepower,
                  trn,
                  trim(risvtp_id) as vtp_id,
                  trim(country_id) as country_id
             From IPS_VESSELS@RORISSQL) b
    on (a.id = b.id)
    when matched then
      update
         set name       = b.name,
             lbd        = b.lbd,
             length     = b.length,
             breadth    = b.breadth,
             ctt_id     = b.ctt_id,
             gross_tonn = b.gross_tonn,
             horsepower = b.horsepower,
             trn        = b.trn,
             vtp_id     = b.vtp_id,
             vtmis_id   = b.vtmis_id,
             country_id = b.country_id
    when not matched then
      insert
        (id,
         vtmis_id,
         name,
         lbd,
         length,
         breadth,
         ctt_id,
         gross_tonn,
         horsepower,
         trn,
         vtp_id,
         country_id)
      values
        (b.id,
         b.vtmis_id,
         b.name,
         b.lbd,
         b.length,
         b.breadth,
         b.ctt_id,
         b.gross_tonn,
         b.horsepower,
         b.trn,
         b.vtp_id,
         b.country_id);

    pinfo(tcmesaj   => '5. ips_vessels ' || sql%rowcount ||
                       ' inregistrari noi/modificate',
          tclocatia => 'import_roris');

    merge into ips_vessel_types a
    using (Select trim(id) as id, trim(name) as name
             From IPS_RIS_VES_TYPES@RORISSQL) b
    on (a.id = b.id)
    when not matched then
      insert (id, name) values (b.id, b.name);

    pinfo(tcmesaj   => '6. ips_ris_ves_types ' || sql%rowcount ||
                       ' inregistrari noi',
          tclocatia => 'import_roris');

    merge into ips_route_points a
    using (Select trim(id) as id,
                  vtmis_id,
                  trim(name) as name,
                  TRIM(SUBSTR(name, 1, 6)) as acronym,
                  cnl_id
             From IPS_ROUTE_POINTS@RORISSQL) b
    on (a.id = b.id)
    when matched then
      update
         set vtmis_id = b.vtmis_id,
             name     = b.name,
             acronym  = b.acronym,
             cnl_id   = b.cnl_id
    when not matched then
      insert
        (id, vtmis_id, name, acronym, cnl_id)
      values
        (b.id, b.vtmis_id, b.name, b.acronym, b.cnl_id);

    pinfo(tcmesaj   => '7. ips_route_points ' || sql%rowcount ||
                       ' inregistrari noi/modificate',
          tclocatia => 'import_roris');

    -- voyages
    merge into ips_voyages a
    using (select trim(id) as id,
                  generation as notificati,
                  trim(mtr_name) as mtr_name,
                  trim(ctt_id) as ctt_id,
                  trim(vms_id) as vms_id,
                  trim(convoy_name) as convoy_nam,
                  trim(declaration) as declaratio,
                  notificati as generation,
                  trim(notificat2) as notificat2,
                  trim(origin) as origin,
                  trim(destinatio) as destinatio,
                  state,
                  trim(country_id) as country_id
             from ips_voyages@RORISSQL
            where notificati >= ltLastGenerationTime) b
    on (a.id = b.id)
    when matched then
      update
         set generation = b.generation,
             mtr_name   = b.mtr_name,
             ctt_id     = b.ctt_id,
             vms_id     = b.vms_id,
             convoy_nam = b.convoy_nam,
             declaratio = b.declaratio,
             notificati = b.notificati,
             notificat2 = b.notificat2,
             origin     = b.origin,
             destinatio = b.destinatio,
             state      = b.state,
             country_id = b.country_id
    when not matched then
      insert
        (id,
         generation,
         mtr_name,
         ctt_id,
         vms_id,
         convoy_nam,
         declaratio,
         notificati,
         notificat2,
         origin,
         destinatio,
         state,
         country_id)
      values
        (b.id,
         b.generation,
         b.mtr_name,
         b.ctt_id,
         b.vms_id,
         b.convoy_nam,
         b.declaratio,
         b.notificati,
         b.notificat2,
         b.origin,
         b.destinatio,
         b.state,
         b.country_id);

    pinfo(tcmesaj   => '8. ips_voyages ' || sql%rowcount ||
                       ' inregistrari noi/modificate',
          tclocatia => 'import_roris');

    merge into ips_voyage_members a
    using (select trim(vm.id) as id,
                  trim(vm.vye_id) as vye_id,
                  trim(vm.vsl_id) as vsl_id,
                  trim(vm.ctt_id) as ctt_id,
                  vm.dirty_tank,
                  trim(vm.rstart_id) as rstart_id,
                  trim(vm.rfin_id) as rfin_id,
                  vm.distance,
                  vm.distance_cdmn as distance_c,
                  vm.cnt_mnvr
             From IPS_VOYAGE_MEMBERS@RORISSQL vm
             JOIN IPS_VOYAGES@RORISSQL v
               ON vm.vye_id = v.id
            Where v.notificati >= ltLastGenerationTime) b
    on (a.id = b.id)
    when matched then
      update
         set vye_id     = b.vye_id,
             vsl_id     = b.vsl_id,
             ctt_id     = b.ctt_id,
             dirty_tank = b.dirty_tank,
             rstart_id  = b.rstart_id,
             rfin_id    = b.rfin_id,
             distance   = b.distance,
             distance_c = b.distance_c,
             cnt_mnvr   = b.cnt_mnvr
    when not matched then
      insert
        (id,
         vye_id,
         vsl_id,
         ctt_id,
         dirty_tank,
         rstart_id,
         rfin_id,
         distance,
         distance_c,
         cnt_mnvr)
      values
        (b.id,
         b.vye_id,
         b.vsl_id,
         b.ctt_id,
         b.dirty_tank,
         b.rstart_id,
         b.rfin_id,
         b.distance,
         b.distance_c,
         b.cnt_mnvr);

    pinfo(tcmesaj   => '9. ips_voyage_members ' || sql%rowcount ||
                       ' inregistrari noi/modificate',
          tclocatia => 'import_roris');

    -- sterg inregistrarile care nu mai sunt in RORIS
/*    insert into ips_ids_temp
      (idc)
      (select v1.id
         from (select vm.id as id
                 From IPS_VOYAGE_MEMBERS vm
                 JOIN IPS_VOYAGES v
                   ON vm.vye_id = v.id
                Where v.generation >= ltLastGenerationTime) v1
         left join (select trim(vm.id) as id
                     From IPS_VOYAGE_MEMBERS@RORISSQL vm
                     JOIN IPS_VOYAGES@RORISSQL v
                       ON vm.vye_id = v.id
                    Where v.notificati >= ltLastGenerationTime) v2
           on v1.id = v2.id
        where v2.id is null);*/

/*    delete from ips_cargoes where vms_id in (select idc from ips_ids_temp);*/
    delete from ips_cargoes where id in (select c1.id
      from ips_cargoes c1
      join ips_voyage_members vm
        on c1.vms_id = vm.id
      join ips_voyages v
        ON vm.vye_id = v.id
      left join ips_cargoes@rorissql c2
        on c1.id = trim(c2.id)
     Where v.generation >= ltLastGenerationTime and c2.id is null);

    pinfo(tcmesaj   => '10. ips_cargoes ' || sql%rowcount ||
                       ' inregistrari sterse',
          tclocatia => 'import_roris');

   /*
   -- nu mai sterg din ips_voyage_members. mi-a dat eroare ca mai exista referinte in ips_voyage_members_vanzari
   delete from ips_voyage_members
     where id in (select idc from ips_ids_temp);
    pinfo(tcmesaj   => '11. ips_voyage_members ' || sql%rowcount ||
                       ' inregistrari sterse',
          tclocatia => 'import_roris');
*/
    delete from ips_ids_temp;

    merge into ips_cargoes a
    using (select trim(c.id) as id,
                  trim(c.vms_id) as vms_id,
                  trim(c.risgds_id) as gds_id,
                  c.container,
                  trim(c.source) as source,
                  trim(c.cty_id) as cty_id,
                  trim(c.destination) as destinatio,
                  c.quantity,
                  trim(c.unit) as unit,
                  trim(c.country_id) as country_id
             From IPS_CARGOES@RORISSQL c
             join IPS_VOYAGE_MEMBERS@RORISSQL vm
               ON c.vms_id = vm.id
             JOIN IPS_VOYAGES@RORISSQL v
               ON vm.vye_id = v.id
            Where v.notificati >= ltLastGenerationTime) b
    on (a.id = b.id)
    when matched then
      update
         set vms_id     = b.vms_id,
             gds_id     = b.gds_id,
             container  = b.container,
             source     = b.source,
             cty_id     = b.cty_id,
             destinatio = b.destinatio,
             quantity   = b.quantity,
             unit       = b.unit,
             country_id = b.country_id
    when not matched then
      insert
        (id,
         vms_id,
         gds_id,
         container,
         source,
         cty_id,
         destinatio,
         quantity,
         unit,
         country_id)
      values
        (b.id,
         b.vms_id,
         b.gds_id,
         b.container,
         b.source,
         b.cty_id,
         b.destinatio,
         b.quantity,
         b.unit,
         b.country_id);

    pinfo(tcmesaj   => '12. ips_cargoes ' || sql%rowcount ||
                       ' inregistrari noi/modificate',
          tclocatia => 'import_roris');

    -- exista tranzite la care la ecluza ovidiu, impingatorul trece cu o barja prin camera 1, si cu alta barja prin camera 2
    -- astfel impingatorul apare de 2 ori, prin cate o camera
    -- cheie primara este id (ecluzare), vms_id (membru convoi), chamber_nr (camera ecluza)
    merge into ips_voyage_locks a
    using (Select trim(vl.id) as id,
                  trim(vl.vye_id) as vye_id,
                  VL."DATE" as ddate,
                  trim(vl.rpt_id) as rpt_id,
                  vl.op_min,
                  vl.op_prep,
                  trim(vl.id_lock) as id_lock,
                  trim(vl.lock_name) as lock_name,
                  vl.chamber_nr,
                  vl.chamber_name,
                  trim(vl.fk_voyage_member) as vms_id
             From IPS_VOYAGE_LOCK@RORISSQL vl
             JOIN IPS_VOYAGES@RORISSQL v
               ON vl.vye_id = v.id
            Where v.notificati >= ltLastGenerationTime) b
    on (a.id = b.id and NVL(a.vms_id, 'X') = NVL(b.vms_id, 'X') and a.chamber_nr = b.chamber_nr)
    when matched then
      update set a."date" = b.ddate, a.rpt_id = b.rpt_id, a.op_min = b.op_min, a.op_prep = b.op_prep
    when not matched then
      insert
        (id, vye_id, "date", rpt_id, op_min, op_prep, id_lock, lock_name, chamber_nr, chamber_name, vms_id)
      values
        (b.id,
         b.vye_id,
         b.ddate,
         b.rpt_id,
         b.op_min,
         b.op_prep,
         b.id_lock,
         b.lock_name,
         b.chamber_nr,
         b.chamber_name,
         b.vms_id);

    pinfo(tcmesaj   => '13. ips_voyage_locks ' || sql%rowcount ||
                       ' inregistrari noi/modificate',
          tclocatia => 'import_roris');

    -- sterg inregistrarile care nu mai sunt in RORIS
    delete from ips_voyage_locks
     where id not like '*NUSTERGE%' and id || NVL(vms_id,'X') || chamber_nr in (select v1.id
                    from (select vl.id || NVL(vl.vms_id, 'X') || chamber_nr as id
                            from ips_voyage_locks vl
                            join ips_voyages v
                              on vl.vye_id = v.id
                           where v.generation >= ltLastGenerationTime) v1
                    left join (select trim(vl.id) || NVL(trim(vl.fk_voyage_member), 'X') || trim(vl.chamber_nr) as id
                                From IPS_VOYAGE_LOCK@RORISSQL vl
                                JOIN IPS_VOYAGES@RORISSQL v
                                  ON vl.vye_id = v.id
                               Where v.notificati >= ltLastGenerationTime) v2
                      on v1.id = v2.id
                   where v2.id is null);

    pinfo(tcmesaj   => '14. ips_voyage_locks ' || sql%rowcount ||
                       ' inregistrari sterse',
          tclocatia => 'import_roris');

    merge into ips_berthings a
    using (Select trim(id) as id,
                  arrival_time as arrival_ti,
                  trim(ves_id) as ves_id,
                  max(tip) as tip,
                  max(trim(voy_id)) as voy_id,
                  max(departure_time) as departure_,
                  max(trim(rpt_id)) as rpt_id,
                  max(trim(ctt_id)) as ctt_id
             From IPS_BERTHINGS@RORISSQL
            where arrival_time >= ltLastGenerationTime
            group by id, arrival_time, ves_id) b
    on (a.id = b.id and a.ves_id = b.ves_id and a.arrival_ti = b.arrival_ti)
    when matched then
      update
         set voy_id     = b.voy_id,
             departure_ = b.departure_,
             rpt_id     = b.rpt_id,
             ctt_id     = b.ctt_id,
             tip        = b.tip
    when not matched then
      insert
        (id, arrival_ti, voy_id, departure_, rpt_id, ctt_id, ves_id, tip)
      values
        (b.id,
         b.arrival_ti,
         b.voy_id,
         b.departure_,
         b.rpt_id,
         b.ctt_id,
         b.ves_id,
         b.tip);

    pinfo(tcmesaj   => '15. ips_berthings ' || sql%rowcount ||
                       ' inregistrari noi/modificate',
          tclocatia => 'import_roris');

    -- sterg inregistrarile care nu mai sunt in RORIS
    delete from ips_berthings
     where id not like '*NUSTERGE%' and
           id || ves_id || to_char(arrival_ti, 'yyyymmddhh24miss') in
           (select v1.id || v1.ves_id ||
                   to_char(v1.arrival_ti, 'yyyymmddhh24miss') as id
              from (select id, ves_id, arrival_ti
                      from ips_berthings
                     where arrival_ti >= ltLastGenerationTime) v1
              left join (select trim(id) as id,
                               trim(ves_id) as ves_id,
                               arrival_time as arrival_ti
                          from ips_berthings@RORISSQL
                         where arrival_time >= ltLastGenerationTime) v2
                on v1.id = v2.id
               and v1.ves_id = v2.ves_id
               and v1.arrival_ti = v2.arrival_ti
             where v2.id is null);

    pinfo(tcmesaj   => '16. ips_berthings ' || sql%rowcount ||
                       ' inregistrari sterse',
          tclocatia => 'import_roris');

    merge into ips_routes a
    using (Select VM.RSTART_ID AS RPT_ID,
                  VM.RFIN_ID AS RPT_ID_HAS,
                  TRIM(SUBSTR(R1.NAME, 1, 49)) || '-' ||
                  TRIM(SUBSTR(R2.NAME, 1, 50)) AS NAME,
                  R.ID,
                  MAX(ROUND(NVL(VM.DISTANCE, 0) / 1000, 2)) AS DISTANTA
             FROM IPS_VOYAGE_MEMBERS VM
             JOIN IPS_VOYAGES v
               ON vm.vye_id = v.id
             JOIN IPS_ROUTE_POINTS R1
               ON VM.RSTART_ID = R1.ID
             JOIN IPS_ROUTE_POINTS R2
               ON VM.RFIN_ID = R2.ID
             LEFT JOIN IPS_ROUTES R
               ON VM.RSTART_ID = R.RPT_ID
              AND VM.RFIN_ID = R.RPT_ID_HAS
            Where v.generation >= ltLastGenerationTime
            GROUP BY VM.RSTART_ID,
                     VM.RFIN_ID,
                     TRIM(SUBSTR(R1.NAME, 1, 49)) || '-' ||
                     TRIM(SUBSTR(R2.NAME, 1, 50)),
                     R.ID) b
    on (a.id = b.id)
    when matched then
      update set a.distanta = b.distanta
    when not matched then
      insert
        (rpt_id, rpt_id_has, name, distanta, acronym)
      values
        (b.rpt_id,
         b.rpt_id_has,
         b.name,
         b.distanta,
         DBMS_RANDOM.STRING('U', 6));

    pinfo(tcmesaj   => '17. ips_routes ' || sql%rowcount ||
                       ' inregistrari noi/modificate',
          tclocatia => 'import_roris');
    COMMIT;
    -- Raise_Application_Error (-20343, 'Eroare de test!');
    pinfo(tcmesaj => '18. COMMIT IMPORT FINALIZAT', tclocatia => 'import_roris');
  end import_roris;

end PACK_ACN_IMPORT;
/