-- C:\Users\dgajic\Desktop\IRN3923_FINORA\des\v3\pra_pka_finora.pkb
--
-- Generated for Oracle 10g on Fri Oct 04  14:48:11 2024 by Server Generator 6.5.96.5.6
 

PROMPT Creating Package Body 'PRA_PKA_FINORA'
CREATE OR REPLACE PACKAGE BODY PRA_PKA_FINORA IS
-- Designer PL/SQL Block

  url_m spka_parametri_prijepisa.staza%TYPE;
  url2_m spka_parametri_prijepisa.staza%TYPE;
  data_m CLOB;
  odgovor_m CLOB;
  domval_sifra_m VARCHAR2(100) := pra_rec_zaj.vrijednost_parametra ('0001');
  log_filename_m VARCHAR2(100);
  pkaprifinodg_id_m NUMBER;

FUNCTION receipts_receiptmethod
 (vpla_id_p IN s_vplacanja.id%TYPE
 )
 RETURN VARCHAR2
 IS
  opis_l VARCHAR2 (240);
  vppla_sifra_l s_vpplacanja.sifra%TYPE;
  zmpsusvpla_sifra_l mzmp_vpla_sus.sifra%TYPE;
BEGIN
/*
'ReceiptMethod' kao podatak za Receipts endpont za FINORA:
ISTRATECH: AZZ ISTRATECH GOTOVINA, AZZ IST KARTICE - ZABA, AZZ IST KARTICE - ERSTE, AZZ IST KARTICE - PBZ AMEX
za drugi dio naziva kartica se koristi vezna tablica mzmp_vpla_sus
*/

  IF vpla_id_p IS NULL THEN
    RETURN (NULL);
  END IF;

  SELECT vppla.sifra INTO vppla_sifra_l
    FROM s_vpplacanja vppla, s_vplacanja vpla
   WHERE vppla.id = vpla.vppla_id
     AND vpla.id = vpla_id_p;
  IF vppla_sifra_l = 'G' THEN
    opis_l := 'AZZ ISTRATECH GOTOVINA';
  ELSIF vppla_sifra_l = 'K' THEN
    SELECT MIN (zmpsusvpla.sifra) INTO zmpsusvpla_sifra_l
      FROM mzmp_vpla_sus zmpsusvpla, szmp_sustavi zmpsus
     WHERE zmpsusvpla.zmpsus_id = zmpsus.id
       AND zmpsus.sifra = 'FINORA'
       AND zmpsusvpla.vpla_id = vpla_id_p;
    opis_l := 'AZZ IST KARTICE - ' || zmpsusvpla_sifra_l;
  END IF;
  --
  IF opis_l IS NULL THEN
    opis_l := 'AZZ IST NEDEFINIRANO';
  END IF;
  --
  RETURN (opis_l);

END;

FUNCTION receivables_description
 (rac_id_p IN t_racuni.id%TYPE
 ,izn_tecaja_p IN t_racuni.izn_tecaja%TYPE
 )
 RETURN VARCHAR2
 IS
-- Popis brojeva i iznosa iskorištenih predujmova u valuti računa, zapiše se u Description računa za FINORA
  iznos_l t_rac_pred.iznos%TYPE;
  opis_l VARCHAR2 (240);
BEGIN
  IF rac_id_p IS NULL OR izn_tecaja_p IS NULL THEN
    RETURN (NULL);
  END IF;
  FOR racpred IN (
    SELECT pra_pka_s.br_racuna_fsus (pkarac.oj_sifra, pkarac.vdok_sifra2n, pkarac.rbr, pkarac.datum) broj_racuna,
           ROUND (racpred.iznos * racpred.izn_tecaja / izn_tecaja_p, 2) iznos
      FROM t_rac_pred racpred, tpka_racuni pkarac
     WHERE racpred.rac_id = rac_id_p
       AND racpred.pkaracp_id = pkarac.id
    )
  LOOP
    opis_l := opis_l || racpred.broj_racuna || ' ' || racpred.iznos || '; ';
  END LOOP;
  IF opis_l IS NOT NULL THEN
    IF REGEXP_COUNT (opis_l, ';') = 1 THEN
      opis_l := 'Iskorišteni predujam (broj, iznos) ' || opis_l;
    ELSE
      opis_l := 'Iskorišteni predujmovi (broj, iznos): ' || opis_l;
    END IF;
  END IF;
  RETURN (opis_l);
END;

FUNCTION receivables_transactiontype
 (rac_id_p IN t_racuni.id%TYPE
 ,vapldok_metapl_sifra_p IN s_vapldok.metapl_sifra%TYPE
 ,vapldok_oznaka_p IN s_vapldok.oznaka%TYPE
 ,val_sifra_3a_p IN s_valute.sifra_3a%TYPE
 ,domval_sifra_p IN s_valute.sifra_3a%TYPE
 )
 RETURN VARCHAR2
 IS
   opis_l VARCHAR2 (240);
   vppla_sifra_l s_vpplacanja.sifra%TYPE;
   br_redaka_l NUMBER;
BEGIN
  /*
 'TransactionType' kao podatak za Račun za FINORA:
  BI, BP u domval --> AZZ IST račun
  BI, BP <> domval --> AZZ IST Ino
  GI, GP u gotovini --> AZZ IST Gotovina
  GI, GP kartice --> AZZ IST Kartica
  GI, GP u domval sa više različitih vrsta plaćanja --> AZZ IST račun
  GI, GP <> domval sa više različitih vrsta plaćanja --> AZZ IST Ino
  */

  IF rac_id_p IS NULL OR vapldok_metapl_sifra_p IS NULL OR vapldok_oznaka_p IS NULL
    OR val_sifra_3a_p IS NULL OR domval_sifra_p IS NULL
  THEN
    RETURN (NULL);
  END IF;

  IF vapldok_oznaka_p IN ('BI', 'BP') THEN
    IF val_sifra_3a_p = domval_sifra_p THEN
      opis_l := 'AZZ IST račun';
    ELSE
      opis_l := 'AZZ IST Ino';
    END IF;
  ELSE
    IF vapldok_metapl_sifra_p = 'REC' THEN
      SELECT COUNT (DISTINCT (vppla.sifra)), MAX (vppla.sifra) INTO br_redaka_l, vppla_sifra_l
        FROM t_rac_nap racnap,
             s_vplacanja vpla,
             s_vpplacanja vppla
       WHERE racnap.rac_id = rac_id_p
         AND racnap.vpla_id = vpla.id
         AND vpla.vppla_id = vppla.id;
    END IF;

    IF vapldok_metapl_sifra_p = 'PKA' THEN
      SELECT COUNT (DISTINCT (vppla.sifra)), MAX (vppla.sifra) INTO br_redaka_l, vppla_sifra_l
        FROM tpka_rac_nap racnap,
             s_vplacanja vpla,
             s_vpplacanja vppla
       WHERE racnap.pkarac_id = rac_id_p
         AND racnap.vpla_id = vpla.id -- IRN 3543
         AND vpla.vppla_id = vppla.id;
    END IF;

    IF br_redaka_l = 1 AND vppla_sifra_l = 'G' THEN
      -- samo jedna vrsta poreznih plaćanja i to gotovina
      opis_l := 'AZZ IST Gotovina';
    ELSE
      IF br_redaka_l = 1 AND vppla_sifra_l = 'K' THEN
        -- samo jedna vrsta poreznih plaćanja i to kartice
          opis_l := 'AZZ IST Kartica';
      ELSE
        -- više vrsta poreznih plaćanja ili nije G niti K
        IF val_sifra_3a_p = domval_sifra_p THEN
          opis_l := 'AZZ IST račun';
        ELSE
          opis_l := 'AZZ IST Ino';
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN (opis_l);

END;

FUNCTION business_unit
 (rac_datum_p IN t_racuni.datum%type
) 
RETURN VARCHAR2
IS
  oznaka_l VARCHAR2(50);
  vla_dat_primjene_l s_vlasnik.dat_primjene%type;
BEGIN
  SELECT dat_primjene_domval into vla_dat_primjene_l FROM s_vlasnik;
  IF rac_datum_p < vla_dat_primjene_l THEN
   oznaka_l := 'AUTO ZUBAK - ZAGREB';
  ELSE
    oznaka_l:= 'AUTO ZUBAK - ZAGREB EUR';
  END IF;
  RETURN (oznaka_l);
END;

PROCEDURE ora_zaj_post
 (url_p IN VARCHAR2)
 IS
-- SPKA_PARAMETRI_PRIJEPISA

  url_l spka_parametri_prijepisa.staza%TYPE;
  username_l VARCHAR2(50);
  password_l VARCHAR2(50);
  req_l UTL_HTTP.req;
  res_l UTL_HTTP.resp;
  text_l VARCHAR2(32767);
  clob_l CLOB;
  br_dana_l szmp_parametri.vrijednost%TYPE;
  CURSOR auth_c IS
    SELECT sus_username, sus_password
      FROM mrec_auth_sus
     WHERE zmpsus_id IN (SELECT id FROM szmp_sustavi WHERE sifra = 'FINORA');
BEGIN
  SELECT MIN (staza)
    INTO url_m
    FROM spka_parametri_prijepisa
   WHERE sifra = 'RACUNI';

  OPEN auth_c;
  FETCH auth_c INTO username_l, password_l;
  CLOSE auth_c;
  odgovor_m := NULL;

  br_dana_l := pra_zmp_zaj.zmppar_vrijednost('FINORA', '1001');

  IF UPPER (br_dana_l) <> 'NEBITNO' THEN
    DELETE FROM lpka_clob
     WHERE oznaka = 'FINORA'
       AND TRUNC (SYSDATE) - TRUNC (unos_dat) >= TO_NUMBER (br_dana_l);
  END IF;

  IF log_filename_m IS NOT NULL THEN
    pra_pka2.clob_to_table (CHR(10) || '[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] ' || url_m || url2_m || CHR(10), log_filename_m, NULL, 'FINORA');
    pra_pka2.clob_to_table (data_m || CHR(10), log_filename_m, NULL, 'FINORA');
  END IF;

  url_l := url_m || url_p;

  UTL_HTTP.set_response_error_check (enable => FALSE);
  UTL_HTTP.set_detailed_excp_support (enable => TRUE);
  UTL_HTTP.set_body_charset (charset => 'UTF-8');
  req_l := UTL_HTTP.begin_request (url => url_l, method => 'POST');
  UTL_HTTP.set_authentication(req_l, username_l, password_l, 'Basic');
  UTL_HTTP.set_header (r => req_l, name => 'Content-Type', value => 'application/json; charset=utf-8');
  UTL_HTTP.set_header(req_l, 'Accept' , 'application/json');
  UTL_HTTP.set_header (req_l, 'Authorization', 'Basic enViYWt3czpkYXlBbzZNdFBwM2MyRVgy');
  -- UTL_HTTP.set_header (req_l, 'Content-Length', pra_pka2.clob_length (data_m));

  DECLARE
    req_length_l BINARY_INTEGER;
    buffer_l     VARCHAR2(2000);
    amount_l     PLS_INTEGER := 2000;
    offset_l     PLS_INTEGER := 1;
  BEGIN
    req_length_l := pra_pka2.clob_length (data_m);
    IF req_length_l <= 32767 THEN
      UTL_HTTP.set_header (req_l, 'Content-Length', req_length_l);
      UTL_HTTP.write_text (req_l, data_m);
    ELSE
      UTL_HTTP.set_header (req_l, 'Transfer-Encoding', 'chunked');
      WHILE (offset_l < req_length_l) LOOP
        buffer_l := DBMS_LOB.substr (data_m, amount_l, offset_l);
        UTL_HTTP.write_text (req_l, buffer_l);
        offset_l := offset_l + amount_l;
      END LOOP;
    END IF;
  END;
  -- dbms_output.put_line ('Zahtjev');
  res_l := UTL_HTTP.get_response (req_l);

  -- dbms_output.put_line ('Nakon zahtjeva');
  UTL_HTTP.set_body_charset (res_l, charset => 'UTF-8');

  BEGIN
    DBMS_LOB.CreateTemporary (clob_l, TRUE);
    LOOP
      UTL_HTTP.read_text (res_l, text_l, 32767);
      DBMS_LOB.writeappend (clob_l, LENGTH(text_l), text_l);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response (res_l);
  END;

  odgovor_m := clob_l;
  -- dbms_output.put_line ('Odgovor: ' || odgovor_m);
  IF log_filename_m IS NOT NULL THEN
    pra_pka2.clob_to_table (odgovor_m || CHR(10), log_filename_m, NULL, 'FINORA');
  END IF;

EXCEPTION
  WHEN UTL_HTTP.end_of_body THEN
    UTL_HTTP.end_response (res_l);
  WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN
    UTL_HTTP.end_response (res_l);
  WHEN utl_http.http_client_error THEN
    -- dbms_output.put_line (res_l.status_code || ':' || res_l.reason_phrase);

    UPDATE lpka_prifin_odg
       SET odgovor = Utl_Http.get_detailed_sqlcode  || '/' || Utl_Http.Get_Detailed_Sqlerrm
     WHERE id = pkaprifinodg_id_m;

    -- RETURN;
  WHEN OTHERS THEN
    UPDATE lpka_prifin_odg
       SET odgovor = Utl_Http.get_detailed_sqlcode  || '/' || Utl_Http.Get_Detailed_Sqlerrm
     WHERE id = pkaprifinodg_id_m;

    -- RETURN;
END;

PROCEDURE ora_obradi_odg
 (pkaprifinodg_id_p IN lpka_prifin_odg.id%TYPE
 )
 IS
  fault_l lpka_prifin_odg.odgovor%TYPE;
  success_l lpka_prifin_odg.odgovor%TYPE;
BEGIN

  APEX_JSON.parse (odgovor_m);
  fault_l := APEX_JSON.get_varchar2 (p_path => 'fault.faultMessage'); -- fault.faultMessage
  success_l := APEX_JSON.get_varchar2 (p_path => 'result.status'); -- result.status
  -- dbms_output.put_line ('Odgovor uspjeh: ' || success_l);
  -- dbms_output.put_line ('Odgovor neuspjeh: ' || fault_l);
  IF fault_l IS NOT NULL THEN

    UPDATE lpka_prifin_odg
       SET odgovor = fault_l
     WHERE id = pkaprifinodg_id_p;
    RETURN;

  ELSE

    IF success_l IS NOT NULL THEN
      -- dbms_output.put_line ('uslo u success');
      -- dbms_output.put_line (pkaprifinodg_id_p);
      UPDATE lpka_prifin_odg
         SET odgovor = success_l
       WHERE id = pkaprifinodg_id_p;
      COMMIT;

      UPDATE lpka_rac_fin
         SET pkaprifinsc_id = (SELECT pkaprifin_id FROM lpka_prifin_odg WHERE id = pkaprifinodg_id_p)
       WHERE id IN (SELECT pkaracfin_id FROM lpka_prifin_odg WHERE id = pkaprifinodg_id_p);
      COMMIT;

      RETURN;
    END IF;
  END IF;
END;

PROCEDURE ora_izvrsi_prijepis
 (dat_poc_p IN DATE
 ,dat_zav_p IN DATE
 ,rac_id_p IN NUMBER := NULL
 ,pkarac_id_p IN NUMBER := NULL
 )
 IS
--
  CURSOR parpri_c (sifra_k IN spka_parametri_prijepisa.sifra%TYPE) IS
    SELECT id
      FROM spka_parametri_prijepisa
     WHERE sifra = sifra_k;
  pkaprifinodg_id_l lpka_prifin_odg.id%TYPE;
  pkaparpri_id_l  spka_parametri_prijepisa.id%TYPE;
  pkaprifin_id_l  lpka_prijepisi_fin.id%TYPE;
  pkaparfin_rbr_l lpka_prijepisi_fin.rbr%TYPE;
  datum_l         DATE;
  god_poslovna_l  VARCHAR2(4) := pra_zmp_zaj.zmppar_vrijednost('PKA', '1005');
  napomena_l      VARCHAR2 (100);
BEGIN

  -- licenca
  IF pra_zmp_s.oznaka ('P0040') = 0 THEN
    pra_zmp_zaj.vrati_poruku (-20138);
  END IF;

  -- Ne može se izvršiti prijepis za datum manji od početnog datuma rada na aplikaciji (ZMP parametar 0036).
  pra_zmp_s.provjeri_dat_pocetni_0036 (dat_poc_p);

  OPEN parpri_c ('RACUNI');
  FETCH parpri_c INTO pkaparpri_id_l;
  CLOSE parpri_c;

  IF rac_id_p IS NOT NULL THEN napomena_l := napomena_l || ' račun ' || rac_id_p; END IF;
  IF pkarac_id_p IS NOT NULL THEN napomena_l := napomena_l || ' PKA račun ' || pkarac_id_p; END IF;

  INSERT INTO lpka_prijepisi_fin (pkaparpri_id, god_poslovna, dat_poc, dat_zav, napomena)
    VALUES (pkaparpri_id_l, god_poslovna_l, dat_poc_p, dat_zav_p, napomena_l)
    RETURNING id, rbr INTO pkaprifin_id_l, pkaparfin_rbr_l;

  log_filename_m := LOWER (zmp_zaj.current_schema()) || '_' || god_poslovna_l || '_' || pkaparfin_rbr_l || '.log';

  -- najprije se za svaki datum napravi priprema
  -- commit slanja ide za svaki datum da se ne desi uspješan upis u VANJSKI SUSTAV za prethodni datum
  -- a nikakav upis u MISH zbog greške na sljedećem datumu
  datum_l := dat_poc_p;
  WHILE datum_l <= dat_zav_p LOOP
    -- dbms_output.put_line ('uslo u loop - pripremi prijepis');
    ora_pripremi_prijepis (datum_l, rac_id_p, pkarac_id_p, pkaprifin_id_l);
    datum_l := datum_l + 1;
  END LOOP;
  COMMIT;

  datum_l := dat_poc_p;
  WHILE datum_l <= dat_zav_p LOOP

    FOR pkapred IN (SELECT id AS pkaracfin_id,
                           ozn_uk AS pkapred_id
                      FROM lpka_rac_fin
                     WHERE ozn_vracuna = 'PKAPRED'
                       AND datum = datum_l
                       AND (pkarac_id_p IS NULL OR ozn_uk = pkarac_id_p)
                       AND pkaprifinsc_id IS NULL)
    LOOP
      INSERT INTO lpka_prifin_odg (pkaprifin_id, pkaracfin_id)
      VALUES (pkaprifin_id_l, pkapred.pkaracfin_id)
      RETURNING id INTO pkaprifinodg_id_l;
      pkaprifinodg_id_m := pkaprifinodg_id_l;
      COMMIT;
      ora_salji_pkapred (pkaprifin_id_l, pkapred.pkapred_id, pkaprifinodg_id_l);
    END LOOP;


    FOR recrac IN (SELECT id AS pkaracfin_id,
                          ozn_uk AS rac_id
                     FROM lpka_rac_fin
                    WHERE ozn_vracuna = 'RECRAC'
                      AND datum = datum_l
                      AND (rac_id_p IS NULL OR ozn_uk = rac_id_p)
                      AND pkaprifinsc_id IS NULL)
    LOOP
      INSERT INTO lpka_prifin_odg (pkaprifin_id, pkaracfin_id)
        VALUES (pkaprifin_id_l, recrac.pkaracfin_id)
        RETURNING id INTO pkaprifinodg_id_l;
      pkaprifinodg_id_m := pkaprifinodg_id_l;
      COMMIT;
      ora_salji_recrac (pkaprifin_id_l, recrac.rac_id, pkaprifinodg_id_l);
    END LOOP;

    FOR recracnap IN (SELECT id AS pkaracfin_id,
                             ozn_uk racnap_id
                        FROM lpka_rac_fin
                       WHERE ozn_vracuna = 'RECRACNAP'
                         AND datum = datum_l
                         AND (ozn_uk IN (SELECT id FROM t_rac_nap WHERE id = NVL (rac_id_p, id)))
                         AND pkaprifinsc_id IS NULL)
    LOOP
      -- dbms_output.put_line ('recracnap prvi');
      INSERT INTO lpka_prifin_odg (pkaprifin_id, pkaracfin_id)
        VALUES (pkaprifin_id_l, recracnap.pkaracfin_id)
        RETURNING id INTO pkaprifinodg_id_l;
      pkaprifinodg_id_m := pkaprifinodg_id_l;
      COMMIT;
      ora_salji_recracnap (pkaprifin_id_l, recracnap.racnap_id, pkaprifinodg_id_l);
    END LOOP;

    FOR pkaprednap IN (SELECT id AS pkaracfin_id,
                              ozn_uk pkaracnap_id
                         FROM lpka_rac_fin
                        WHERE ozn_vracuna = 'PKAPREDNAP'
                          AND datum = datum_l
                          AND (ozn_uk IN (SELECT id FROM tpka_rac_nap WHERE id = NVL (pkarac_id_p, id)))
                          AND pkaprifinsc_id IS NULL)
    LOOP
      INSERT INTO lpka_prifin_odg (pkaprifin_id, pkaracfin_id)
        VALUES (pkaprifin_id_l, pkaprednap.pkaracfin_id)
        RETURNING id INTO pkaprifinodg_id_l;
      pkaprifinodg_id_m := pkaprifinodg_id_l;
      COMMIT;
      ora_salji_pkaprednap (pkaprifin_id_l, pkaprednap.pkaracnap_id, pkaprifinodg_id_l);
    END LOOP;

    FOR recracnapn IN (SELECT id AS pkaracfin_id,
                              ozn_uk racnap_id
                         FROM lpka_rac_fin
                        WHERE ozn_vracuna = 'RECRACNAPN'
                          AND datum = datum_l
                          AND (ozn_uk IN (SELECT id FROM t_rac_nap WHERE id = NVL (rac_id_p, id)))
                          AND pkaprifinsc_id IS NULL)
    LOOP
      INSERT INTO lpka_prifin_odg (pkaprifin_id, pkaracfin_id)
        VALUES (pkaprifin_id_l, recracnapn.pkaracfin_id)
        RETURNING id INTO pkaprifinodg_id_l;
      pkaprifinodg_id_m := pkaprifinodg_id_l;
      COMMIT;
      ora_salji_recracnapn (pkaprifin_id_l, recracnapn.racnap_id, pkaprifinodg_id_l);
    END LOOP;

    FOR pkaprenapn IN (SELECT id AS pkaracfin_id,
                              ozn_uk pkaracnap_id
                         FROM lpka_rac_fin
                        WHERE ozn_vracuna = 'PKAPRENAPN'
                          AND datum = datum_l
                          AND (ozn_uk IN (SELECT id FROM tpka_rac_nap WHERE id = NVL (pkarac_id_p, id)))
                          AND pkaprifinsc_id IS NULL)
    LOOP
      INSERT INTO lpka_prifin_odg (pkaprifin_id, pkaracfin_id)
        VALUES (pkaprifin_id_l, pkaprenapn.pkaracfin_id)
        RETURNING id INTO pkaprifinodg_id_l;
      pkaprifinodg_id_m := pkaprifinodg_id_l;
      COMMIT;
      ora_salji_pkaprednapn (pkaprifin_id_l, pkaprenapn.pkaracnap_id, pkaprifinodg_id_l);
    END LOOP;


    FOR recracn IN (SELECT id AS pkaracfin_id,
                           ozn_uk AS rac_id
                      FROM lpka_rac_fin
                     WHERE ozn_vracuna = 'RECRACN'
                       AND datum = datum_l
                       AND (rac_id_p IS NULL OR ozn_uk = rac_id_p)
                       AND pkaprifinsc_id IS NULL)
    LOOP
      INSERT INTO lpka_prifin_odg (pkaprifin_id, pkaracfin_id)
        VALUES (pkaprifin_id_l, recracn.pkaracfin_id)
        RETURNING id INTO pkaprifinodg_id_l;
      pkaprifinodg_id_m := pkaprifinodg_id_l;
      COMMIT;
      ora_salji_recracn (pkaprifin_id_l, recracn.rac_id, pkaprifinodg_id_l);
    END LOOP;

    FOR pkapredn IN (SELECT id AS pkaracfin_id,
                            ozn_uk AS pkapred_id
                       FROM lpka_rac_fin
                      WHERE ozn_vracuna = 'PKAPREDN'
                        AND datum = datum_l
                        AND (pkarac_id_p IS NULL OR ozn_uk = pkarac_id_p)
                        AND pkaprifinsc_id IS NULL)
    LOOP
      INSERT INTO lpka_prifin_odg (pkaprifin_id, pkaracfin_id)
        VALUES (pkaprifin_id_l, pkapredn.pkaracfin_id)
        RETURNING id INTO pkaprifinodg_id_l;
      pkaprifinodg_id_m := pkaprifinodg_id_l;
      COMMIT;
      ora_salji_pkapredn (pkaprifin_id_l, pkapredn.pkapred_id, pkaprifinodg_id_l);
    END LOOP;

    datum_l := datum_l + 1;

  END LOOP;

END;

PROCEDURE ora_pripremi_prijepis
 (datum_p IN DATE
 ,rac_id_p IN NUMBER
 ,pkarac_id_p IN NUMBER
 ,pkaprifin_id_p IN lpka_prijepisi_fin.id%TYPE
 )
 IS
--
  pripremljen_l  NUMBER;
  brojac_l       NUMBER;
BEGIN

  -- REC računi - RECRAC (invoice)
  FOR recrac_c IN (
    SELECT rac.id rac_id,
           rac.guid guid
      FROM t_racuni rac,
           s_vapldok vapldok,
           s_vdokumenata vdok
     WHERE rac.datum = datum_p AND rac.id = NVL (rac_id_p, rac.id)
       AND rac.oznaka IN ('V','P') -- storno idu posebno
       AND rac.vdok_id = vdok.id
       AND vdok.ozn_fin = -1
       AND rac.vapldok_id = vapldok.id
       AND vapldok.oznaka IN ('BI', 'GI', 'BV') -- svi osim RP
       AND vapldok.metapl_sifra = 'REC')
  LOOP
    SELECT COUNT(1)
      INTO pripremljen_l
      FROM lpka_rac_fin
     WHERE ozn_uk = recrac_c.rac_id AND ozn_vracuna = 'RECRAC';
    -- dbms_output.put_line ('ima li pripremljenih :' ||pripremljen_l);
    IF pripremljen_l = 0 THEN
      INSERT INTO lpka_rac_fin (ozn_uk, datum, ozn_vracuna, guid)
        VALUES (recrac_c.rac_id, datum_p, 'RECRAC', recrac_c.guid);
      COMMIT;
    END IF;
  END LOOP;
  -- KRAJ REC računi - RECRAC (invoice)

  -- REC GI računi NAPLATA - RECRACNAP (receipts)
  FOR recrac_c IN (
    SELECT rac.id rac_id,
           racnap.id racnap_id,
           rac.guid guid
      FROM t_rac_nap racnap,
           t_racuni rac,
           s_vapldok vapldok,
           s_vdokumenata vdok
     WHERE racnap.rac_id = rac.id AND racnap.id = NVL (rac_id_p, racnap.id)
       AND rac.datum = datum_p
       AND rac.oznaka IN ('V','P') -- storno idu posebno
       AND rac.vdok_id = vdok.id
       AND vdok.ozn_fin = -1
       AND rac.vapldok_id = vapldok.id
       AND vapldok.oznaka IN ('GI')
       AND vapldok.metapl_sifra = 'REC')
  LOOP
    SELECT COUNT(1)
      INTO pripremljen_l
      FROM lpka_rac_fin
     WHERE ozn_uk = recrac_c.racnap_id AND ozn_vracuna = 'RECRACNAP';

    IF pripremljen_l = 0 THEN
      INSERT INTO lpka_rac_fin (ozn_uk, datum, ozn_vracuna, guid)
        VALUES (recrac_c.racnap_id, datum_p, 'RECRACNAP', recrac_c.guid);
    END IF;
  END LOOP;
  -- KRAJ REC GI računi NAPLATA - RECRACNAP (receipts)

  -- Predujmovi - PKAPRED (invoice)
  FOR predujmovi_c IN (
    SELECT pkarac.id pkarac_id,
           pkarac.guid guid
      FROM tpka_racuni pkarac,
           s_vapldok vapldok,
           s_vdokumenata vdok
     WHERE pkarac.datum = datum_p AND pkarac.id = NVL (pkarac_id_p, pkarac.id)
       AND pkarac.oznaka IN ('V','P') -- storno idu posebno
       AND pkarac.pkaracI_id IS NULL AND pkarac.racI_id IS NULL -- Uplaćeni predujmovi
       AND pkarac.vdok_id = vdok.id
       AND vdok.ozn_fin = -1
       AND pkarac.vapldok_id = vapldok.id
       AND vapldok.oznaka IN ('BP', 'GP')
       AND vapldok.metapl_sifra = 'PKA')
  LOOP
    SELECT COUNT(1)
      INTO pripremljen_l
      FROM lpka_rac_fin
     WHERE ozn_uk = predujmovi_c.pkarac_id AND ozn_vracuna = 'PKAPRED';

    IF pripremljen_l = 0 THEN
      INSERT INTO lpka_rac_fin (ozn_uk, datum, ozn_vracuna, guid)
        VALUES (predujmovi_c.pkarac_id, datum_p, 'PKAPRED', predujmovi_c.guid);
    END IF;
  END LOOP;
  -- KRAJ Predujmovi - PKAPRED (invoice)

  -- PRED GP računi NAPLATA - PKAPREDNAP (receipts)
  FOR recrac_c IN (
    SELECT pkarac.id rac_id,
           pkaracnap.id racnap_id,
           pkarac.guid guid
      FROM tpka_rac_nap pkaracnap,
           tpka_racuni pkarac,
           s_vapldok vapldok,
           s_vdokumenata vdok
     WHERE pkaracnap.pkarac_id = pkarac.id AND pkaracnap.id = NVL (pkarac_id_p, pkaracnap.id)
       AND pkarac.datum = datum_p
       AND pkarac.oznaka IN ('V','P') -- storno idu posebno
       AND pkarac.pkaracI_id IS NULL AND pkarac.racI_id IS NULL -- Uplaćeni predujmovi
       AND pkarac.vdok_id = vdok.id
       AND vdok.ozn_fin = -1
       AND pkarac.vapldok_id = vapldok.id
       AND vapldok.oznaka IN ('GP')
       AND vapldok.metapl_sifra = 'PKA')
  LOOP
    SELECT COUNT(1)
      INTO pripremljen_l
      FROM lpka_rac_fin
     WHERE ozn_uk = recrac_c.racnap_id AND ozn_vracuna = 'PKAPREDNAP';

    IF pripremljen_l = 0 THEN
      INSERT INTO lpka_rac_fin (ozn_uk, datum, ozn_vracuna, guid)
        VALUES (recrac_c.racnap_id, datum_p, 'PKAPREDNAP', recrac_c.guid);
    END IF;
  END LOOP;
  -- KRAJ PRED GP računi NAPLATA - PKAPREDNAP (receipts)

  -- RECRACN (creditmemo),  BI GI BV
  FOR recrac_c IN (
    SELECT rac.id rac_id,
           rac.guid guid
      FROM t_racuni rac,
           s_vapldok vapldok,
           s_vdokumenata vdok
     WHERE rac.datum = datum_p AND rac.id = NVL (rac_id_p, rac.id)
       AND rac.oznaka IN ('N') -- storno idu posebno
       AND rac.vdok_id = vdok.id
       AND vdok.ozn_fin = -1
       AND rac.vapldok_id = vapldok.id
       AND vapldok.oznaka IN ('BI', 'GI', 'BV') -- svi osim RP
       AND vapldok.metapl_sifra = 'REC')
  LOOP
    SELECT COUNT(1)
      INTO pripremljen_l
      FROM lpka_rac_fin
     WHERE ozn_uk = recrac_c.rac_id AND ozn_vracuna = 'RECRACN';

    IF pripremljen_l = 0 THEN
      INSERT INTO lpka_rac_fin (ozn_uk, datum, ozn_vracuna, guid)
        VALUES (recrac_c.rac_id, datum_p, 'RECRACN', recrac_c.guid);
    END IF;
  END LOOP;
  -- KRAJ RECRACN (creditmemo),  BI GI BV

  -- PKAPREDN (creditmemo), BP GP
  FOR predujmovi_c IN (
    SELECT pkarac.id pkarac_id,
           pkarac.guid guid
      FROM tpka_racuni pkarac,
           s_vapldok vapldok,
           s_vdokumenata vdok
     WHERE pkarac.datum = datum_p AND pkarac.id = NVL (pkarac_id_p, pkarac.id)
       AND (pkarac.oznaka IN ('N') -- storno
                 OR pkarac.racI_id IS NOT NULL  -- iskorišteni
                )
       AND pkarac.pkaracI_id IS NULL  -- PKA račune ne šaljemo pa ne šaljemo niti iskorištene predujmove za njih
       AND pkarac.vdok_id = vdok.id
       AND vdok.ozn_fin = -1
       AND pkarac.vapldok_id = vapldok.id
       AND vapldok.oznaka IN ('BP', 'GP')
       AND vapldok.metapl_sifra = 'PKA')
  LOOP
    SELECT COUNT(1)
      INTO pripremljen_l
      FROM lpka_rac_fin
     WHERE ozn_uk = predujmovi_c.pkarac_id AND ozn_vracuna = 'PKAPREDN';

    IF pripremljen_l = 0 THEN
      INSERT INTO lpka_rac_fin (ozn_uk, datum, ozn_vracuna, guid)
        VALUES (predujmovi_c.pkarac_id, datum_p, 'PKAPREDN', predujmovi_c.guid);
    END IF;
  END LOOP;
  -- KRAJ PKAPREDN (creditmemo), BP GP

  -- RECRACNAPN (receiptsrev), GI
  FOR recrac_c IN (
    SELECT rac.id rac_id,
           racnap.id racnap_id,
           rac.guid guid
      FROM t_rac_nap racnap,
           t_racuni rac,
           s_vapldok vapldok,
           s_vdokumenata vdok
     WHERE racnap.rac_id = rac.id AND racnap.id = NVL (rac_id_p, racnap.id)
       AND rac.datum = datum_p
       AND rac.oznaka IN ('N') -- storno
       AND rac.vdok_id = vdok.id
       AND vdok.ozn_fin = -1
       AND rac.vapldok_id = vapldok.id
       AND vapldok.oznaka IN ('GI')
       AND vapldok.metapl_sifra = 'REC')
  LOOP
    SELECT COUNT(1)
      INTO pripremljen_l
      FROM lpka_rac_fin
     WHERE ozn_uk = recrac_c.racnap_id AND ozn_vracuna = 'RECRACNAPN';

    IF pripremljen_l = 0 THEN
      INSERT INTO lpka_rac_fin (ozn_uk, datum, ozn_vracuna, guid)
        VALUES (recrac_c.racnap_id, datum_p, 'RECRACNAPN', recrac_c.guid);
    END IF;
  END LOOP;
  -- KRAJ RECRACNAPN (receiptsrev), GI

  -- PKAPRENAPN (receiptsrev) GP
  FOR recrac_c IN (
    SELECT pkarac.id rac_id,
           pkaracnap.id racnap_id,
           pkarac.guid guid
      FROM tpka_rac_nap pkaracnap,
           tpka_racuni pkarac,
           s_vapldok vapldok,
           s_vdokumenata vdok
     WHERE pkaracnap.pkarac_id = pkarac.id AND pkaracnap.id = NVL (pkarac_id_p, pkaracnap.id)
       AND pkarac.datum = datum_p
       AND pkarac.oznaka IN ('N') -- storno
       AND pkarac.pkaracI_id IS NULL AND pkarac.racI_id IS NULL -- Uplaćeni predujmovi
       AND pkarac.vdok_id = vdok.id
       AND vdok.ozn_fin = -1
       AND pkarac.vapldok_id = vapldok.id
       AND vapldok.oznaka IN ('GP')
       AND vapldok.metapl_sifra = 'PKA')
  LOOP
    SELECT COUNT(1)
      INTO pripremljen_l
      FROM lpka_rac_fin
     WHERE ozn_uk = recrac_c.racnap_id AND ozn_vracuna = 'PKAPRENAPN';

    IF pripremljen_l = 0 THEN
      INSERT INTO lpka_rac_fin (ozn_uk, datum, ozn_vracuna, guid)
        VALUES (recrac_c.racnap_id, datum_p, 'PKAPRENAPN', recrac_c.guid);
    END IF;
  END LOOP;
  -- KRAJ PKAPRENAPN (receiptsrev) GP

END;

PROCEDURE ora_salji_recrac
 (pkaprifin_id_p IN lpka_prijepisi_fin.id%TYPE
 ,rac_id_p IN t_racuni.id%TYPE
 ,pkaprifinodg_id_p IN lpka_prifin_odg.id%TYPE
 ) IS

  CURSOR receivable_invoice_header_c IS
    SELECT
      SourceSystem
      ,SourceObjectId                      -- > lpka_prifin_odg.id
      ,Partner
      ,PartnerType
      ,AddressName
      ,AddressLine1
      ,City
      ,Country
      ,PostalCode
      ,OIB
      ,TaxOrganizationType
      ,TransactionSource
      ,TransactionType
      ,RemittanceModel
      ,RemittanceReferenceNumber
      ,TransactionNumber
      ,TO_CHAR (TransactionDate, 'YYYY-MM-DD') AS TransactionDate
      ,InvoiceCurrencyCode
      ,TO_CHAR (DueDate, 'YYYY-MM-DD') AS DueDate
      ,Description
      ,BusinessUnit
      ,Account
      ,CostCenter
      ,PJ
      ,PC
      ,MV
      ,IncomeCode
      ,OutcomeCode
      ,AccountMap
      ,CostCenterMap
      ,PJMap
      ,PCMap
      ,MVMap
      ,IncomeCodeMap
      ,OutcomeCodeMap
      --receivablesInvoiceLines
   FROM
    (SELECT
      'ISTRATECH' AS SourceSystem
      ,pkaprifinodg_id_p AS SourceObjectId -- > lpka_prifin_odg.id
      ,kd.kd_naziv AS Partner
      ,'Customer' AS PartnerType
      ,kd.kd_naz_ulice AS AddressName
      ,kd.kd_naz_ulice AS AddressLine1
      ,kd.kd_pos_naziv AS City
      ,kd.kd_zem_sifra_2a AS Country
      ,kd.kd_pos_broj AS PostalCode
      ,kd.kd_br_porezni AS OIB
      ,DECODE (kd.kd_br_porezni, NULL, 'INDIVIDUAL', 'CORPORATION') AS TaxOrganizationType
      ,'AZ ISTRATECH' AS TransactionSource
      ,Receivables_TransactionType (rac.id, vapldok.metapl_sifra, vapldok.oznaka, val.sifra_3a, domval_sifra_m) AS TransactionType
      ,rac.poziv_na_broj AS RemittanceModel
      ,pra_pka_s.br_racuna_fsus (rac.oj_sifra, rac.vdok_sifra2n, rac.rbr, rac.datum) AS RemittanceReferenceNumber
      ,pra_pka_s.br_racuna_fsus (rac.oj_sifra, rac.vdok_sifra2n, rac.rbr, rac.datum) || '/' || SUBSTR (rac.god_poslovna, 3, 2) AS TransactionNumber
      ,rac.datum AS TransactionDate
      ,val.sifra_3a AS InvoiceCurrencyCode
      ,rac.dat_dospijeca AS DueDate
      ,Receivables_Description (rac.id, rac.izn_tecaja) AS Description
      ,business_unit(rac.datum) AS BusinessUnit
      ,NULL AS Account
      ,NULL AS CostCenter
      ,NULL AS PJ
      ,NULL AS PC
      ,NULL AS MV
      ,NULL AS IncomeCode
      ,NULL AS OutcomeCode
      ,NULL AS AccountMap
      ,pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1003') AS  CostCenterMap
      ,NULL AS PJMap
      ,NULL AS PCMap
      ,NULL AS MVMap
      ,NULL AS IncomeCodeMap
      ,NULL AS OutcomeCodeMap
      -- receivablesInvoiceLines
    FROM
      t_racuni rac,
      s_vapldok vapldok,
      (SELECT id age_id FROM m_agencije WHERE sifra = pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1002')) -- pra_zmp_zaj.zmppar_vrijednost ('FINORA, '1001'))
      age_param,
      (SELECT age.id kd_id,
          age.naziv kd_naziv,
          age.naz_ulice kd_naz_ulice,
          zem.sifra_2a kd_zem_sifra_2a,
          pos.broj kd_pos_broj,
          pos.naziv kd_pos_naziv,
          DECODE (zem.sifra_2a, domzem_sifra, age.oib, age.br_porezni) kd_br_porezni
       FROM m_agencije age,
          s_zemlje zem,
          (SELECT pra_rec_zaj.vrijednost_parametra ('0002') domzem_sifra FROM dual) param,
        m_poste pos
      WHERE age.zem_id = zem.id
        AND age.pos_id = pos.id (+)
      ) kd,
      s_valute val
    WHERE rac.id  = rac_id_p   -- VS
      AND rac.vapldok_id = vapldok.id
      AND kd.kd_id = DECODE (rac.age_id, NULL, age_param.age_id, pra_rec2.agekupac_id (rac.vdok_id, rac.age_id, rac.agep_id, rac.vpdog_id))
      AND val.id = rac.val_id
    );
  receivable_invoice_header_r receivable_invoice_header_c%ROWTYPE;

  CURSOR receivable_invoice_lines_c IS
    SELECT
      rac_id_za_test,
      LineNumber
      ,Description
      ,Quantity
      ,UnitOfMeasure
      ,TRIM (TO_CHAR (UnitSellingPrice, '9999999990D99999999', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS UnitSellingPrice
      -- ,UnitSellingPrice
      ,TRIM (TO_CHAR (Discount, '9999999990D99999999', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS Discount
      -- ,Discount
      ,TRIM (TO_CHAR (OriginalSellingPrice, '9999999990D99999999', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS OriginalSellingPrice
      -- ,OriginalSellingPrice
      ,ConsumptionTax
      ,TRIM (TO_CHAR (TaxClassificationCode, '990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS TaxClassificationCode
      -- ,TaxClassificationCode
      ,TRIM (TO_CHAR (TaxAmount, '9999999990D99999999', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS TaxAmount
      -- ,TaxAmount
      ,ContractType
      ,ServiceCode
      ,ChannelId
      ,ProductType
      ,CompanyId
      ,Account
      ,CostCenter
      ,PJ
      ,PC
      ,MV
      ,AccountMap
      ,CostCenterMap
      ,PJMap
      ,PCMap
      ,MVMap
    FROM
      ( -- receivablesInvoiceLines
      SELECT
        rac_id_za_test
        ,DENSE_RANK() over (ORDER BY ozn_abc, dat_poc, id_stavke, usl_u_usl) AS LineNumber --VS: DAJE UVIJEK ISTI RBR TEMELJEM ORDER BY
        ,Description
        ,Quantity
        ,UnitOfMeasure
        ,CASE oznaka WHEN 'TRO' THEN (izn_stavke_bruto - izn_odbitka) * 100 / (100 + stopa_pdv)
                     ELSE izn_stavke_bruto
        END AS UnitSellingPrice -- nije zaokružena
        ,CASE oznaka WHEN 'TRO' THEN izn_odbitka * 100 / (100 + stopa_pdv)
                     ELSE NULL
        END AS Discount
        ,CASE oznaka WHEN 'TRO' THEN (izn_stavke_bruto) * 100 / (100 + stopa_pdv)
                     ELSE izn_stavke_bruto
        END AS OriginalSellingPrice
        ,NULL AS ConsumptionTax
        ,NVL (stopa_pdv, 0) AS TaxClassificationCode
        ,CASE oznaka WHEN 'TRO' THEN NVL (((izn_stavke_bruto - izn_odbitka) * stopa_pdv / (100 + stopa_pdv)), 0)
                     ELSE 0
        END AS TaxAmount
        ,NULL AS ContractType
        ,NULL AS ServiceCode
        ,NULL AS ChannelId
        ,ProductType
        ,NULL AS CompanyId
        ,NULL AS Account
        ,NULL AS CostCenter
        ,NULL AS PJ
        ,NULL AS PC
        ,NULL AS MV
        ,AccountMap
        ,CostCenterMap
        ,NULL AS PJMap
        ,NULL AS PCMap
        ,NULL AS MVMap
      FROM
        -- poc usluge
        (
        SELECT
          rac.id rac_id_za_test,
          'TRO' AS oznaka
          ,usl.naziv || '; ' || TO_CHAR (tro.dat_poc, 'DD.MM.YYYY') || ' - ' || TO_CHAR (tro.dat_zav, 'DD.MM.YYYY') AS Description
          ,1 AS Quantity
          ,'KOM' AS UnitOfMeasure
          ,ROUND (tro.cijena_usluge * tro.stopa_placanja / 100 * tro.izn_tecaja / rac.izn_tecaja, 2)
                * kol_usluge
                * DECODE (tro.obr_po_danu, -1, (tro.dat_zav - tro.dat_poc - tro.obr_zdana), 1)
            AS izn_stavke_bruto
          ,tro.izn_odbitka AS izn_odbitka
          ,tro.stopa_pdv AS stopa_pdv
          ,'Services' AS ProductType
          ,usl.sifra AS AccountMap
          ,pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1003') AS CostCenterMap
          ,usl.ozn_abc ozn_abc
          ,tro.dat_poc dat_poc
          ,tro.id id_stavke
          ,0 usl_u_usl
        FROM
          t_troskovi tro, t_racuni rac,
          s_usluge usl
        WHERE
          tro.rac_id = rac.id
          AND tro.usl_id = usl.id
          AND rac.id  = rac_id_p --VS
        -- kraj usluge
        UNION ALL
        -- poc usluge u usluzi
        SELECT
          rac.id rac_id_za_test,
          'TRO' AS oznaka
          ,usl.naziv || '; ' || TO_CHAR (tro.dat_poc, 'DD.MM.YYYY') || ' - ' || TO_CHAR (tro.dat_zav, 'DD.MM.YYYY') AS Description
          ,-1 AS Quantity
          ,'KOM' AS UnitOfMeasure
          ,-1 * ROUND (tro.cijena_usluge * tro.stopa_placanja / 100 * tro.izn_tecaja / rac.izn_tecaja, 2)
              * kol_usluge
              * DECODE (tro.obr_po_danu, -1, (tro.dat_zav - tro.dat_poc - tro.obr_zdana), 1)
            AS izn_stavke_bruto
          ,-1 * tro.izn_odbitka AS izn_odbitka
          ,tro.stopa_pdv1 AS stopa_pdv
          ,'Services' AS ProductType
          ,usl.sifra AS AccountMap
          ,pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1003') AS CostCenterMap
          ,usl.ozn_abc ozn_abc
          ,tro.dat_poc dat_poc
          ,tro.id id_stavke
          ,-1 usl_u_usl
        FROM
          t_troskovi tro, t_racuni rac,
          s_usluge usl
        WHERE
          tro.rac_id = rac.id
          AND tro.usl_id1 = usl.id
          AND rac.id  = rac_id_p   -- VS
        -- kraj usluge u usluzi
        UNION ALL
        -- poc POS
        SELECT
          rac.id rac_id_za_test,
          'PKARNK' AS oznaka
          ,'DIVENTA ' || gmpkas.naziv || ' ' || pkarnk.rbr || '/' || pkarnk.god_poslovna AS Description
          ,1 AS Quantity
          ,'KOM' AS UnitOfMeasure
          ,ROUND ((pkarnk.izn_placanja + NVL (pkarnk.izn_placanja2, 0)) / rac.izn_tecaja, 2) -- IRN 3543
            AS izn_stavke_bruto
          ,TO_NUMBER (NULL) AS izn_odbitka
          ,TO_NUMBER (NULL) AS stopa_pdv
          ,'Services' AS ProductType
          ,'DIVENTA' AS AccountMap
          ,pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1003') AS CostCenterMap
          ,'98' ozn_abc --IRN 3923
          ,pkarnk.datum dat_poc
          ,pkarnk.id id_stavke
          ,0 usl_u_usl
        FROM
          tpka_rnkasa pkarnk,
          t_racuni rac,
          mgmp_kase gmpkas
        WHERE
          pkarnk.rac_id = rac.id
          AND pkarnk.gmpkas_id = gmpkas.id
          AND rac.id  = rac_id_p  -- VS
        -- kraj POS
        UNION ALL --IRN 3923
        -- početak Napojnice 
        SELECT
          rac.id rac_id_za_test,
          'NAPOJNICA' AS oznaka
          ,'Napojnica' AS Description
          ,1 AS Quantity
          ,'KOM' AS UnitOfMeasure
          ,pra_rec_s5.izn_napojnice_uk (rac.id, 2)   -- u stavkama ide iznos napojnice u valuti računa 
            AS izn_stavke_bruto
          ,TO_NUMBER (NULL) AS izn_odbitka
          ,TO_NUMBER (NULL) AS stopa_pdv
          ,'Services' AS ProductType
         ,'NAPOJNICA' AS AccountMap -- staviti 'opisno' kao za ostale stavke 
          ,pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1003') AS CostCenterMap
          ,'99' AS ozn_abc
          ,rac.datum AS dat_poc
          ,1 AS id_stavke
          ,0 AS usl_u_usl
        FROM
          t_racuni rac
        WHERE
          rac.id  = rac_id_p
          AND pra_rec_s5.izn_napojnice_uk (rac.id, 2) <> 0 
        -- kraj Napojnica
        ) -- KRAJ unije usluge+usl_u_usl+POS
      ORDER BY ozn_abc, dat_poc, id_stavke, usl_u_usl
      );
  receivable_invoice_lines_r receivable_invoice_lines_c%ROWTYPE;
BEGIN
  data_m := NULL;
  odgovor_m := NULL;

  OPEN receivable_invoice_header_c;
  FETCH receivable_invoice_header_c INTO receivable_invoice_header_r;
  IF receivable_invoice_header_c%NOTFOUND THEN
    CLOSE receivable_invoice_header_c;
  END IF;
  CLOSE receivable_invoice_header_c;

  APEX_JSON.initialize_clob_output;
  --APEX_JSON.open_array(); -- [
  APEX_JSON.open_object(); -- {

  APEX_JSON.write ('SourceSystem'             , receivable_invoice_header_r.SourceSystem);
  APEX_JSON.write ('SourceObjectId'           , receivable_invoice_header_r.SourceObjectId);
  IF receivable_invoice_header_r.Partner IS NOT NULL THEN
    APEX_JSON.write_raw ('Partner'            , '"' || receivable_invoice_header_r.Partner || '"');
  END IF;
  APEX_JSON.write ('PartnerType'              , receivable_invoice_header_r.PartnerType);
  IF receivable_invoice_header_r.AddressName IS NOT NULL THEN
    APEX_JSON.write_raw ('AddressName'        , '"' || receivable_invoice_header_r.AddressName || '"');
  END IF;
  IF receivable_invoice_header_r.AddressLine1 IS NOT NULL THEN
    APEX_JSON.write_raw ('AddressLine1'       , '"' || receivable_invoice_header_r.AddressLine1 || '"');
  END IF;
  IF receivable_invoice_header_r.City IS NOT NULL THEN
    APEX_JSON.write_raw ('City'               , '"' || receivable_invoice_header_r.City || '"');
  END IF;
  APEX_JSON.write ('Country'                  , receivable_invoice_header_r.Country);
  APEX_JSON.write ('PostalCode'               , receivable_invoice_header_r.PostalCode);
  APEX_JSON.write ('OIB'                      , receivable_invoice_header_r.OIB);
  APEX_JSON.write ('TaxOrganizationType'      , receivable_invoice_header_r.TaxOrganizationType);
  APEX_JSON.write ('TransactionSource'        , receivable_invoice_header_r.TransactionSource);
  IF receivable_invoice_header_r.TransactionType IS NOT NULL THEN
    APEX_JSON.write_raw ('TransactionType'            , '"' || receivable_invoice_header_r.TransactionType || '"');
  END IF;
  APEX_JSON.write ('RemittanceModel'          , receivable_invoice_header_r.RemittanceModel);
  APEX_JSON.write ('RemittanceReferenceNumber', receivable_invoice_header_r.RemittanceReferenceNumber);
  APEX_JSON.write ('TransactionNumber'        , receivable_invoice_header_r.TransactionNumber);
  APEX_JSON.write ('TransactionDate'          , receivable_invoice_header_r.TransactionDate);
  APEX_JSON.write ('InvoiceCurrencyCode'      , receivable_invoice_header_r.InvoiceCurrencyCode);
  APEX_JSON.write ('DueDate'                  , receivable_invoice_header_r.DueDate);
  IF receivable_invoice_header_r.Description IS NOT NULL THEN
    APEX_JSON.write_raw ('Description'        , '"' || receivable_invoice_header_r.Description || '"');
  END IF;
  APEX_JSON.write ('BusinessUnit'             , receivable_invoice_header_r.BusinessUnit);
  APEX_JSON.write ('Account'                  , receivable_invoice_header_r.Account);
  APEX_JSON.write ('CostCenter'               , receivable_invoice_header_r.CostCenter);
  APEX_JSON.write ('PJ'                       , receivable_invoice_header_r.PJ);
  APEX_JSON.write ('PC'                       , receivable_invoice_header_r.PC);
  APEX_JSON.write ('MV'                       , receivable_invoice_header_r.MV);
  APEX_JSON.write ('IncomeCode'               , receivable_invoice_header_r.IncomeCode);
  APEX_JSON.write ('OutcomeCode'              , receivable_invoice_header_r.OutcomeCode);
  APEX_JSON.write ('AccountMap'               , receivable_invoice_header_r.AccountMap);
  APEX_JSON.write ('CostCenterMap'            , receivable_invoice_header_r.CostCenterMap);
  APEX_JSON.write ('PJMap'                    , receivable_invoice_header_r.PJMap);
  APEX_JSON.write ('PCMap'                    , receivable_invoice_header_r.PCMap);
  APEX_JSON.write ('MVMap'                    , receivable_invoice_header_r.MVMap);
  APEX_JSON.write ('IncomeCodeMap'            , receivable_invoice_header_r.IncomeCodeMap);
  APEX_JSON.write ('OutcomeCodeMap'           , receivable_invoice_header_r.OutcomeCodeMap);
  APEX_JSON.open_array('receivablesInvoiceLines'); -- [

  OPEN receivable_invoice_lines_c;
  FETCH receivable_invoice_lines_c INTO receivable_invoice_lines_r;
  -- CLOSE receivable_invoice_lines_c;


  WHILE (receivable_invoice_lines_c%FOUND) LOOP

    APEX_JSON.open_object(); -- {
    APEX_JSON.write ('LineNumber'           , receivable_invoice_lines_r.LineNumber);
    IF receivable_invoice_lines_r.Description IS NOT NULL THEN
      APEX_JSON.write_raw ('Description'    , '"' || receivable_invoice_lines_r.Description || '"');
    END IF;
    APEX_JSON.write ('Quantity'             , receivable_invoice_lines_r.Quantity);
    APEX_JSON.write ('UnitOfMeasure'        , receivable_invoice_lines_r.UnitOfMeasure);
    APEX_JSON.write ('UnitSellingPrice'     , receivable_invoice_lines_r.UnitSellingPrice);
    APEX_JSON.write ('Discount'             , receivable_invoice_lines_r.Discount);
    APEX_JSON.write ('OriginalSellingPrice' , receivable_invoice_lines_r.OriginalSellingPrice);
    APEX_JSON.write ('ConsumptionTax'       , receivable_invoice_lines_r.ConsumptionTax);
    APEX_JSON.write ('TaxClassificationCode', receivable_invoice_lines_r.TaxClassificationCode);
    APEX_JSON.write ('TaxAmount'            , receivable_invoice_lines_r.TaxAmount);
    APEX_JSON.write ('ContractType'         , receivable_invoice_lines_r.ContractType);
    APEX_JSON.write ('ServiceCode'          , receivable_invoice_lines_r.ServiceCode);
    APEX_JSON.write ('ChannelId'            , receivable_invoice_lines_r.ChannelId);
    APEX_JSON.write ('ProductType'          , receivable_invoice_lines_r.ProductType);
    APEX_JSON.write ('CompanyId'            , receivable_invoice_lines_r.CompanyId);
    APEX_JSON.write ('Account'              , receivable_invoice_lines_r.Account);
    APEX_JSON.write ('CostCenter'           , receivable_invoice_lines_r.CostCenter);
    APEX_JSON.write ('PJ'                   , receivable_invoice_lines_r.PJ);
    APEX_JSON.write ('PC'                   , receivable_invoice_lines_r.PC);
    APEX_JSON.write ('MV'                   , receivable_invoice_lines_r.MV);
    APEX_JSON.write ('AccountMap'           , receivable_invoice_lines_r.AccountMap);
    APEX_JSON.write ('CostCenterMap'        , receivable_invoice_lines_r.CostCenterMap);
    APEX_JSON.write ('PJMap'                , receivable_invoice_lines_r.PJMap);
    APEX_JSON.write ('PCMap'                , receivable_invoice_lines_r.PCMap);
    APEX_JSON.write ('MVMap'                , receivable_invoice_lines_r.MVMap);
    APEX_JSON.close_object(); -- }
    FETCH receivable_invoice_lines_c INTO receivable_invoice_lines_r;
  END LOOP;

  IF (receivable_invoice_lines_c%ISOPEN) THEN
    CLOSE receivable_invoice_lines_c;
  END IF;

  APEX_JSON.close_array; -- ]
  APEX_JSON.close_object(); -- }

  data_m := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;
  url2_m := '/receivablesInvoices';
  ora_zaj_post (url2_m);

  ora_obradi_odg (pkaprifinodg_id_p);
  -- dbms_output.put_line (data_m);

END;

PROCEDURE ora_salji_pkapred
 (pkaprifin_id_p IN lpka_prijepisi_fin.id%TYPE
 ,pkarac_id_p IN tpka_racuni.id%TYPE
 ,pkaprifinodg_id_p IN lpka_prifin_odg.id%TYPE
 ) IS
  CURSOR receivable_invoice_header_c IS
    SELECT
      SourceSystem
      ,pkaprifinodg_id_p SourceObjectId -- > lpka_prifin_odg.id
      ,Partner
      ,PartnerType
      ,AddressName
      ,AddressLine1
      ,City
      ,Country
      ,PostalCode
      ,OIB
      ,TaxOrganizationType
      ,TransactionSource
      ,TransactionType
      ,RemittanceModel
      ,RemittanceReferenceNumber
      ,TransactionNumber
      ,TO_CHAR (TransactionDate, 'YYYY-MM-DD') TransactionDate
      ,InvoiceCurrencyCode
      ,TO_CHAR (DueDate, 'YYYY-MM-DD') AS DueDate
      ,Description
      ,BusinessUnit
      ,Account
      ,CostCenter
      ,PJ
      ,PC
      ,MV
      ,IncomeCode
      ,OutcomeCode
      ,AccountMap
      ,CostCenterMap
      ,PJMap
      ,PCMap
      ,MVMap
      ,IncomeCodeMap
      ,OutcomeCodeMap
      --receivablesInvoiceLines
    FROM
      (SELECT
        'ISTRATECH' AS SourceSystem
        ,pkaprifinodg_id_p AS SourceObjectId -- > lpka_prifin_odg.id
        ,kd.kd_naziv AS Partner
        ,'Customer' AS PartnerType
        ,kd.kd_naz_ulice AS AddressName
        ,kd.kd_naz_ulice AS AddressLine1
        ,kd.kd_pos_naziv AS City
        ,kd.kd_zem_sifra_2a AS Country
        ,kd.kd_pos_broj AS PostalCode
        ,kd.kd_br_porezni AS OIB
        ,DECODE (kd.kd_br_porezni, NULL, 'INDIVIDUAL', 'CORPORATION') AS TaxOrganizationType
        ,'AZ ISTRATECH' AS TransactionSource
        ,Receivables_TransactionType (rac.id, vapldok.metapl_sifra, vapldok.oznaka, val.sifra_3a, domval_sifra_m) AS TransactionType
        ,rac.poziv_na_broj AS RemittanceModel
        ,NVL (rac.br_modela, pra_pka_s.br_racuna_fsus (rac.oj_sifra, rac.vdok_sifra2n, rac.rbr, rac.datum))
          AS RemittanceReferenceNumber
        ,pra_pka_s.br_racuna_fsus (rac.oj_sifra, rac.vdok_sifra2n, rac.rbr, rac.datum) || '/' || SUBSTR (rac.god_poslovna, 3, 2) AS TransactionNumber
        ,rac.datum AS TransactionDate
        ,val.sifra_3a AS InvoiceCurrencyCode
        ,NVL (rac.dat_dospijeca, SYSDATE) AS DueDate
        ,NULL AS Description
        ,business_unit(rac.datum) AS BusinessUnit
        ,NULL AS Account
        ,NULL AS CostCenter
        ,NULL AS PJ
        ,NULL AS PC
        ,NULL AS MV
        ,NULL AS IncomeCode
        ,NULL AS OutcomeCode
        ,NULL AS AccountMap
        ,NULL AS CostCenterMap
        ,NULL AS PJMap
        ,NULL AS PCMap
        ,NULL AS MVMap
        ,NULL AS IncomeCodeMap
        ,NULL AS OutcomeCodeMap
        -- receivablesInvoiceLines
      FROM
        tpka_racuni rac,
        s_vapldok vapldok,
        (SELECT id gmpkom_id FROM mgmp_komitenti WHERE sifra = pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1002'))
        gmpkom_param,
        (SELECT
           gmpkom.id kd_id,
           gmpkom.naziv kd_naziv,
           gmpkom.naz_ulice kd_naz_ulice,
           zem.sifra_2a kd_zem_sifra_2a,
           pos.broj kd_pos_broj,
           pos.naziv kd_pos_naziv,
           DECODE (zem.sifra_2a, domzem_sifra, gmpkom.oib, gmpkom.br_porezni) kd_br_porezni
         FROM
           mgmp_komitenti gmpkom,
           s_zemlje zem,
           (SELECT pra_rec_zaj.vrijednost_parametra ('0002') domzem_sifra FROM dual) param,
           m_poste pos
         WHERE
           gmpkom.zem_id = zem.id
           AND gmpkom.pos_id = pos.id (+)
        ) kd,
        s_valute val
      WHERE
        rac.id = pkarac_id_p
        AND rac.vapldok_id = vapldok.id
        AND kd.kd_id = DECODE (rac.gmpkom_id, NULL, gmpkom_param.gmpkom_id, rac.gmpkom_id)
        AND val.id = rac.val_id
      );

  receivable_invoice_header_r receivable_invoice_header_c%ROWTYPE;

  CURSOR receivable_invoice_lines_c IS
    SELECT
      rac_id_za_test,
      LineNumber
      ,Description
      ,Quantity
      ,UnitOfMeasure
      ,TRIM (TO_CHAR (UnitSellingPrice, '9999999990D99999999', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS UnitSellingPrice
      --,UnitSellingPrice
      ,TRIM (TO_CHAR (Discount, '9999999990D99999999', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS Discount
      --,Discount
      ,TRIM (TO_CHAR (OriginalSellingPrice, '9999999990D99999999', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS OriginalSellingPrice
      --,OriginalSellingPrice
      ,ConsumptionTax
      ,TRIM (TO_CHAR (TaxClassificationCode, '990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS TaxClassificationCode
      --,TaxClassificationCode
      ,TRIM (TO_CHAR (TaxAmount, '9999999990D99999999', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS TaxAmount
      --,TaxAmount
      ,ContractType
      ,ServiceCode
      ,ChannelId
      ,ProductType
      ,CompanyId
      ,Account
      ,CostCenter
      ,PJ
      ,PC
      ,MV
      ,AccountMap
      ,CostCenterMap
      ,PJMap
      ,PCMap
      ,MVMap
    FROM
      ( -- receivablesInvoiceLines
      SELECT
        pkarac.id rac_id_za_test,
        DENSE_RANK() over (ORDER BY pkaracpdv.pkarac_id, pkaracpdv.stopa_pdv) AS LineNumber
        ,'Predujam' AS Description
        ,1 AS Quantity
        ,'KOM' AS UnitOfMeasure
        ,pkaracpdv.izn_racval * 100 / (100 + pkaracpdv.stopa_pdv)
          AS UnitSellingPrice -- nije zaokružena
        ,NULL AS Discount
        ,pkaracpdv.izn_racval * 100 / (100 + pkaracpdv.stopa_pdv)
          AS OriginalSellingPrice
        ,NULL AS ConsumptionTax
        ,NVL (pkaracpdv.stopa_pdv, 0) AS TaxClassificationCode
        ,NVL ((pkaracpdv.izn_racval - pkaracpdv.izn_racval * 100 / (100 + pkaracpdv.stopa_pdv)), 0)
          AS TaxAmount
        ,NULL AS ContractType
        ,NULL AS ServiceCode
        ,NULL AS ChannelId
        ,'Services' AS ProductType
        ,NULL AS CompanyId
        ,NULL AS Account
        ,NULL AS CostCenter
        ,NULL AS PJ
        ,NULL AS PC
        ,NULL AS MV
        ,pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1004') AS AccountMap
        ,pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1003') AS CostCenterMap
        ,NULL AS PJMap
        ,NULL AS PCMap
        ,NULL AS MVMap
      FROM
        ipka_rac_pdv pkaracpdv, tpka_racuni pkarac
      WHERE
        pkaracpdv.pkarac_id = pkarac.id
        AND pkarac.id = pkarac_id_p
      ORDER BY pkaracpdv.pkarac_id, pkaracpdv.stopa_pdv
      ) ;
  receivable_invoice_lines_r receivable_invoice_lines_c%ROWTYPE;

BEGIN
  data_m := NULL;
  odgovor_m := NULL;

  OPEN receivable_invoice_header_c;
  FETCH receivable_invoice_header_c INTO receivable_invoice_header_r;
  IF receivable_invoice_header_c%NOTFOUND THEN
    CLOSE receivable_invoice_header_c;
  END IF;
  CLOSE receivable_invoice_header_c;

  APEX_JSON.initialize_clob_output;
  --APEX_JSON.open_array(); -- [
  APEX_JSON.open_object(); -- {

  APEX_JSON.write ('SourceSystem'             , receivable_invoice_header_r.SourceSystem);
  APEX_JSON.write ('SourceObjectId'           , receivable_invoice_header_r.SourceObjectId);
  IF receivable_invoice_header_r.Partner IS NOT NULL THEN
    APEX_JSON.write_raw ('Partner'            , '"' || receivable_invoice_header_r.Partner || '"');
  END IF;
  APEX_JSON.write ('PartnerType'              , receivable_invoice_header_r.PartnerType);
  IF receivable_invoice_header_r.AddressName IS NOT NULL THEN
    APEX_JSON.write_raw ('AddressName'        , '"' || receivable_invoice_header_r.AddressName || '"');
  END IF;
  IF receivable_invoice_header_r.AddressLine1 IS NOT NULL THEN
    APEX_JSON.write_raw ('AddressLine1'       , '"' || receivable_invoice_header_r.AddressLine1 || '"');
  END IF;
  IF receivable_invoice_header_r.City IS NOT NULL THEN
    APEX_JSON.write_raw ('City'               , '"' || receivable_invoice_header_r.City || '"');
  END IF;
  APEX_JSON.write ('Country'                  , receivable_invoice_header_r.Country);
  APEX_JSON.write ('PostalCode'               , receivable_invoice_header_r.PostalCode);
  APEX_JSON.write ('OIB'                      , receivable_invoice_header_r.OIB);
  APEX_JSON.write ('TaxOrganizationType'      , receivable_invoice_header_r.TaxOrganizationType);
  APEX_JSON.write ('TransactionSource'        , receivable_invoice_header_r.TransactionSource);
  IF receivable_invoice_header_r.TransactionType IS NOT NULL THEN
    APEX_JSON.write_raw ('TransactionType'            , '"' || receivable_invoice_header_r.TransactionType || '"');
  END IF;
  APEX_JSON.write ('RemittanceModel'          , receivable_invoice_header_r.RemittanceModel);
  APEX_JSON.write ('RemittanceReferenceNumber', receivable_invoice_header_r.RemittanceReferenceNumber);
  APEX_JSON.write ('TransactionNumber'        , receivable_invoice_header_r.TransactionNumber);
  APEX_JSON.write ('TransactionDate'          , receivable_invoice_header_r.TransactionDate);
  APEX_JSON.write ('InvoiceCurrencyCode'      , receivable_invoice_header_r.InvoiceCurrencyCode);
  APEX_JSON.write ('DueDate'                  , receivable_invoice_header_r.DueDate);
  IF receivable_invoice_header_r.Description IS NOT NULL THEN
    APEX_JSON.write_raw ('Description'        , '"' || receivable_invoice_header_r.Description || '"');
  END IF;
  APEX_JSON.write ('BusinessUnit'             , receivable_invoice_header_r.BusinessUnit);
  APEX_JSON.write ('Account'                  , receivable_invoice_header_r.Account);
  APEX_JSON.write ('CostCenter'               , receivable_invoice_header_r.CostCenter);
  APEX_JSON.write ('PJ'                       , receivable_invoice_header_r.PJ);
  APEX_JSON.write ('PC'                       , receivable_invoice_header_r.PC);
  APEX_JSON.write ('MV'                       , receivable_invoice_header_r.MV);
  APEX_JSON.write ('IncomeCode'               , receivable_invoice_header_r.IncomeCode);
  APEX_JSON.write ('OutcomeCode'              , receivable_invoice_header_r.OutcomeCode);
  APEX_JSON.write ('AccountMap'               , receivable_invoice_header_r.AccountMap);
  APEX_JSON.write ('CostCenterMap'            , receivable_invoice_header_r.CostCenterMap);
  APEX_JSON.write ('PJMap'                    , receivable_invoice_header_r.PJMap);
  APEX_JSON.write ('PCMap'                    , receivable_invoice_header_r.PCMap);
  APEX_JSON.write ('MVMap'                    , receivable_invoice_header_r.MVMap);
  APEX_JSON.write ('IncomeCodeMap'            , receivable_invoice_header_r.IncomeCodeMap);
  APEX_JSON.write ('OutcomeCodeMap'           , receivable_invoice_header_r.OutcomeCodeMap);
  APEX_JSON.open_array('receivablesInvoiceLines'); -- [

  OPEN receivable_invoice_lines_c;
  FETCH receivable_invoice_lines_c INTO receivable_invoice_lines_r;
  -- CLOSE receivable_invoice_lines_c;


  WHILE (receivable_invoice_lines_c%FOUND) LOOP
    APEX_JSON.open_object(); -- {
    APEX_JSON.write ('LineNumber'           , receivable_invoice_lines_r.LineNumber);
    IF receivable_invoice_lines_r.Description IS NOT NULL THEN
      APEX_JSON.write_raw ('Description'    , '"' || receivable_invoice_lines_r.Description || '"');
    END IF;
    APEX_JSON.write ('Quantity'             , receivable_invoice_lines_r.Quantity);
    APEX_JSON.write ('UnitOfMeasure'        , receivable_invoice_lines_r.UnitOfMeasure);
    APEX_JSON.write ('UnitSellingPrice'     , receivable_invoice_lines_r.UnitSellingPrice);
    APEX_JSON.write ('Discount'             , receivable_invoice_lines_r.Discount);
    APEX_JSON.write ('OriginalSellingPrice' , receivable_invoice_lines_r.OriginalSellingPrice);
    APEX_JSON.write ('ConsumptionTax'       , receivable_invoice_lines_r.ConsumptionTax);
    APEX_JSON.write ('TaxClassificationCode', receivable_invoice_lines_r.TaxClassificationCode);
    APEX_JSON.write ('TaxAmount'            , receivable_invoice_lines_r.TaxAmount);
    APEX_JSON.write ('ContractType'         , receivable_invoice_lines_r.ContractType);
    APEX_JSON.write ('ServiceCode'          , receivable_invoice_lines_r.ServiceCode);
    APEX_JSON.write ('ChannelId'            , receivable_invoice_lines_r.ChannelId);
    APEX_JSON.write ('ProductType'          , receivable_invoice_lines_r.ProductType);
    APEX_JSON.write ('CompanyId'            , receivable_invoice_lines_r.CompanyId);
    APEX_JSON.write ('Account'              , receivable_invoice_lines_r.Account);
    APEX_JSON.write ('CostCenter'           , receivable_invoice_lines_r.CostCenter);
    APEX_JSON.write ('PJ'                   , receivable_invoice_lines_r.PJ);
    APEX_JSON.write ('PC'                   , receivable_invoice_lines_r.PC);
    APEX_JSON.write ('MV'                   , receivable_invoice_lines_r.MV);
    APEX_JSON.write ('AccountMap'           , receivable_invoice_lines_r.AccountMap);
    APEX_JSON.write ('CostCenterMap'        , receivable_invoice_lines_r.CostCenterMap);
    APEX_JSON.write ('PJMap'                , receivable_invoice_lines_r.PJMap);
    APEX_JSON.write ('PCMap'                , receivable_invoice_lines_r.PCMap);
    APEX_JSON.write ('MVMap'                , receivable_invoice_lines_r.MVMap);
    APEX_JSON.close_object(); -- }
    FETCH receivable_invoice_lines_c INTO receivable_invoice_lines_r;
  END LOOP;

  IF (receivable_invoice_lines_c%ISOPEN) THEN
    CLOSE receivable_invoice_lines_c;
  END IF;

  APEX_JSON.close_array; -- ]
  APEX_JSON.close_object(); -- }

  data_m := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;

  url2_m := '/receivablesInvoices';
  ora_zaj_post (url2_m);

  ora_obradi_odg (pkaprifinodg_id_p);
  -- dbms_output.put_line (data_m);

END;

PROCEDURE ora_salji_recracn
 (pkaprifin_id_p IN lpka_prijepisi_fin.id%TYPE
 ,rac_id_p IN t_racuni.id%TYPE
 ,pkaprifinodg_id_p IN lpka_prifin_odg.id%TYPE
 ) IS
  CURSOR credit_memo_c IS
    SELECT
      SourceSystem
      ,SourceObjectId
      ,Partner
      ,PartnerType
      ,AddressName
      ,AddressLine1
      ,City
      ,Country
      ,PostalCode
      ,OIB
      ,TaxOrganizationType
      ,BusinessUnit
      ,TransactionSource
      ,TransactionType
      ,TransactionNumber
      ,PreviousTransactionNumber
      ,InvoiceCurrencyCode
      ,TO_CHAR (TransactionDate, 'YYYY-MM-DD')
        AS TransactionDate
      ,TRIM (TO_CHAR (Amount, '9999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS Amount
    FROM
      (SELECT
        rac.rac_id
        ,'ISTRATECH' AS SourceSystem
        ,pkaprifinodg_id_p AS SourceObjectId -- > lpka_prifin_odg.id
        ,kd.kd_naziv AS Partner
        ,'Customer' AS PartnerType
        ,kd.kd_naz_ulice AS AddressName
        ,kd.kd_naz_ulice AS AddressLine1
        ,kd.kd_pos_naziv AS City
        ,kd.kd_zem_sifra_2a AS Country
        ,kd.kd_pos_broj AS PostalCode
        ,kd.kd_br_porezni AS OIB
        ,NULL AS TaxOrganizationType
        ,business_unit(rac.datum) AS BusinessUnit
        ,'AZ ISTRATECH' AS TransactionSource
        ,'AZ KO Istratech' AS TransactionType
        ,pra_pka_s.br_racuna_fsus (rac.oj_sifra, rac.vdok_sifra2n, rac.rbr, rac.datum) || '/' || SUBSTR (rac.god_poslovna, 3, 2) AS TransactionNumber
        ,pra_pka_s.br_racuna_fsus (prac.oj_sifra, prac.vdok_sifra2n, prac.rbr, prac.datum) || '/' || SUBSTR (prac.god_poslovna, 3, 2) AS PreviousTransactionNumber
        ,val.sifra_3a AS InvoiceCurrencyCode
        ,rac.datum AS TransactionDate
        ,rac.iznos + pra_rec_s2.izn_predujma_racval (rac.id, rac.izn_tecaja) + pra_rec_s5.izn_napojnice_uk (rac.id, 2) AS Amount -- staviti na dvije dec sa dec.točkom --IRN 3923 iznos napojnice u valuti računa
       FROM
         t_racuni rac, t_racuni prac,
         s_vapldok vapldok,
         (SELECT id age_id FROM m_agencije WHERE sifra = pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1002')) 
         age_param,
         (SELECT
           age.id kd_id,
           age.naziv kd_naziv,
           age.naz_ulice kd_naz_ulice,
           zem.sifra_2a kd_zem_sifra_2a,
           pos.broj kd_pos_broj,
           pos.naziv kd_pos_naziv,
           DECODE (zem.sifra_2a, domzem_sifra, age.oib, age.br_porezni) kd_br_porezni
         FROM
           m_agencije age,
           s_zemlje zem,
           (SELECT pra_rec_zaj.vrijednost_parametra ('0002') domzem_sifra FROM dual) param,
           m_poste pos
         WHERE
           age.zem_id = zem.id
           AND age.pos_id = pos.id (+)
         ) kd,
         s_valute val
       WHERE
         rac.rac_id = prac.id
          AND rac.id = rac_id_p
          AND rac.vapldok_id = vapldok.id
          AND kd.kd_id = DECODE (rac.age_id, NULL, age_param.age_id, pra_rec2.agekupac_id (rac.vdok_id, rac.age_id, rac.agep_id, rac.vpdog_id))
          AND val.id = rac.val_id
          -- and rac.oznaka IN 'N'
      );
  credit_memo_r credit_memo_c%ROWTYPE;

BEGIN

  OPEN credit_memo_c;
  FETCH credit_memo_c INTO credit_memo_r;
  IF credit_memo_c%NOTFOUND THEN
    CLOSE credit_memo_c;
  END IF;
  --CLOSE credit_memo_c;

  APEX_JSON.initialize_clob_output;
  APEX_JSON.open_object(); -- {
  APEX_JSON.write ('SourceSystem'             , credit_memo_r.SourceSystem);
  APEX_JSON.write ('SourceObjectId'           , credit_memo_r.SourceObjectId);
  IF credit_memo_r.Partner IS NOT NULL THEN
    APEX_JSON.write_raw ('Partner'            , '"' || credit_memo_r.Partner || '"');
  END IF;
  APEX_JSON.write ('PartnerType'              , credit_memo_r.PartnerType);
  IF credit_memo_r.AddressName IS NOT NULL THEN
    APEX_JSON.write_raw ('AddressName'        , '"' || credit_memo_r.AddressName || '"');
  END IF;
  IF credit_memo_r.AddressLine1 IS NOT NULL THEN
    APEX_JSON.write_raw ('AddressLine1'       , '"' || credit_memo_r.AddressLine1 || '"');
  END IF;
  IF credit_memo_r.City IS NOT NULL THEN
    APEX_JSON.write_raw ('City'               , '"' || credit_memo_r.City || '"');
  END IF;
  APEX_JSON.write ('Country'                  , credit_memo_r.Country);
  APEX_JSON.write ('PostalCode'               , credit_memo_r.PostalCode);
  APEX_JSON.write ('OIB'                      , credit_memo_r.OIB);
  APEX_JSON.write ('TaxOrganizationType'      , credit_memo_r.TaxOrganizationType);
  APEX_JSON.write ('BusinessUnit'             , credit_memo_r.BusinessUnit);
  APEX_JSON.write ('TransactionSource'        , credit_memo_r.TransactionSource);
  IF credit_memo_r.TransactionType IS NOT NULL THEN
    APEX_JSON.write_raw ('TransactionType'            , '"' || credit_memo_r.TransactionType || '"');
  END IF;
  APEX_JSON.write ('TransactionNumber'        , credit_memo_r.TransactionNumber);
  APEX_JSON.write ('PreviousTransactionNumber', credit_memo_r.PreviousTransactionNumber);
  APEX_JSON.write ('InvoiceCurrencyCode'      , credit_memo_r.InvoiceCurrencyCode);
  APEX_JSON.write ('TransactionDate'          , credit_memo_r.TransactionDate);
  APEX_JSON.write ('Amount'                   , credit_memo_r.Amount);
  APEX_JSON.close_object(); -- }

  IF (credit_memo_c%ISOPEN) THEN
    CLOSE credit_memo_c;
  END IF;

  data_m := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;

  url2_m := '/creditMemo';
  ora_zaj_post (url2_m);

  ora_obradi_odg (pkaprifinodg_id_p);
  -- dbms_output.put_line (data_m);
END;

PROCEDURE ora_salji_pkapredn
 (pkaprifin_id_p IN lpka_prijepisi_fin.id%TYPE
 ,pkarac_id_p IN tpka_racuni.id%TYPE
 ,pkaprifinodg_id_p IN lpka_prifin_odg.id%TYPE
 ) IS
  CURSOR credit_memo_c IS
    SELECT
      SourceSystem
      ,SourceObjectId
      ,Partner
      ,PartnerType
      ,AddressName
      ,AddressLine1
      ,City
      ,Country
      ,PostalCode
      ,OIB
      ,TaxOrganizationType
      ,BusinessUnit
      ,TransactionSource
      ,TransactionType
      ,TransactionNumber
      ,PreviousTransactionNumber
      ,InvoiceCurrencyCode
      ,TO_CHAR (TransactionDate, 'YYYY-MM-DD')
        AS TransactionDate
      ,TRIM (TO_CHAR (Amount, '9999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS Amount
    FROM
      (SELECT
        rac.pkarac_id
        ,'ISTRATECH' AS SourceSystem
        ,pkaprifinodg_id_p AS SourceObjectId -- > lpka_prifin_odg.id
        ,kd.kd_naziv AS Partner
        ,'Customer' AS PartnerType
        ,kd.kd_naz_ulice AS AddressName
        ,kd.kd_naz_ulice AS AddressLine1
        ,kd.kd_pos_naziv AS City
        ,kd.kd_zem_sifra_2a AS Country
        ,kd.kd_pos_broj AS PostalCode
        ,kd.kd_br_porezni AS OIB
        ,NULL AS TaxOrganizationType
        ,business_unit(rac.datum) AS BusinessUnit
        ,'AZ ISTRATECH' AS TransactionSource
        ,'AZ KO Istratech' AS TransactionType
        ,pra_pka_s.br_racuna_fsus (rac.oj_sifra, rac.vdok_sifra2n, rac.rbr, rac.datum) || '/' || SUBSTR (rac.god_poslovna, 3, 2) AS TransactionNumber
        ,pra_pka_s.br_racuna_fsus (prac.oj_sifra, prac.vdok_sifra2n, prac.rbr, prac.datum) || '/' || SUBSTR (prac.god_poslovna, 3, 2) AS PreviousTransactionNumber
        ,val.sifra_3a AS InvoiceCurrencyCode
        ,rac.datum AS TransactionDate
        ,rac.iznos AS Amount -- staviti na dvije dec s dec. točkom
       FROM
         tpka_racuni rac,
         tpka_racuni prac,
         s_vapldok vapldok,
         (SELECT id gmpkom_id FROM mgmp_komitenti WHERE sifra = pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1002'))
         gmpkom_param,
         (SELECT
            gmpkom.id kd_id,
            gmpkom.naziv kd_naziv,
            gmpkom.naz_ulice kd_naz_ulice,
            zem.sifra_2a kd_zem_sifra_2a,
            pos.broj kd_pos_broj,
            pos.naziv kd_pos_naziv,
            DECODE (zem.sifra_2a, domzem_sifra, gmpkom.oib, gmpkom.br_porezni) kd_br_porezni
          FROM
            mgmp_komitenti gmpkom,
            s_zemlje zem,
            (SELECT pra_rec_zaj.vrijednost_parametra ('0002') domzem_sifra FROM dual) param,
            m_poste pos
          WHERE
            gmpkom.zem_id = zem.id
            AND gmpkom.pos_id = pos.id (+)
         ) kd,
         s_valute val
       WHERE --rac.pkarac_id = prac.id
         -- za I se mora povezati preko pkaracd_id, za N preko pkarac_id
         (CASE WHEN rac.raci_id IS NOT NULL THEN rac.pkaracd_id ELSE rac.pkarac_id END) = prac.id
         AND rac.id  = pkarac_id_p
         AND rac.vapldok_id = vapldok.id
         AND kd.kd_id = DECODE (rac.gmpkom_id, NULL, gmpkom_param.gmpkom_id, rac.gmpkom_id)
         AND val.id = rac.val_id
         -- AND rac.oznaka IN 'N'
       );
  credit_memo_r credit_memo_c%ROWTYPE;

BEGIN

  OPEN credit_memo_c;
  FETCH credit_memo_c INTO credit_memo_r;
  IF credit_memo_c%NOTFOUND THEN
    CLOSE credit_memo_c;
  END IF;
  -- CLOSE credit_memo_c;

  APEX_JSON.initialize_clob_output;
  APEX_JSON.open_object(); -- {
  APEX_JSON.write ('SourceSystem'             , credit_memo_r.SourceSystem);
  APEX_JSON.write ('SourceObjectId'           , credit_memo_r.SourceObjectId);
  IF credit_memo_r.Partner IS NOT NULL THEN
    APEX_JSON.write_raw ('Partner'            , '"' || credit_memo_r.Partner || '"');
  END IF;
  APEX_JSON.write ('PartnerType'              , credit_memo_r.PartnerType);
  IF credit_memo_r.AddressName IS NOT NULL THEN
    APEX_JSON.write_raw ('AddressName'        , '"' || credit_memo_r.AddressName || '"');
  END IF;
  IF credit_memo_r.AddressLine1 IS NOT NULL THEN
    APEX_JSON.write_raw ('AddressLine1'       , '"' || credit_memo_r.AddressLine1 || '"');
  END IF;
  IF credit_memo_r.City IS NOT NULL THEN
    APEX_JSON.write_raw ('City'               , '"' || credit_memo_r.City || '"');
  END IF;
  APEX_JSON.write ('Country'                  , credit_memo_r.Country);
  APEX_JSON.write ('PostalCode'               , credit_memo_r.PostalCode);
  APEX_JSON.write ('OIB'                      , credit_memo_r.OIB);
  APEX_JSON.write ('TaxOrganizationType'      , credit_memo_r.TaxOrganizationType);
  APEX_JSON.write ('BusinessUnit'             , credit_memo_r.BusinessUnit);
  APEX_JSON.write ('TransactionSource'        , credit_memo_r.TransactionSource);
  IF credit_memo_r.TransactionType IS NOT NULL THEN
    APEX_JSON.write_raw ('TransactionType'            , '"' || credit_memo_r.TransactionType || '"');
  END IF;
  APEX_JSON.write ('TransactionNumber'        , credit_memo_r.TransactionNumber);
  APEX_JSON.write ('PreviousTransactionNumber', credit_memo_r.PreviousTransactionNumber);
  APEX_JSON.write ('InvoiceCurrencyCode'      , credit_memo_r.InvoiceCurrencyCode);
  APEX_JSON.write ('TransactionDate'          , credit_memo_r.TransactionDate);
  APEX_JSON.write ('Amount'                   , credit_memo_r.Amount);
  APEX_JSON.close_object(); -- }

  IF (credit_memo_c%ISOPEN) THEN
    CLOSE credit_memo_c;
  END IF;

  data_m := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;

  url2_m := '/creditMemo';
  ora_zaj_post (url2_m);

  ora_obradi_odg (pkaprifinodg_id_p);
  -- dbms_output.put_line (data_m);
END;

PROCEDURE ora_salji_recracnap
 (pkaprifin_id_p IN lpka_prijepisi_fin.id%TYPE
 ,racnap_id_p IN t_rac_nap.id%TYPE
 ,pkaprifinodg_id_p IN lpka_prifin_odg.id%TYPE
 ) IS
  CURSOR receipts_c IS
    SELECT
      racnap_id
     ,SourceSystem
     ,SourceObjectId
     ,ReceiptMethod
     --,ReceiptNumber
     ,TRIM (TO_CHAR (Amount, '9999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
       AS Amount
     ,CurrencyCode
     ,TO_CHAR (ReceiptDate, 'YYYY-MM-DD')
       AS ReceiptDate
     ,TO_CHAR (GlDate, 'YYYY-MM-DD')
       AS GlDate
     ,Partner
     ,AddressName
     ,AddressLine1
     ,City
     ,Country
     ,PostalCode
     ,OIB
     ,PartnerType
     ,TaxOrganizationType
     ,BusinessUnit
     --,ReceiptLines
    FROM
     (SELECT
       racnap.id racnap_id
       ,'ISTRATECH' AS SourceSystem
       ,pkaprifinodg_id_p AS SourceObjectId -- > lpka_prifin_odg.id
       ,Receipts_ReceiptMethod (racnap.vpla_id) AS ReceiptMethod -- IRN 3544
       -- ,/*racnap*/ pra_pka_s.br_racuna_fsus (rac.oj_sifra, rac.vdok_sifra2n, rac.rbr, rac.datum) AS ReceiptNumber
       ,racnap.izn_placanja + NVL (racnap.izn_napojnice, 0) Amount --IRN 3923
       ,val.sifra_3a AS CurrencyCode
       ,rac.datum AS ReceiptDate
       ,rac.datum AS GlDate
       ,kd.kd_naziv AS Partner
       ,kd.kd_naz_ulice AS AddressName
       ,kd.kd_naz_ulice AS AddressLine1
       ,kd.kd_pos_naziv AS City
       ,kd.kd_zem_sifra_2a AS Country
       ,kd.kd_pos_broj AS PostalCode
       ,kd.kd_br_porezni AS OIB
       ,'Customer' AS PartnerType
       ,DECODE (kd.kd_br_porezni, NULL, 'INDIVIDUAL', 'CORPORATION') AS TaxOrganizationType
       ,business_unit(rac.datum) AS BusinessUnit
      FROM
        t_rac_nap racnap, t_racuni rac,
        s_valute val,
        (SELECT id age_id FROM m_agencije WHERE sifra = pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1002')) -- pra_zmp_zaj.zmppar_vrijednost ('FINORA, '1001'))
        age_param,
        (SELECT age.id kd_id,
                age.naziv kd_naziv,
                age.naz_ulice kd_naz_ulice,
                zem.sifra_2a kd_zem_sifra_2a,
                pos.broj kd_pos_broj,
                pos.naziv kd_pos_naziv,
                DECODE (zem.sifra_2a, domzem_sifra, age.oib, age.br_porezni) kd_br_porezni
           FROM m_agencije age,
                s_zemlje zem,
                (SELECT pra_rec_zaj.vrijednost_parametra ('0002') domzem_sifra FROM dual) param,
                m_poste pos
          WHERE age.zem_id = zem.id
            AND age.pos_id = pos.id (+)
        ) kd
      WHERE
        racnap.rac_id = rac.id
        AND racnap.id = racnap_id_p
        AND racnap.val_id = val.id -- IRN 3544
        AND kd.kd_id = DECODE (rac.age_id, NULL, age_param.age_id, pra_rec2.agekupac_id (rac.vdok_id, rac.age_id, rac.agep_id, rac.vpdog_id))
      );
  receipts_r receipts_c%ROWTYPE;

  CURSOR receipt_lines_c IS
    SELECT
      racnap_id
      ,TRIM (TO_CHAR (Amount, '9999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS Amount
      --Amount
      ,CurrencyCode
      ,TO_CHAR (ApplicationDate, 'YYYY-MM-DD') AS ApplicationDate
      ,TransactionNumber
    FROM
      (
      SELECT -- ReceiptLines
        rac.id rac_id_za_test
        ,racnap.id racnap_id
        ,ROUND ((racnap.izn_placanja + NVL (racnap.izn_napojnice, 0)) * racnap.izn_tecaja / rac.izn_tecaja, 2) AS Amount --IRN 3923
        ,val.sifra_3a AS CurrencyCode
        ,rac.datum AS ApplicationDate
        ,pra_pka_s.br_racuna_fsus (rac.oj_sifra, rac.vdok_sifra2n, rac.rbr, rac.datum) || '/' || SUBSTR (rac.god_poslovna, 3, 2) AS TransactionNumber
      FROM
        t_rac_nap racnap, t_racuni rac,
        s_valute val
      WHERE
        racnap.rac_id = rac.id
        AND racnap.id = racnap_id_p
        AND rac.val_id = val.id
      ORDER BY racnap.id
      ) ReceiptLines;
  receipt_lines_r receipt_lines_c%ROWTYPE;
BEGIN
  data_m := NULL;
  odgovor_m := NULL;
  APEX_JSON.initialize_clob_output;

  IF (NOT receipt_lines_c%ISOPEN) THEN
    OPEN receipt_lines_c;
  END IF;
  FETCH receipt_lines_c INTO receipt_lines_r;

  WHILE (receipt_lines_c%FOUND) LOOP
    APEX_JSON.open_object(); -- {
    OPEN receipts_c;
    FETCH receipts_c INTO receipts_r;
    IF receipts_c%NOTFOUND THEN
      CLOSE receipts_c;
    END IF;

    APEX_JSON.write ('SourceSystem'             , receipts_r.SourceSystem);
    APEX_JSON.write ('SourceObjectId'           , receipts_r.SourceObjectId);
    APEX_JSON.write ('ReceiptMethod'            , receipts_r.ReceiptMethod);
    APEX_JSON.write ('ReceiptNumber'            , receipts_r.racnap_id);
    APEX_JSON.write ('Amount'                   , receipts_r.Amount);
    APEX_JSON.write ('CurrencyCode'             , receipts_r.CurrencyCode);
    APEX_JSON.write ('ReceiptDate'              , receipts_r.ReceiptDate);
    APEX_JSON.write ('GlDate'                   , receipts_r.GlDate);
    IF receipts_r.Partner IS NOT NULL THEN
      APEX_JSON.write_raw ('Partner'            , '"' || receipts_r.Partner || '"');
    END IF;
    IF receipts_r.AddressName IS NOT NULL THEN
      APEX_JSON.write_raw ('AddressName'        , '"' || receipts_r.AddressName || '"');
    END IF;
    IF receipts_r.AddressLine1 IS NOT NULL THEN
      APEX_JSON.write_raw ('AddressLine1'       , '"' || receipts_r.AddressLine1 || '"');
    END IF;
    IF receipts_r.City IS NOT NULL THEN
      APEX_JSON.write_raw ('City'               , '"' || receipts_r.City || '"');
    END IF;
    APEX_JSON.write ('Country'                  , receipts_r.Country);
    APEX_JSON.write ('PostalCode'               , receipts_r.PostalCode);
    APEX_JSON.write ('OIB'                      , receipts_r.OIB);
    APEX_JSON.write ('PartnerType'              , receipts_r.PartnerType);
    APEX_JSON.write ('TaxOrganizationType'      , receipts_r.TaxOrganizationType);
    APEX_JSON.write ('BusinessUnit'             , receipts_r.BusinessUnit);
    APEX_JSON.open_array('ReceiptLines'); -- [
    APEX_JSON.open_object(); -- {
    APEX_JSON.write ('Amount'           , receipt_lines_r.Amount);
    APEX_JSON.write ('CurrencyCode'     , receipt_lines_r.CurrencyCode);
    APEX_JSON.write ('ApplicationDate'  , receipt_lines_r.ApplicationDate);
    APEX_JSON.write ('TransactionNumber', receipt_lines_r.TransactionNumber);
    APEX_JSON.close_object(); -- }
    APEX_JSON.close_array; -- ]
    APEX_JSON.close_object(); -- }
    FETCH receipt_lines_c INTO receipt_lines_r;
    CLOSE receipts_c;
  END LOOP;

  IF (receipt_lines_c%ISOPEN) THEN
    CLOSE receipt_lines_c;
  END IF;
  IF (receipts_c%ISOPEN) THEN
    CLOSE receipts_c;
  END IF;
  -- dbms_output.put_line ('recracnap');
  data_m := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;

  url2_m := '/receipts';
  ora_zaj_post (url2_m);

  ora_obradi_odg (pkaprifinodg_id_p);
  -- dbms_output.put_line (data_m);
END;

PROCEDURE ora_salji_pkaprednap
 (pkaprifin_id_p IN lpka_prijepisi_fin.id%TYPE
 ,pkaracnap_id_p IN tpka_rac_nap.id%TYPE
 ,pkaprifinodg_id_p IN lpka_prifin_odg.id%TYPE
 ) IS
  CURSOR receipts_c IS
    SELECT
       pkaracnap_id
      ,SourceSystem
      ,SourceObjectId
      ,ReceiptMethod
      ,ReceiptNumber
      ,TRIM (TO_CHAR (Amount, '9999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS Amount
      ,CurrencyCode
      ,TO_CHAR (ReceiptDate, 'YYYY-MM-DD')
        AS ReceiptDate
      ,TO_CHAR (GlDate, 'YYYY-MM-DD')
        AS GlDate
      ,Partner
      ,AddressName
      ,AddressLine1
      ,City
      ,Country
      ,PostalCode
      ,OIB
      ,PartnerType
      ,TaxOrganizationType
      ,BusinessUnit
      --,ReceiptLines
    FROM
      (SELECT
         racnap.id pkaracnap_id
        ,'ISTRATECH' AS SourceSystem
        ,pkaprifinodg_id_p AS SourceObjectId -- > lpka_prifin_odg.id
        ,Receipts_ReceiptMethod (racnap.vpla_id) AS ReceiptMethod -- IRN 3543
        ,racnap.id AS ReceiptNumber
        ,racnap.iznos Amount
        ,val.sifra_3a AS CurrencyCode
        ,rac.datum AS ReceiptDate
        ,rac.datum AS GlDate
        ,kd.kd_naziv AS Partner
        ,kd.kd_naz_ulice AS AddressName
        ,kd.kd_naz_ulice AS AddressLine1
        ,kd.kd_pos_naziv AS City
        ,kd.kd_zem_sifra_2a AS Country
        ,kd.kd_pos_broj AS PostalCode
        ,kd.kd_br_porezni AS OIB
        ,'Customer' AS PartnerType
        ,DECODE (kd.kd_br_porezni, NULL, 'INDIVIDUAL', 'CORPORATION') AS TaxOrganizationType
        ,business_unit(rac.datum) AS BusinessUnit
       FROM
         tpka_rac_nap racnap, tpka_racuni rac,
         s_valute val,
         (SELECT id gmpkom_id FROM mgmp_komitenti WHERE sifra = pra_zmp_zaj.zmppar_vrijednost ('FINORA', '1002'))
         gmpkom_param,
         (SELECT
           gmpkom.id kd_id,
           gmpkom.naziv kd_naziv,
           gmpkom.naz_ulice kd_naz_ulice,
           zem.sifra_2a kd_zem_sifra_2a,
           pos.broj kd_pos_broj,
           pos.naziv kd_pos_naziv,
           DECODE (zem.sifra_2a, domzem_sifra, gmpkom.oib, gmpkom.br_porezni) kd_br_porezni
         FROM
           mgmp_komitenti gmpkom,
           s_zemlje zem,
           (SELECT pra_rec_zaj.vrijednost_parametra ('0002') domzem_sifra FROM dual) param,
           m_poste pos
         WHERE
           gmpkom.zem_id = zem.id
           AND gmpkom.pos_id = pos.id (+)
         ) kd
       WHERE racnap.id = pkaracnap_id_p
         AND racnap.pkarac_id = rac.id
         AND racnap.val_id = val.id -- IRN 3543
         AND kd.kd_id = DECODE (rac.gmpkom_id, NULL, gmpkom_param.gmpkom_id, rac.gmpkom_id)
    );
  receipts_r receipts_c%ROWTYPE;

  CURSOR receipt_lines_c IS
    SELECT
      rac_id_za_test,
      pkaracnap_id
      ,TRIM (TO_CHAR (Amount, '9999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
        AS Amount
      --,Amount
      ,CurrencyCode
      ,TO_CHAR (ApplicationDate, 'YYYY-MM-DD') AS ApplicationDate
      ,TransactionNumber
    FROM
      (
      SELECT -- ReceiptLines
        rac.id rac_id_za_test,
        racnap.id pkaracnap_id
       ,ROUND (racnap.iznos * racnap.izn_tecaja / rac.izn_tecaja, 2) AS Amount
       ,val.sifra_3a AS CurrencyCode
       ,rac.datum AS ApplicationDate
       ,pra_pka_s.br_racuna_fsus (rac.oj_sifra, rac.vdok_sifra2n, rac.rbr, rac.datum) || '/' || SUBSTR (rac.god_poslovna, 3, 2) AS TransactionNumber
      FROM
        tpka_rac_nap racnap, tpka_racuni rac,
        s_valute val
      WHERE
        racnap.pkarac_id = rac.id
        AND rac.val_id = val.id
        AND racnap.id = pkaracnap_id_p
      ORDER BY racnap.id
      ) ReceiptLines;
  receipt_lines_r receipt_lines_c%ROWTYPE;
BEGIN
  data_m := NULL;
  odgovor_m := NULL;
  APEX_JSON.initialize_clob_output;

  IF (NOT receipt_lines_c%ISOPEN) THEN
    OPEN receipt_lines_c;
  END IF;
  FETCH receipt_lines_c INTO receipt_lines_r;

  WHILE (receipt_lines_c%FOUND) LOOP
    APEX_JSON.open_object(); -- {
    OPEN receipts_c;
    FETCH receipts_c INTO receipts_r;
    IF receipts_c%NOTFOUND THEN
      CLOSE receipts_c;
    END IF;

    APEX_JSON.write ('SourceSystem'             , receipts_r.SourceSystem);
    APEX_JSON.write ('SourceObjectId'           , receipts_r.SourceObjectId);
    APEX_JSON.write ('ReceiptMethod'            , receipts_r.ReceiptMethod);
    APEX_JSON.write ('ReceiptNumber'            , receipts_r.pkaracnap_id);
    APEX_JSON.write ('Amount'                   , receipts_r.Amount);
    APEX_JSON.write ('CurrencyCode'             , receipts_r.CurrencyCode);
    APEX_JSON.write ('ReceiptDate'              , receipts_r.ReceiptDate);
    APEX_JSON.write ('GlDate'                   , receipts_r.GlDate);
    IF receipts_r.Partner IS NOT NULL THEN
      APEX_JSON.write_raw ('Partner'            , '"' || receipts_r.Partner || '"');
    END IF;
    IF receipts_r.AddressName IS NOT NULL THEN
      APEX_JSON.write_raw ('AddressName'        , '"' || receipts_r.AddressName || '"');
    END IF;
    IF receipts_r.AddressLine1 IS NOT NULL THEN
      APEX_JSON.write_raw ('AddressLine1'       , '"' || receipts_r.AddressLine1 || '"');
    END IF;
    IF receipts_r.City IS NOT NULL THEN
      APEX_JSON.write_raw ('City'               , '"' || receipts_r.City || '"');
    END IF;
    APEX_JSON.write ('Country'                  , receipts_r.Country);
    APEX_JSON.write ('PostalCode'               , receipts_r.PostalCode);
    APEX_JSON.write ('OIB'                      , receipts_r.OIB);
    APEX_JSON.write ('PartnerType'              , receipts_r.PartnerType);
    APEX_JSON.write ('TaxOrganizationType'      , receipts_r.TaxOrganizationType);
    APEX_JSON.write ('BusinessUnit'             , receipts_r.BusinessUnit);
    APEX_JSON.open_array('ReceiptLines'); -- [
    APEX_JSON.open_object(); -- {
    APEX_JSON.write ('Amount'           , receipt_lines_r.Amount);
    APEX_JSON.write ('CurrencyCode'     , receipt_lines_r.CurrencyCode);
    APEX_JSON.write ('ApplicationDate'  , receipt_lines_r.ApplicationDate);
    APEX_JSON.write ('TransactionNumber', receipt_lines_r.TransactionNumber);
    APEX_JSON.close_object(); -- }
    APEX_JSON.close_array; -- ]
    APEX_JSON.close_object(); -- }
    FETCH receipt_lines_c INTO receipt_lines_r;
    CLOSE receipts_c;
  END LOOP;

  IF (receipt_lines_c%ISOPEN) THEN
    CLOSE receipt_lines_c;
  END IF;
  IF (receipts_c%ISOPEN) THEN
    CLOSE receipts_c;
  END IF;

  data_m := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;

  url2_m := '/receipts';
  ora_zaj_post (url2_m);

  ora_obradi_odg (pkaprifinodg_id_p);
  -- dbms_output.put_line (data_m);
END;

PROCEDURE ora_salji_recracnapn
 (pkaprifin_id_p IN lpka_prijepisi_fin.id%TYPE
 ,racnap_id_p IN t_rac_nap.id%TYPE
 ,pkaprifinodg_id_p IN lpka_prifin_odg.id%TYPE
 ) IS
  CURSOR reversal_receipt_c IS
    SELECT
      rac_id
      ,SourceSystem
      ,SourceObjectId
      ,ReceiptNumber
      ,TO_CHAR (ReversalDate, 'YYYY-MM-DD') ReversalDate
      ,TO_CHAR (ReversalGlDate, 'YYYY-MM-DD') ReversalGlDate
      ,BusinessUnit
    FROM
      (
      SELECT
        rac.rac_id rac_id
        ,'ISTRATECH' AS SourceSystem
        ,pkaprifinodg_id_p AS SourceObjectId
        ,racnap.id AS ReceiptNumber
        ,rac.datum AS ReversalDate
        ,rac.datum AS ReversalGlDate
        ,business_unit(rac.datum) AS BusinessUnit
      FROM
        t_rac_nap racnap, t_racuni rac
      WHERE
        racnap.rac_id = rac.id
        AND racnap.id = racnap_id_p
      );
  reversal_receipt_r reversal_receipt_c%ROWTYPE;

BEGIN
  data_m := NULL;
  odgovor_m := NULL;

  OPEN reversal_receipt_c;
  FETCH reversal_receipt_c INTO reversal_receipt_r;
  IF reversal_receipt_c%NOTFOUND THEN
    CLOSE reversal_receipt_c;
  END IF;
  -- CLOSE reversal_receipt_c;

  APEX_JSON.initialize_clob_output;
  -- APEX_JSON.open_array(); -- [
  APEX_JSON.open_object(); -- {

  APEX_JSON.write ('SourceSystem'   , reversal_receipt_r.SourceSystem);
  APEX_JSON.write ('SourceObjectId' , reversal_receipt_r.SourceObjectId);
  APEX_JSON.write ('ReceiptNumber'  , reversal_receipt_r.ReceiptNumber);
  APEX_JSON.write ('ReversalDate'   , reversal_receipt_r.ReversalDate);
  APEX_JSON.write ('ReversalGlDate' , reversal_receipt_r.ReversalGlDate);
  APEX_JSON.write ('BusinessUnit'   , reversal_receipt_r.BusinessUnit);

  APEX_JSON.close_object(); -- }
  IF (reversal_receipt_c%ISOPEN) THEN
    CLOSE reversal_receipt_c;
  END IF;

  data_m := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;

  url2_m := '/reversalReceipt';
  ora_zaj_post (url2_m);

  ora_obradi_odg (pkaprifinodg_id_p);
  -- dbms_output.put_line (data_m);
END;

PROCEDURE ora_salji_pkaprednapn
 (pkaprifin_id_p IN lpka_prijepisi_fin.id%TYPE
 ,pkaracnap_id_p IN tpka_rac_nap.id%TYPE
 ,pkaprifinodg_id_p IN lpka_prifin_odg.id%TYPE
 ) IS
  CURSOR reversal_receipt_c IS
    SELECT
      pkarac_id
      ,SourceSystem
      ,SourceObjectId
      ,ReceiptNumber
      ,TO_CHAR (ReversalDate, 'YYYY-MM-DD') ReversalDate
      ,TO_CHAR (ReversalGlDate, 'YYYY-MM-DD') ReversalGlDate
      ,BusinessUnit
    FROM
      (
      SELECT
        pkarac.id pkarac_id
        ,'ISTRATECH' AS SourceSystem
        ,pkaprifinodg_id_p AS SourceObjectId
        ,pkaracnap.id AS ReceiptNumber
        ,pkarac.datum AS ReversalDate
        ,pkarac.datum AS ReversalGlDate
        ,business_unit(pkarac.datum) AS BusinessUnit
      FROM
        tpka_rac_nap pkaracnap, tpka_racuni pkarac
      WHERE
        pkaracnap.pkarac_id = pkarac.id
        AND pkaracnap.id = pkaracnap_id_p
      );
  reversal_receipt_r reversal_receipt_c%ROWTYPE;

BEGIN
  data_m := NULL;
  odgovor_m := NULL;

  OPEN reversal_receipt_c;
  FETCH reversal_receipt_c INTO reversal_receipt_r;
  IF reversal_receipt_c%NOTFOUND THEN
    CLOSE reversal_receipt_c;
  END IF;
  -- CLOSE reversal_receipt_c;

  APEX_JSON.initialize_clob_output;
  -- APEX_JSON.open_array(); -- [
  APEX_JSON.open_object(); -- {

  APEX_JSON.write ('SourceSystem'  , reversal_receipt_r.SourceSystem);
  APEX_JSON.write ('SourceObjectId', reversal_receipt_r.SourceObjectId);
  APEX_JSON.write ('ReceiptNumber' , reversal_receipt_r.ReceiptNumber);
  APEX_JSON.write ('ReversalDate'  , reversal_receipt_r.ReversalDate);
  APEX_JSON.write ('ReversalGlDate', reversal_receipt_r.ReversalGlDate);
  APEX_JSON.write ('BusinessUnit'  , reversal_receipt_r.BusinessUnit);

  APEX_JSON.close_object(); -- }
  IF (reversal_receipt_c%ISOPEN) THEN
    CLOSE reversal_receipt_c;
  END IF;

  data_m := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;

  url2_m := '/reversalReceipt';
  ora_zaj_post (url2_m);

  ora_obradi_odg (pkaprifinodg_id_p);
  -- dbms_output.put_line (data_m);
END;

PROCEDURE ora_izvrsi_prijepis_job
 (br_dana_poc_p IN NUMBER := NULL
 ,br_dana_zav_p IN NUMBER := NULL
 )
IS
  dat_poc_l DATE;
  dat_zav_l DATE;
  email_prvi_l szmp_parametri.vrijednost%TYPE;
  email_drugi_l szmp_parametri.vrijednost%TYPE;
  sender_l    szmp_parametri.vrijednost%TYPE;
  sender_h_l s_poruke.tekst%TYPE;
  broj_gresaka_l NUMBER;
  detalj_obrade_l VARCHAR2 (100);
BEGIN
  email_prvi_l := pra_pka_zaj.vrijednost_parametra ('1091');
  email_drugi_l := pra_pka_zaj.vrijednost_parametra ('1092');

  -- ako je sysdate = početni datum rada na aplikaciji nema se šta prepisati. 
  IF pra_zmp_s.dat_pocetni_0036 = TRUNC (SYSDATE) THEN
    RETURN;
  END IF;
  -- Ako je upisan broj dana onda se razdoblje određuje prema njemu
  -- ako nije upisan broj dana onda je razdoblje od (prethodnog završnog datuma + 1 odnosno početnog dana rada na aplikaciji ako je taj veći) do jučer
  IF br_dana_poc_p IS NULL THEN
    SELECT GREATEST (NVL (MAX (dat_zav), TRUNC (SYSDATE) - 2) + 1, pra_zmp_s.dat_pocetni_0036) INTO dat_poc_l FROM lpka_prijepisi_fin;
  ELSE
    dat_poc_l := TRUNC (SYSDATE) - br_dana_poc_p;
  END IF;
  IF br_dana_zav_p IS NULL THEN
    dat_zav_l := TRUNC (SYSDATE) - 1;
  ELSE
    dat_zav_l := TRUNC (SYSDATE) - br_dana_zav_p;
  END IF;

  detalj_obrade_l := '(' || TO_CHAR (dat_poc_l, 'DD.MM.YYYY') || ' - ' || TO_CHAR (dat_zav_l, 'DD.MM.YYYY') || ')';

  pra_pka_finora.ora_izvrsi_prijepis (
    TRUNC (dat_poc_l),
    TRUNC (dat_zav_l),
    NULL,
    NULL);

  SELECT COUNT(1) INTO broj_gresaka_l
    FROM lpka_prifin_odg
   WHERE pkaprifin_id = (
         SELECT MAX (id)
           FROM lpka_prijepisi_fin
          WHERE pkaparpri_id = (
                SELECT id FROM spka_parametri_prijepisa WHERE sifra = 'RACUNI')
         )
     AND lpka_prifin_odg.odgovor != 'Success';

  IF broj_gresaka_l <> 0 THEN
    IF UPPER (email_prvi_l) <> 'NEBITNO' OR UPPER (email_drugi_l) <> 'NEBITNO' THEN
      sender_l := pra_zmp_zaj.zmppar_vrijednost ('ZMP', '0905'); -- noreply
      sender_h_l := pra_zmp_zaj.poruka ('ZMP', 'III0192'); -- Baza
    END IF;

    IF UPPER (email_prvi_l) <> 'NEBITNO' THEN
      pra_pka2.salji_html_email (
        recipient_p   => email_prvi_l,
        recipient_h_p => NULL,
        sender_p      => sender_l,
        sender_h_p    => sender_h_l,
        bcc_p         => NULL,
        subject_p     => SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') || ' - Prijepis računa ' || detalj_obrade_l  || '- ' || pra_zmp_zaj.poruka ('REC', 'REC0190'), -- Greška!
        text_p        =>  '(' || broj_gresaka_l  || ')') ;
    END IF;

    IF UPPER (email_drugi_l) <> 'NEBITNO' THEN
      pra_pka2.salji_html_email (
        recipient_p   => email_drugi_l,
        recipient_h_p => NULL,
        sender_p      => sender_l,
        sender_h_p    => sender_h_l,
        bcc_p         => NULL,
        subject_p     => SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') || ' - Prijepis računa ' || detalj_obrade_l  || '- ' || pra_zmp_zaj.poruka ('REC', 'REC0190'), -- Greška!
        text_p        =>  '(' || broj_gresaka_l  || ')' );
    END IF;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    IF UPPER (email_prvi_l) <> 'NEBITNO' OR UPPER (email_drugi_l) <> 'NEBITNO' THEN
      sender_l := pra_zmp_zaj.zmppar_vrijednost ('ZMP', '0905'); -- noreply
      sender_h_l := pra_zmp_zaj.poruka ('ZMP', 'III0192'); -- Baza
    END IF;

    IF UPPER (email_prvi_l) <> 'NEBITNO' THEN
      pra_pka2.salji_html_email (
        recipient_p   => email_prvi_l,
        recipient_h_p => NULL,
        sender_p      => sender_l,
        sender_h_p    => sender_h_l,
        bcc_p         => NULL,
        subject_p     => SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') || ' - Prijepis računa ' || detalj_obrade_l  || '- ' || pra_zmp_zaj.poruka ('REC', 'REC0190'), -- Greška!
        text_p        => SQLERRM);
    END IF;

    IF UPPER (email_drugi_l) <> 'NEBITNO' THEN
      pra_pka2.salji_html_email (
        recipient_p   => email_drugi_l,
        recipient_h_p => NULL,
        sender_p      => sender_l,
        sender_h_p    => sender_h_l,
        bcc_p         => NULL,
        subject_p     => SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') || ' - Prijepis računa ' || detalj_obrade_l  || '- ' || pra_zmp_zaj.poruka ('REC', 'REC0190'), -- Greška!
        text_p        => SQLERRM);
    END IF;
    RAISE;
END;


END;
/
SHOW ERROR