PROMPT Creating Package Body 'PRA_ERACUN2'
CREATE OR REPLACE PACKAGE BODY PRA_ERACUN2 IS
-- Private Declaration

domval_sifra_m CONSTANT szmp_parametri.vrijednost%TYPE := pra_zmp_zaj.zmppar_vrijednost ('ZMP', '0001');
domzem_sifra_m CONSTANT szmp_parametri.vrijednost%TYPE := pra_zmp_zaj.zmppar_vrijednost ('ZMP', '0002');
jm_rasporedi_c CONSTANT NUMBER(3, 2) := 1 / 100;

data_m CLOB;
log_filename_m VARCHAR2(100);
odgovor_m CLOB;
url_m s_parametri_prijepisa.staza%TYPE;
erac_password_m s_vlasnik.erac_password%TYPE;
clob_m CLOB;

-- 4096
racblob_id_m t_racuni_blob.id%TYPE;
CopyIndicator_m VARCHAR2(5) := 'false';

-- REC param 1175: Oznaka sustava za slanje e-računa: 1 = Moj-eRačun, 2 = EBA, 3 = FINA, 5 = Pondi ili nebitno.
tip_m szmp_parametri.vrijednost%TYPE := pra_zmp_zaj.zmppar_vrijednost ('REC', '1175');

rac_row_m        t_racuni%ROWTYPE;
pkarac_row_m     tpka_racuni%ROWTYPE;
stopa_odbitka_m  NUMBER; -- 07.01.2026.
sifra_vatex_m    s_vpdogadjaja.sifra_vatex%TYPE;
oib_operatera_m  s_vlasnik.oib%TYPE;
invoice_id_m     VARCHAR2(30);  -- broj računa npr. 1234-801-1126
racval_sifra_m   s_valute.sifra_3a%TYPE;
vapldok_oznaka_m s_vapldok.oznaka%TYPE;

-- napomena za prolazne stavke npr. turističku pristojbu
TaxExemptionReason0_m VARCHAR2(1000) := 'Oslobođeno poreza na temelju čl. 33. st. 3. Zakona o PDV-u';

charge_tpristojba_m t_racuni.iznos%TYPE;
charge_reason_m     tpka_rac_isp.naziv%TYPE; -- 07.01.2026.
charge_ppot_m       t_racuni.iznos%TYPE;
charge_pnaknada_m   t_racuni.iznos%TYPE;
charges_m           t_racuni.iznos%TYPE;
charges2_m          t_racuni.iznos%TYPE;
AllowanceCharge0_m  XMLTYPE; -- turistička pristojba
AllowanceCharge1_m  XMLTYPE; -- porez na potrošnju
AllowanceCharge2_m  XMLTYPE; -- povratna naknada

sum_tax_domval_m     t_racuni.iznos%TYPE;
sum_neto_domval_m    t_racuni.iznos%TYPE;
sum_taxable_racval_m t_racuni.iznos%TYPE;

HRTaxSubtotal_m XMLTYPE;
UBLExtensions_m XMLTYPE;
AdditionalDocumentReference_m XMLTYPE;

-- END Private Declaration


PROCEDURE IZRADI_ERACUN_BLOB
 (RAC_ID_P IN t_racuni.id%TYPE
 ,PKARAC_ID_P IN tpka_racuni.id%TYPE
 ,FILENAME_P IN VARCHAR2
 ,ZAH_PONUDA_P IN VARCHAR2 := NULL
 ,NARUDZBENICA_P IN VARCHAR2 := NULL
 ,UGOVOR_P IN VARCHAR2 := NULL
 ,PROJEKT_P IN VARCHAR2 := NULL
 ,NAPOMENA_P IN VARCHAR2 := NULL
 ,RACBLOB_ID_P OUT t_racuni_blob.id%TYPE
 )
 IS
-- poziva se iz modula pka2070.fmb - Računi za predujam, rec2250.fmb - Računi i pka2050.fmb - Računi

  postoji_racblob_l NUMBER(1) := 0;

  eracun_xml_l XMLTYPE;
  blob_l BLOB;
  clob_l CLOB;

  FUNCTION clob2blob (clob_p CLOB) RETURN BLOB IS
    -- ne prolazi na 9-ki
    blob2_l        BLOB;
    src_offset_l   NUMBER;
    dest_offset_l  NUMBER;
    amount_l       NUMBER;
    lang_context_l NUMBER := DBMS_LOB.default_lang_ctx;
    warning_l      NUMBER;
  BEGIN
    DBMS_LOB.createtemporary (blob2_l, TRUE);
    src_offset_l := 1;
    dest_offset_l := 1;
    amount_l := DBMS_LOB.getlength (clob_p);
    DBMS_LOB.converttoblob (
      blob2_l,
      clob_p,
      amount_l,
      src_offset_l,
      dest_offset_l,
      1,
      lang_context_l,
      warning_l);
    RETURN blob2_l;
  END;

  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

  -- REC param 1175: Oznaka sustava za slanje e-računa: 1 = Moj-eRačun, 2 = EBA, 3 = FINA, 5 = Pondi ili nebitno.
  tip_m := pra_zmp_zaj.zmppar_vrijednost ('REC', '1175');
  IF tip_m = 'nebitno' THEN
    RETURN;
  END IF;

  IF rac_id_p IS NOT NULL THEN
    SELECT DECODE (COUNT (1), 0, 0, -1) INTO postoji_racblob_l
      FROM t_racuni_blob
     WHERE rac_id = rac_id_p
       AND oznaka = 'xml'
       AND id_vanjsus IS NOT NULL;

  ELSIF pkarac_id_p IS NOT NULL THEN
    SELECT DECODE (COUNT (1), 0, 0, -1) INTO postoji_racblob_l
      FROM t_racuni_blob
     WHERE pkarac_id = pkarac_id_p
       AND oznaka = 'xml'
       AND id_vanjsus IS NOT NULL;

  END IF;

  IF postoji_racblob_l = -1 THEN
    CopyIndicator_m := 'true'; -- 4096
  END IF;

  IF tip_m IN ('1', '2', '3', '5') THEN  -- 1 = Moj-eRačun, 2 = EBA, 3 = FINA, 5 = Pondi
    IF rac_id_p IS NOT NULL THEN
      eracun_xml_l := rec_invoice_xml (rac_id_p, zah_ponuda_p, narudzbenica_p, ugovor_p, projekt_p, napomena_p, tip_m);
      clob_l := eracun_xml_l.getclobval();
    ELSIF pkarac_id_p IS NOT NULL THEN
      eracun_xml_l := pka_invoice_xml (pkarac_id_p, zah_ponuda_p, narudzbenica_p, ugovor_p, projekt_p, napomena_p, tip_m);
      clob_l := eracun_xml_l.getclobval();
    END IF;

    -- ispravi tagove koji su predugi za Oracle
    SELECT REPLACE (
             REPLACE (
               REPLACE (clob_l, 'cac:OriginatorDocRef', 'cac:OriginatorDocumentReference'),
                          'cac:AdditionalDocRef', 'cac:AdditionalDocumentReference'),
                          'cbc:EmbeddedDocument', 'cbc:EmbeddedDocumentBinaryObject')
      INTO clob_l
      FROM dual;
  ELSE
    RETURN;
  END IF;

  blob_l := clob2blob (clob_l);
  racblob_id_p := pra_rec_zaj.sekvenca (NULL);

  INSERT INTO t_racuni_blob (id, rac_id, pkarac_id, datoteka, oznaka, opis)
    VALUES (racblob_id_p, rac_id_p, pkarac_id_p, blob_l, 'xml', filename_p || '.xml');

  COMMIT;

  pra_pka2.clob_to_table (clob_l, filename_p || '.xml', racblob_id_p, 'ERACUN'); -- 4096; jednostavnije za čitanje nego iz t_racuni_blob

END;
FUNCTION REC_INVOICE_XML
 (RAC_ID_P IN t_racuni.id%TYPE
 ,ZAH_PONUDA_P IN VARCHAR2 := NULL
 ,NARUDZBENICA_P IN VARCHAR2 := NULL
 ,UGOVOR_P IN VARCHAR2 := NULL
 ,PROJEKT_P IN VARCHAR2 := NULL
 ,NAPOMENA_P IN VARCHAR2 := NULL
 ,TIP_P IN VARCHAR2
 )
 RETURN XMLTYPE
 IS
-- poziva se iz izradi_eracun_blob
  OrderReference_l               XMLTYPE;
  OriginatorDocumentReference_l  XMLTYPE;
  ContractDocumentReference_l    XMLTYPE;
  ProjectReference_l             XMLTYPE;
  BillingReference_l             XMLTYPE;
  AccountingSupplierParty_l      XMLTYPE;
  AccountingCustomerParty_l      XMLTYPE;
  PaymentMeans_l                 XMLTYPE;
  InvoiceTaxTotal_l              XMLTYPE;
  LegalMonetaryTotal_l           XMLTYPE;

  Invoice_l                      XMLTYPE;
  InvoiceLines_l                 XMLTYPE;
  TaxSubtotals_l                 XMLTYPE;

  racbruto_racval_l              t_racuni.iznos%TYPE;
  predujam_racval_l              t_racuni.iznos%TYPE;
BEGIN

  IF rac_id_p IS NULL THEN
    RETURN NULL;
  END IF;

  SELECT * INTO rac_row_m FROM t_racuni WHERE id = rac_id_p;

  sifra_vatex_m := NULL;
  IF rac_row_m.vpdog_id IS NOT NULL THEN
    SELECT MIN (sifra_vatex) INTO sifra_vatex_m FROM s_vpdogadjaja WHERE id = rac_row_m.vpdog_id;
  END IF;

  oib_operatera_m := pra_pka_i3fiskal.oib_test ('45892556322'); -- 4096

  SELECT
    xmlelement ("cac:AccountingSupplierParty",
      xmlelement ("cac:Party",
        xmlelement ("cbc:EndpointID", xmlattributes ('9934' "schemeID"), vla.oib),
        xmlelement ("cac:PartyIdentification",
          xmlelement ("cbc:ID", '9934:' || vla.oib)
          -- 4096; za sada nije moguća identifikacija prodavatelja via npr. GLN
        ),
        xmlelement ("cac:PartyName",
          xmlelement ("cbc:Name", so.naziv) -- 18.01.2026.
        ),
        xmlelement ("cac:PostalAddress",
          xmlelement ("cbc:StreetName", so.naz_ulice),
          xmlelement ("cbc:CityName", pos.naziv),
          xmlelement ("cbc:PostalZone", pos.broj),
          xmlelement ("cac:Country",
            xmlelement ("cbc:IdentificationCode", domzem_sifra_m)
          )
        ),
        xmlelement ("cac:PartyTaxScheme",
          xmlelement ("cbc:CompanyID", domzem_sifra_m || vla.oib),
          xmlelement ("cac:TaxScheme",
            xmlelement ("cbc:ID", 'VAT')
          )
        ),
        xmlelement ("cac:PartyLegalEntity",
          xmlelement ("cbc:RegistrationName", vla.naziv),
          xmlelement ("cbc:CompanyID", vla.oib),
          DECODE (vla.opis_ztd, NULL, NULL, -- 18.01.2026.
            xmlelement ("cbc:CompanyLegalForm", REPLACE (vla.opis_ztd, CHR(10), ' '))
          ) 
        ),
        DECODE (so.imep_sefarec || so.email, NULL, NULL, -- 30.11.
          xmlelement ("cac:Contact",
            DECODE (so.imep_sefarec, NULL, NULL,
              xmlelement ("cbc:Name", so.imep_sefarec)
            ),
            DECODE (so.email, NULL, NULL,
              xmlelement ("cbc:ElectronicMail", so.email)
            )
          )
        )
      ),
      xmlelement ("cac:SellerContact", -- 4096
        (SELECT xmlelement ("cbc:ID", DECODE (TrebaOib, 0, vla.oib, NVL (OibOper, oib_operatera_m))) -- oib operatera
           FROM
             (
             SELECT
               MAX (sifra) AS OibOper,
               MAX (ozn_ima_oib) AS TrebaOib
             FROM
               m_korisnici
             WHERE
               rac_row_m.unos_korisnik = naziv
             )
        ),
        xmlelement ("cbc:Name", rac_row_m.unos_korisnik) -- oznaka operatera
      )
    ),
    xmlelement ("cac:PaymentMeans",
      xmlelement ("cbc:PaymentMeansCode", 30),
      /* Specifikacija, str. 67:
      10 - Gotovina
      30 - Kreditni transfer
      48 - Bankovna kartica
      49 - Izravno terećenje
      */
      DECODE (rac_row_m.poziv_na_broj, NULL, NULL, xmlelement ("cbc:PaymentID", 'HR99' || ' ' || rac_row_m.poziv_na_broj)),
      xmlelement ("cac:PayeeFinancialAccount",
        -- CHR(9) - horizontal tab. CHR(10) - line feed. CHR(11) - vertical tab. CHR(12) - form feed. CHR(13) - carriage return. CHR(32) - space.
        xmlelement ("cbc:ID", TRANSLATE (urac.iban, 'A' || chr(9) || chr(10) || chr(11) || chr(12) || chr(13) || chr(32), 'A'))
      )
    ),
    TO_NUMBER (rac_row_m.rbr) || '-' || rac_row_m.oj_sifra || '-' || rac_row_m.vdok_sifra2n, -- 4096
    pra_rec_s2.iznos_racuna_bruto (rac_row_m.id, rac_row_m.iznos, rac_row_m.izn_tecaja), -- u valuti računa
    pra_rec_s2.izn_predujma_racval (rac_row_m.id, rac_row_m.izn_tecaja), -- u valuti računa
    val.sifra_3a
  INTO
    AccountingSupplierParty_l,
    PaymentMeans_l,
    invoice_id_m,
    racbruto_racval_l,
    predujam_racval_l,
    racval_sifra_m
  FROM
    s_vlasnik vla,
    m_so so,
    m_poste pos,
    m_uracuni urac,
    s_valute val
  WHERE
    rac_row_m.so_id = so.id
    AND so.pos_id = pos.id
    AND rac_row_m.urac_id = urac.id (+)
    AND rac_row_m.val_id = val.id;

  AccountingCustomerParty_l := rec_customer_xml (rac_id_p); -- 4096
  -- rekapitulacija poreza u domaćoj valuti i sum_tax_domval_m i sum_neto_domval_m i charge_ppot_m i charge_pnaknada_m
  -- 12.01.2026. računanje charge_tpristojba_m preseljeno iz rec_stavke_xml u rec_taxes_xml
  TaxSubtotals_l := rec_taxes_xml (rac_id_p);
  -- stavke u valuti računa (-- 12.01.2026. i charge_tpristojba_m) i sum_taxable_racval_m
  InvoiceLines_l := rec_stavke_xml (rac_id_p);

  -- vidi ChargeTotalAmount
  charges_m := ROUND ((NVL (charge_tpristojba_m, 0) + NVL (charge_ppot_m, 0) + NVL (charge_pnaknada_m, 0)) / rac_row_m.izn_tecaja, 2);
  -- vidi OutOfScopeOfVATAmount
  charges2_m := ROUND ((NVL (charge_ppot_m, 0) + NVL (charge_pnaknada_m, 0)) / rac_row_m.izn_tecaja, 2);

  SELECT
    DECODE (narudzbenica_p, NULL, NULL,
      xmlelement ("cac:OrderReference", xmlelement ("cbc:ID", narudzbenica_p))), -- narudžbenica
    DECODE (zah_ponuda_p, NULL, NULL,
      xmlelement ("cac:OriginatorDocRef", xmlelement ("cbc:ID", zah_ponuda_p))), -- zahtjev za ponudu
    DECODE (ugovor_p, NULL, NULL,
      xmlelement ("cac:ContractDocumentReference", xmlelement ("cbc:ID", ugovor_p))), -- ugovor
    DECODE (projekt_p, NULL, NULL,
      xmlelement ("cac:ProjectReference", xmlelement ("cbc:ID", projekt_p))), -- projekt
    -- AllowanceCharge
    DECODE (NVL (charge_tpristojba_m, 0), 0, NULL,
      xmlelement ("cac:AllowanceCharge",
        xmlelement ("cbc:ChargeIndicator", 'true'),
        xmlelement ("cbc:AllowanceChargeReason", 'Turistička pristojba'), -- turistička pristojba
        xmlelement ("cbc:Amount", xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (charge_tpristojba_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cac:TaxCategory",
          xmlelement ("cbc:ID", 'E'),
          xmlelement ("cbc:Name", 'HR:E'), -- 4096
          xmlelement ("cbc:Percent", TRIM (TO_CHAR (0, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          xmlelement ("cbc:TaxExemptionReason", TaxExemptionReason0_m),
          xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
        )
      )),
    DECODE (NVL (charge_ppot_m, 0), 0, NULL,
      xmlelement ("cac:AllowanceCharge",
        xmlelement ("cbc:ChargeIndicator", 'true'),
        xmlelement ("cbc:AllowanceChargeReason", '#HR:PP#'), -- porez na potrošnju
        xmlelement ("cbc:Amount", xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (charge_ppot_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cac:TaxCategory",
          xmlelement ("cbc:ID", 'E'),
          xmlelement ("cbc:Name", 'HR:PP'), -- 4096
          xmlelement ("cbc:Percent", TRIM (TO_CHAR (0, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          xmlelement ("cbc:TaxExemptionReason", '#HR:PP#'), -- 4096
          xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
        )
      )),
    DECODE (NVL (charge_pnaknada_m, 0), 0, NULL,
      xmlelement ("cac:AllowanceCharge",
        xmlelement ("cbc:ChargeIndicator", 'true'),
        xmlelement ("cbc:AllowanceChargeReason", '#HR:POVNAK#'), -- povratna naknada
        xmlelement ("cbc:Amount", xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (charge_pnaknada_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cac:TaxCategory",
          xmlelement ("cbc:ID", 'E'),
          xmlelement ("cbc:Name", 'HR:POVNAK'), -- 4096
          xmlelement ("cbc:Percent", TRIM (TO_CHAR (0, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          xmlelement ("cbc:TaxExemptionReason", '#HR:POVNAK#'), -- 4096
          xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
        )
      )),
    xmlelement ("cac:TaxTotal",
      xmlelement ("cbc:TaxAmount", xmlattributes (domval_sifra_m "currencyID"), TRIM (TO_CHAR (sum_tax_domval_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      TaxSubtotals_l
    ),
    xmlelement ("cac:LegalMonetaryTotal",
      xmlelement ("cbc:LineExtensionAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (sum_taxable_racval_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      xmlelement ("cbc:TaxExclusiveAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (ROUND (sum_neto_domval_m / rac_row_m.izn_tecaja, 2), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      xmlelement ("cbc:TaxInclusiveAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (racbruto_racval_l, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      xmlelement ("cbc:ChargeTotalAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (charges_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      xmlelement ("cbc:PrepaidAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (predujam_racval_l, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      xmlelement ("cbc:PayableAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (rac_row_m.iznos, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,''')))
    )
  INTO
    OrderReference_l,
    OriginatorDocumentReference_l,
    ContractDocumentReference_l,
    ProjectReference_l,
    AllowanceCharge0_m, -- turistička pristojba
    AllowanceCharge1_m, -- porez na potrošnju
    AllowanceCharge2_m, -- povratna naknada
    InvoiceTaxTotal_l,
    LegalMonetaryTotal_l
  FROM dual;

  UBLExtensions_m := zaj_prosirenja_xml;
  AdditionalDocumentReference_m := zaj_prilozi_xml ('T_RACUNI', rac_id_p, invoice_id_m);

  BillingReference_l := rec_billref_xml; -- 4096

  SELECT
    xmlelement ("Invoice",
      xmlattributes (
        'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' "xmlns",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' "xmlns:cac",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' "xmlns:cbc",
        'urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2' "xmlns:cct",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' "xmlns:ext",
        'urn:mfin.gov.hr:schema:xsd:HRExtensionAggregateComponents-1' "xmlns:hrextac", -- 4096
        'urn:oasis:names:specification:ubl:schema:xsd:UnqualifiedDataTypes-2' "xmlns:p3", -- 4096
        'urn:oasis:names:specification:ubl:schema:xsd:SignatureAggregateComponents-2' "xmlns:sac",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonSignatureComponents-2' "xmlns:sig",
        'http://www.w3.org/2001/XMLSchema-instance' "xmlns:xsi", -- 4096
        'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2 ../xsd/ubl/maindoc/UBL-Invoice-2.1.xsd ' "xsi:schemaLocation" -- 4096
      ),
      UBLExtensions_m, -- 4096, Proširenja
      xmlelement ("cbc:CustomizationID", 'urn:cen.eu:en16931:2017#compliant#urn:mfin.gov.hr:cius-2025:1.0#conformant#urn:mfin.gov.hr:ext-2025:1.0'), -- 4096
      xmlelement ("cbc:ProfileID", 'P1'),
      /* Specifikacija, str. 2: 3.2 Podržani zahtjevi poslovnog procesa
        --
        P1 - Fakturiranje isporuka dobara i usluga preko narudžbi na temelju ugovora
        P2 - Periodicno fakturiranje isporuka na temelju ugovora
        P3 - Fakturiranje isporuka preko nepredvidene narudžbe
        P4 - Placanje predujma (avansno placanje)
        P5 - Placanje na licu mjesta
        P6 - Placanje prije isporuke na temelju narudžbe
        P7 - Racuni s referencom na otpremnicu
        P8 - Racuni s referencom na otpremnicu i primku
        P9 - Odobrenje ili negativno fakturiranje
        P10 - Korektivno fakturiranje
        P11 - Parcijalno i završno fakturiranje
        P12 - Samoizdavanje racuna
      */
      xmlelement ("cbc:ID", invoice_id_m),
      DECODE (CopyIndicator_m, 'true', xmlelement ("cbc:CopyIndicator", CopyIndicator_m), NULL), -- 4096
      xmlelement ("cbc:IssueDate", TO_CHAR (rac_row_m.unos_dat, 'RRRR-MM-DD')),
      xmlelement ("cbc:IssueTime", TO_CHAR (rac_row_m.unos_dat, 'HH24:MI:SS')), -- 4096
      xmlelement ("cbc:DueDate", TO_CHAR (rac_row_m.dat_dospijeca, 'RRRR-MM-DD')),
      xmlelement ("cbc:InvoiceTypeCode", '380'),
      /* Specifikacija, str. 23: iz liste UNTDID 1001
        --
        82 - Račun za mjerene usluge
        325 - Predračun
        326 - Parcijalni račun
        380 - Komercijalni račun
        381 - Odobrenje
        383 - Terećenje
        384 - Korektivni račun
        386 - Račun za predujam
        394 - Račun za leasing
      */
      DECODE (rac_row_m.napomena, NULL, NULL, xmlelement ("cbc:Note", rac_row_m.napomena)),
      xmlelement ("cbc:DocumentCurrencyCode", racval_sifra_m),
      /* Specifikacija, str. 19:
        "
        U računu će se koristiti samo jedna valuta, osim za ukupni iznos PDV u računovodstvenoj valuti (BT-111)
        u skladu s člankom 230 Direktive 2006/112 / EZ o PDV-u.
        Popisi važećih valuta su registrirani kod ISO 4217 Agencije za održavanje "Kodovi za prezentiranje valuta i sredstava".
        "
      */
      DECODE (napomena_p, -- neka vrijednost koju izdavatelj računa upisuje na zahtjev i za potrebe kupca
        NULL, NULL,
        xmlelement ("cbc:BuyerReference", napomena_p)),
      OrderReference_l,
      BillingReference_l, -- 4096
      OriginatorDocumentReference_l,
      ContractDocumentReference_l,
      AdditionalDocumentReference_m, -- 4096
      ProjectReference_l,
      AccountingSupplierParty_l,
      AccountingCustomerParty_l,
      xmlelement ("cac:Delivery",
        xmlelement ("cbc:ActualDeliveryDate", TO_CHAR (rac_row_m.datum, 'RRRR-MM-DD'))), -- 4096
      PaymentMeans_l,
      AllowanceCharge0_m, -- turistička pristojba
      AllowanceCharge1_m, -- porez na potrošnju
      AllowanceCharge2_m, -- povratna naknada
      InvoiceTaxTotal_l,
      LegalMonetaryTotal_l,
      Invoice_l,
      InvoiceLines_l
    )
  INTO Invoice_l
  FROM dual;

  IF tip_p IN ('1', '2', '5') THEN -- Moj-eRacun, EBA, Pondi
    SELECT xmltype ('<?xml version="1.0" encoding="UTF-8"?>' || Invoice_l.getclobval())
      INTO Invoice_l
      FROM dual;
  ELSIF tip_p = '3' THEN -- FINA
    SELECT xmltype ('<?xml version="1.0" encoding="utf-8" standalone="yes"?>' || Invoice_l.getclobval())
      INTO Invoice_l
      FROM dual;
  END IF;

  RETURN Invoice_l;
END;
FUNCTION REC_BILLREF_XML
 RETURN XMLTYPE
 IS
-- 4096
  BillingReference_l XMLTYPE;
BEGIN

  /*
  <cac:BillingReference>
    <cac:InvoiceDocumentReference>
      <cbc:ID>1-P1-1</cbc:ID>
      <cbc:IssueDate>2025-05-01</cbc:IssueDate>
      <cbc:DocumentDescription>
        Plaćeno po računu za predujam: (1-P1-1)
        Osnovica PDV 25%: 100.00 EUR
        PDV 25%: 25.00 EUR
        Ukupno predujam: 125.00 EUR
      </cbc:DocumentDescription>
    </cac:InvoiceDocumentReference>
  </cac:BillingReference>
  */

  FOR doc_c IN (
    SELECT
      1 ozn_poretka,
      TO_NUMBER (pkarac.rbr) || '-' || pkarac.oj_sifra || '-' || pkarac.vdok_sifra2n AS id,
      pkarac.unos_dat AS IssueDate,
      'Račun za iskorišteni predujam ' || TRIM (TO_CHAR (pkarac.iznos, '9999999999999990D99')) || ' ' || val.sifra_3a AS Description
    FROM
      tpka_racuni pkarac,
      s_valute val
    WHERE
      pkarac.raci_id = rac_row_m.id
      AND pkarac.val_id = val.id
    UNION ALL
    SELECT
      2 ozn_poretka,
      TO_NUMBER (rac.rbr) || '-' || rac.oj_sifra || '-' || rac.vdok_sifra2n AS id,
      rac.unos_dat AS IssueDate,
      'Poništeni račun za isporuku ' || TRIM (TO_CHAR (rac.iznos, '9999999999999990D99')) || ' ' || val.sifra_3a AS Description
    FROM
      t_racuni rac,
      s_valute val
    WHERE
      rac.id = rac_row_m.rac_id
      AND rac.val_id = val.id
    ORDER BY 1, 2)
  LOOP
    SELECT
      xmlagg (
        xmlconcat (BillingReference_l,
          xmlelement ("cac:BillingReference",
            xmlelement ("cac:InvoiceDocumentReference",
              xmlelement ("cbc:ID", doc_c.id),
              xmlelement ("cbc:IssueDate", TO_CHAR (doc_c.IssueDate, 'RRRR-MM-DD')),
              xmlelement ("cbc:DocumentDescription", doc_c.Description)
            )
          )
        )
      )
    INTO BillingReference_l
    FROM dual;
  END LOOP;

  RETURN BillingReference_l;

END;
FUNCTION REC_CUSTOMER_XML
 (RAC_ID_P IN t_racuni.id%TYPE
 )
 RETURN XMLTYPE
 IS
-- poziva se iz rec_invoice_xml
  kupac_id_l  m_agencije.id%TYPE;
  email_niz_l VARCHAR2(400);
  AccountingCustomerParty_l XMLTYPE;
BEGIN

  kupac_id_l := pra_rec2.agek_id (rac_row_m.age_id, rac_row_m.agep_id);

  FOR agegzem_c IN (
    SELECT email
      FROM m_age_gzem
     WHERE age_id = kupac_id_l
       AND LOWER (napomena) LIKE '%e-račun%')
  LOOP
    IF email_niz_l IS NOT NULL THEN
      email_niz_l := email_niz_l || ';';
    END IF;
    email_niz_l := email_niz_l || agegzem_c.email;
  END LOOP;

  SELECT
    xmlelement ("cac:AccountingCustomerParty",
      xmlelement ("cac:Party",
        DECODE (kupac.ozn_identifikatora, 'GLN', -- IRN 4096
          xmlelement ("cbc:EndpointID", xmlattributes ('0088' "schemeID"), kupac.gln),
          xmlelement ("cbc:EndpointID", xmlattributes (kupac.sheme_id "schemeID"), kupac.CompanyID)
        ),
        xmlelement ("cac:PartyIdentification",
          DECODE (kupac.ozn_identifikatora, 'GLN', -- IRN 4096
            xmlelement ("cbc:ID", xmlattributes ('0088' "schemeID"), kupac.gln), -- IRN 4096
            xmlelement ("cbc:ID", kupac.sheme_id || ':' || kupac.CompanyID || DECODE (kupac.pj_sifra, NULL, NULL, '::HR99:' || kupac.pj_sifra))
          )
        ),
        xmlelement ("cac:PostalAddress",
          DECODE (kupac.ulica, NULL, NULL,  -- 30.11.
            xmlelement ("cbc:StreetName", kupac.ulica)
          ),
          DECODE (kupac.mjesto, NULL, NULL, -- 30.11.
            xmlelement ("cbc:CityName", kupac.mjesto)
          ),
          DECODE (kupac.posta, NULL, NULL,  -- 30.11.
            xmlelement ("cbc:PostalZone", kupac.posta)
          ),
          xmlelement ("cac:Country",
            xmlelement ("cbc:IdentificationCode", kupac.zem_sifra_2a)
          )
        ),
        xmlelement ("cac:PartyTaxScheme",
          xmlelement ("cbc:CompanyID", kupac.br_porezni),
          xmlelement ("cac:TaxScheme",
            xmlelement ("cbc:ID", 'VAT')
          )
        ),
        xmlelement ("cac:PartyLegalEntity",
          xmlelement ("cbc:RegistrationName", kupac.naziv),
          xmlelement ("cbc:CompanyID", kupac.CompanyID) -- IRN 4096 samo za Reverse charge izbaciti?
        ),
        DECODE (kupac.odg_osoba || kupac.email, NULL, NULL, -- 30.11.
          xmlelement ("cac:Contact",
            DECODE (kupac.odg_osoba, NULL, NULL,
              xmlelement ("cbc:Name", kupac.odg_osoba)
            ),
            DECODE (kupac.email, NULL, NULL,
              xmlelement ("cbc:ElectronicMail", kupac.email)
            )
          )
        )
      )
    )
  INTO
    AccountingCustomerParty_l
  FROM (
    SELECT
      DECODE (gmpkomad_age.id, NULL, NVL (gmpkom.sifra_pj, age.sifra_pj), gmpkomad_age.sifra_pj) AS pj_sifra,
      DECODE (gmpkomad_age.id, NULL, age.naziv, gmpkomad_age.naziv) AS naziv,
      DECODE (gmpkomad_age.id, NULL, age.naz_ulice, gmpkomad_age.naz_ulice) AS ulica,
      DECODE (gmpkomad_age.id, NULL, pos.naziv, gmpkomad_age.pos_naziv) AS mjesto,
      DECODE (gmpkomad_age.id, NULL, pos.broj, gmpkomad_age.pos_broj) AS posta,
      DECODE (gmpkomad_age.id, NULL, zem.sifra_2a, gmpkomad_age.zem_sifra_2a) AS zem_sifra_2a,
      DECODE (zem.sifra_2a, domzem_sifra_m, domzem_sifra_m || age.oib, age.br_porezni) AS br_porezni,
      DECODE (zem.sifra_2a, domzem_sifra_m, age.oib, age.br_porezni) AS CompanyID,
      DECODE (gmpkomad_age.id, NULL, NVL (email_niz_l, age.email), gmpkomad_age.email) AS email,
      DECODE (gmpkomad_age.id, NULL, NVL (gmpkom.imep_osobeodg, age.imep_osobeodg), gmpkomad_age.odg_osoba) AS odg_osoba,
      NVL (gmpkom.ozn_identifikatora, age.ozn_identifikatora) AS ozn_identifikatora, -- 4096
      DECODE (gmpkomad_age.id, NULL, NVL (gmpkom.gln, age.gln), gmpkomad_age.gln) AS gln, -- 4096
      DECODE (zem.sifra_2a, 'SI', '9949', '9934') AS sheme_id -- 4096
    FROM
      m_agencije age,
      mgmp_komitenti gmpkom,
      s_zemlje zem,
      m_poste pos,
      (
      SELECT
        gmpkomad.id AS id,
        gmpkomad.gmpkom_id AS gmpkom_id,
        gmpkomad.sifra_pj AS sifra_pj,
        gmpkomad.naziv AS naziv,
        gmpkomad.email AS email,
        gmpkomad.naz_ulice AS naz_ulice,
        gmpkomad.imep_osobeodg AS odg_osoba,
        pos.broj AS pos_broj,
        pos.naziv AS pos_naziv,
        zem.sifra_2a AS zem_sifra_2a,
        gmpkomad.gln -- 4096
      FROM
        mgmp_kom_adrese gmpkomad,
        s_zemlje zem,
        m_poste pos
      WHERE
        gmpkomad.id = rac_row_m.gmpkomad_id
        AND zem.id = gmpkomad.zem_id
        AND pos.id (+) = gmpkomad.pos_id
      ) gmpkomad_age
    WHERE
      age.id = kupac_id_l
      AND zem.id = age.zem_id
      AND pos.id (+) = age.pos_id
      AND gmpkom.id (+) = age.gmpkom_id
      AND gmpkomad_age.gmpkom_id (+) = age.gmpkom_id
    ) kupac;

  RETURN (AccountingCustomerParty_l);

END;
FUNCTION REC_TAXES_XML
 (RAC_ID_P IN t_racuni.id%TYPE
 )
 RETURN XMLTYPE
 IS
-- poziva se iz rec_invoice_xml
  TaxSubtotals_l XMLTYPE;
  ozn_e_l NUMBER;
  -- 12.01.2026.
  usl_napomena2_l s_usluge.napomena2%TYPE;
BEGIN

  sum_tax_domval_m := NULL;
  sum_neto_domval_m := NULL;
  charge_ppot_m := NULL;
  charge_pnaknada_m := NULL;
  HRTaxSubtotal_m := NULL;

  -- 12.01.2026.; preseljeno iz funkcije rec_stavke_xml
  -- 4096; turističku pristojbu prikazati u cac:AllowanceCharge
  charge_tpristojba_m := NULL;
  SELECT
    SUM (
      ROUND (
        (
        ROUND (cijena_usluge * stopa_placanja / 100 * izn_tecaja / rac_row_m.izn_tecaja, 2)
          * kol_usluge
          * DECODE (obr_po_danu, -1, dat_zav - dat_poc - obr_zdana, 1)
          - izn_odbitka
        ) * 100 / (100 + stopa_pdv), 2
        )
      )
  INTO
    charge_tpristojba_m
  FROM
    t_troskovi
  WHERE
    rac_id = rac_id_p
    AND usl_id IN (SELECT usl_id FROM s_vplabp_tz);


  -- 12.01.2026. dodatni ili zamjenski cbc:TaxExemptionReason
  -- ako su npr. turistička pristojba i No Show na istom računu

  TaxExemptionReason0_m := 'Oslobođeno poreza na temelju čl. 33. st. 3. Zakona o PDV-u'; -- 18.01.2026.

  SELECT
    MAX (usl.napomena2)
  INTO
    usl_napomena2_l
  FROM
    t_troskovi tro,
    s_usluge usl
  WHERE
    tro.rac_id = rac_id_p
    AND tro.usl_id = usl.id
    AND tro.stopa_pdv = 0;

  IF usl_napomena2_l IS NOT NULL THEN
    IF NVL (charge_tpristojba_m, 0) <> 0 THEN
      TaxExemptionReason0_m := SUBSTR (TaxExemptionReason0_m || '; ' || usl_napomena2_l, 1, 1000);
    ELSE
      TaxExemptionReason0_m := SUBSTR (usl_napomena2_l, 1, 1000);
    END IF;
  END IF;
  -- KRAJ 12.01.2026. dodatni ili zamjenski cbc:TaxExemptionReason

  SELECT
    xmlagg (
      xmlelement ("cac:TaxSubtotal",
        xmlelement ("cbc:TaxableAmount",
          xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (osnovica, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cbc:TaxAmount",
          xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (porez, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cac:TaxCategory",
          xmlelement ("cbc:ID", TaxCategory_ID),
          xmlelement ("cbc:Name", TaxCategory_Name),
          xmlelement ("cbc:Percent",
            TRIM (TO_CHAR (stopa, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          DECODE (TaxCategory_ID, 'E',
            DECODE (sifra_vatex_m, NULL,
               xmlelement ("cbc:TaxExemptionReason", TaxExemptionReason0_m),
               xmlelement ("cbc:TaxExemptionReasonCode", sifra_vatex_m)),
            NULL),
          xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
        )
      )
    ),
    SUM (porez),
    SUM (osnovica),
    SUM (izn_ppot),
    SUM (izn_pnaknade),
    MAX (ozn_e)
  INTO
    TaxSubtotals_l,
    sum_tax_domval_m,
    sum_neto_domval_m,
    charge_ppot_m,
    charge_pnaknada_m,
    ozn_e_l
  FROM
    (
    SELECT
      TaxCategory_ID,     -- TaxCategory/ID
      TaxCategory_Name,   -- TaxCategory/Name
      stopa,
      SUM (neto) AS osnovica,
      SUM (pdv) AS porez,
      SUM (DECODE (ozn_ppot, 1, neto, 0)) AS izn_ppot,
      SUM (DECODE (ozn_pnaknade, 1, neto, 0)) AS izn_pnaknade,
      DECODE (TaxCategory_ID, 'E', 1, 0) AS ozn_e
    FROM
      (
      SELECT
        DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1', 'E', 'S') AS TaxCategory_ID,
        DECODE (rac_pdv.stopa_pdv,
          25, 'HR:PDV25',
          13, 'HR:PDV13',
           5, 'HR:PDV5',
           0, DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1', 'HR:E', 'HR:Z')
          ) AS TaxCategory_Name,
        rac_pdv.stopa_pdv AS stopa,
        ROUND (rac_pdv.iznos, 2) AS neto,
        rac_pdv.izn_domval - ROUND (rac_pdv.iznos, 2) AS pdv,
        0 AS ozn_ppot,
        0 AS ozn_pnaknade
      FROM
        i_rac_pdv rac_pdv,
        s_vpisporukaz vpispz,
        szmp_spisporuka zmpspisp
      WHERE
        rac_pdv.rac_id = rac_id_p
        AND rac_pdv.vpispz_id = vpispz.id
        AND zmpspisp.id = vpispz.zmpspisp_id
      UNION ALL
      SELECT
        'E',                -- TaxCategory_ID
        'HR:E',             -- TaxCategory_Name
        0,                  -- stopa
        SUM (izn_ppot),     -- neto
        0,                  -- pdv
        1,                  -- ozn_ppot
        0                   -- ozn_pnaknade
      FROM
        tpka_rnkasa
      WHERE
        rac_id = rac_id_p
        AND NVL (stopa_ppot, 0) <> 0
      GROUP BY
        stopa_ppot
      UNION ALL
      -- IRN 3818
      SELECT
        'E',                -- TaxCategory_ID
        'HR:E',             -- TaxCategory_Name
        0,                  -- stopa
        SUM (izn_pnaknade), -- neto
        0,                  -- pdv
        0,                  -- ozn_ppot
        1                   -- ozn_pnaknade
      FROM
        tpka_rnkasa
      WHERE
        rac_id = rac_id_p
        AND NVL (izn_pnaknade, 0) <> 0
      HAVING SUM (izn_pnaknade) <> 0
      )
    GROUP BY
      TaxCategory_ID,
      TaxCategory_Name,
      stopa
    ORDER BY stopa DESC  -- 4096 dodala DESC
    );

  IF ozn_e_l = 0 THEN
    RETURN (TaxSubtotals_l); -- nije potrebno slati HRTaxSubtotal
  END IF;

  -- 4096; HRTaxSubtotal
  SELECT
    xmlagg (
      xmlelement ("hrextac:HRTaxSubtotal",
        xmlelement ("cbc:TaxableAmount",
          xmlattributes (domval_sifra_m "currencyID"), TRIM (TO_CHAR (osnovica, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cbc:TaxAmount",
          xmlattributes (domval_sifra_m "currencyID"), TRIM (TO_CHAR (porez, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("hrextac:HRTaxCategory",
          xmlelement ("cbc:ID", TaxCategory_ID),
          xmlelement ("cbc:Name", TaxCategory_Name),
          xmlelement ("cbc:Percent", TRIM (TO_CHAR (stopa, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          DECODE (TaxExemptionReason, NULL, NULL, xmlelement ("cbc:TaxExemptionReason", TaxExemptionReason)),
          xmlelement ("hrextac:HRTaxScheme", xmlelement ("cbc:ID", TaxScheme_ID))
        )
      )
    )
  INTO
    HRTaxSubtotal_m
  FROM
    (
    SELECT
      TaxCategory_ID,        -- TaxCategory/ID
      TaxCategory_Name,      -- TaxCategory/Name
      stopa,
      TaxExemptionReason,    -- TaxCategory/TaxExemptionReason
      TaxScheme_ID,          -- TaxScheme/ID
      SUM (neto) AS osnovica,
      SUM (pdv) AS porez
    FROM
      (
      SELECT
        DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1', 'E', 'S') AS TaxCategory_ID,
        DECODE (rac_pdv.stopa_pdv,
          25, 'HR:PDV25',
          13, 'HR:PDV13',
           5, 'HR:PDV5',
           0, DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1', 'HR:E', 'HR:Z')
          ) AS TaxCategory_Name,
        rac_pdv.stopa_pdv AS stopa,
        DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1', NVL (sifra_vatex_m, TaxExemptionReason0_m)) AS TaxExemptionReason, -- vrsta poreznog događaja ili turistička pristojba
        'VAT' AS TaxScheme_ID,
        ROUND (rac_pdv.iznos, 2) AS neto,
        rac_pdv.izn_domval - ROUND (rac_pdv.iznos, 2) AS pdv
      FROM
        i_rac_pdv rac_pdv,
        s_vpisporukaz vpispz,
        szmp_spisporuka zmpspisp
      WHERE
        rac_pdv.vpispz_id = vpispz.id
        AND zmpspisp.id = vpispz.zmpspisp_id
        AND rac_pdv.rac_id = rac_id_p
      UNION ALL
      SELECT
        'O',                -- TaxCategory_ID
        'HR:PP',            -- TaxCategory_Name
        0,                  -- stopa
        '#HR:PP#',          -- TaxExemptionReason
        'LOC',              -- TaxScheme_ID
        charge_ppot_m,      -- neto
        0                   -- pdv
      FROM
        dual
      WHERE
        NVL (charge_ppot_m, 0) <> 0
      UNION ALL
      SELECT
        'O',                -- TaxCategory_ID
        'HR:POVNAK',        -- TaxCategory_Name
        0,                  -- stopa
        '#HR:POVNAK#',      -- TaxExemptionReason
        'OTH',              -- TaxScheme_ID
        charge_pnaknada_m,  -- neto
        0                   -- pdv
      FROM
        dual
      WHERE
        NVL (charge_pnaknada_m, 0) <> 0
      )
    GROUP BY
      TaxCategory_ID,
      TaxCategory_Name,
      stopa,
      TaxExemptionReason,
      TaxScheme_ID
    ORDER BY stopa DESC
    );

  RETURN (TaxSubtotals_l);

END;
FUNCTION REC_STAVKE_XML
 (RAC_ID_P IN t_racuni.id%TYPE
 )
 RETURN XMLTYPE
 IS
-- poziva se iz rec_invoice_xml
  InvoiceLines_l XMLTYPE;
  ruz_l t_racuni.iznos%TYPE := 0;
  i NUMBER (5) := 0;
BEGIN

  sum_taxable_racval_m := NULL;

  pripremi_rec_tab_ruz (rac_id_p);

  FOR tro_c IN (
    SELECT
      1 AS oznaka,
      SUM (tro.kol_usluge * DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.obr_zdana - tro.dat_poc, 1)) AS kol_usluge,
      tro.obr_po_danu AS obr_po_danu,
      tro.dat_poc AS dat_poc,
      tro.dat_zav AS dat_zav,
      tro.br_uputnice AS br_uputnice,
      tro.stopa_placanja AS stopa_placanja, -- 17.12.
      SUM (
        ROUND (
          (
          ROUND (tro.cijena_usluge * tro.stopa_placanja / 100 * tro.izn_tecaja / rac_row_m.izn_tecaja, 2)
            * tro.kol_usluge
            * DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.dat_poc - tro.obr_zdana, 1)
            - tro.izn_odbitka
          )
          * 100 / (100 + tro.stopa_pdv), 2)
        ) AS osnovica,
      SUM (
        ROUND (tro.cijena_usluge * tro.stopa_placanja / 100 * tro.izn_tecaja / rac_row_m.izn_tecaja, 2)
          * tro.kol_usluge
          * DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.dat_poc - tro.obr_zdana, 1)
          - tro.izn_odbitka
        ) AS iznos, -- 17.12.
      tro.stopa_pdv AS stopa_pdv,
      tro.vpispz_id AS vpispz_id,
      zmpspisp.sifra AS sk_sifra,
      usl.sifra AS usl_sifra,
      usl1.sifra AS usl1_sifra,
      usl.naziv AS usl_naziv,
      usl.ozn_cje_vsj AS usl_ozn_cje_vsj,
      vsj.naziv AS vsj_naziv,
      usl.ozn_abc AS usl_ozn_abc,
      pra_rec_s5.kpd_sifra (tro.so_id, tro.usl_id) AS kpd_sifra -- 4096
    FROM
      t_troskovi tro,
      s_vpisporukaz vpispz,
      szmp_spisporuka zmpspisp,
      s_usluge usl,
      s_kpd kpd, -- 4096
      s_usluge usl1,
      s_vsj vsj
    WHERE
      tro.rac_id = rac_id_p
      AND vpispz.id = tro.vpispz_id
      AND zmpspisp.id = vpispz.zmpspisp_id
      AND usl.id = tro.usl_id
      AND kpd.id (+) = usl.kpd_id -- 4096
      AND usl1.id (+) = tro.usl_id1
      AND vsj.id (+) = tro.vsj_id
      AND tro.usl_id NOT IN (SELECT usl_id FROM s_vplabp_tz) -- 4096
    GROUP BY
      tro.br_uputnice,
      tro.val_id, tro.cijena_usluge, tro.stopa_placanja, tro.izn_tecaja,
      tro.obr_po_danu,
      tro.obr_zdana,
      tro.dat_poc,
      tro.dat_zav,
      tro.stopa_pdv,
      tro.vpispz_id,
      zmpspisp.sifra,
      usl.sifra,
      usl1.sifra,
      usl.naziv,
      usl.ozn_cje_vsj,
      vsj.sifra,
      vsj.naziv,
      usl.ozn_abc,
      pra_rec_s5.kpd_sifra (tro.so_id, tro.usl_id)
    UNION
    SELECT -- usluge u koje je uključena cijena druge usluge
      2 AS oznaka,
      SUM (tro.kol_usluge * DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.obr_zdana - tro.dat_poc, 1) * (-1)) AS kol_usluge,
      tro.obr_po_danu AS obr_po_danu,
      tro.dat_poc AS dat_poc,
      tro.dat_zav AS dat_zav,
      tro.br_uputnice AS br_uputnice,
      tro.stopa_placanja AS stopa_placanja, -- 17.12.
      SUM (
        ROUND (
          (
          ROUND (tro.cijena_usluge * tro.stopa_placanja / 100 * tro.izn_tecaja / rac_row_m.izn_tecaja, 2)
            * tro.kol_usluge * (-1)
            * DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.dat_poc - tro.obr_zdana, 1)
            - tro.izn_odbitka
          )
          * 100 / (100 + tro.stopa_pdv1), 2)
        ) AS osnovica,
      SUM (
        ROUND (tro.cijena_usluge * tro.stopa_placanja / 100 * tro.izn_tecaja / rac_row_m.izn_tecaja, 2)
          * tro.kol_usluge * (-1)
          * DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.dat_poc - tro.obr_zdana, 1)
          - tro.izn_odbitka
        ) AS iznos, -- 17.12.
      tro.stopa_pdv1 AS stopa_pdv,
      tro.vpispz_id1 AS vpispz_id, -- 18.01.2026.
      zmpspisp.sifra AS sk_sifra,
      usl.sifra AS usl_sifra,
      NULL, -- usl1_sifra
      usl.naziv AS usl_naziv,
      usl.ozn_cje_vsj AS usl_ozn_cje_vsj,
      vsj.naziv AS vsj_naziv,
      usl.ozn_abc AS usl_ozn_abc,
      pra_rec_s5.kpd_sifra (tro.so_id, tro.usl_id1) AS kpd_sifra -- 4096; -- 13.01.2026. usl_id1 umjesto usl_id
    FROM
      t_troskovi tro,
      s_vpisporukaz vpispz,
      szmp_spisporuka zmpspisp,
      s_usluge usl,
      s_kpd kpd, -- 4096
      s_vsj vsj
    WHERE
      tro.rac_id = rac_id_p
      AND vpispz.id = tro.vpispz_id1
      AND zmpspisp.id = vpispz.zmpspisp_id
      AND usl.id = tro.usl_id1
      AND kpd.id (+) = usl.kpd_id -- 4096
      AND vsj.id (+) = tro.vsj_id
      AND tro.usl_id1 NOT IN (SELECT usl_id FROM s_vplabp_tz) -- 4096
    GROUP BY
      tro.br_uputnice,
      tro.val_id, tro.cijena_usluge, tro.stopa_placanja, tro.izn_tecaja,
      tro.obr_po_danu,
      tro.obr_zdana,
      tro.dat_poc,
      tro.dat_zav,
      tro.stopa_pdv1,
      tro.vpispz_id1, -- 18.01.2026.
      zmpspisp.sifra,
      usl.sifra,
      -- NULL AS usl1_sifra,
      usl.naziv,
      usl.ozn_cje_vsj,
      vsj.sifra,
      vsj.naziv,
      usl.ozn_abc,
      pra_rec_s5.kpd_sifra (tro.so_id, tro.usl_id1) -- 13.01.2026. usl_id1 umjesto usl_id
    ORDER BY
      br_uputnice NULLS LAST, usl_ozn_abc, usl_sifra, dat_poc, stopa_placanja DESC, oznaka
    )
  LOOP

    i := i + 1;

    IF tro_c.osnovica * (1 + tro_c.stopa_pdv / 100) = tro_c.iznos THEN
      ruz_l := 0; -- 17.12.
    ELSE
      ruz_l := zaj_izn_ruz (tro_c.vpispz_id);
    END IF;

    -- InvoiceLines_l
    SELECT
      xmlagg (
        xmlconcat (InvoiceLines_l,
          xmlelement ("cac:InvoiceLine",
            xmlelement ("cbc:ID", TO_CHAR(i)),
            xmlelement ("cbc:InvoicedQuantity", xmlattributes ('H87' "unitCode"), tro_c.kol_usluge),
            xmlelement ("cbc:LineExtensionAmount",
              xmlattributes (racval_sifra_m "currencyID"),
              TRIM (TO_CHAR (tro_c.osnovica + ruz_l, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
            ),
            xmlelement ("cac:Item",
              xmlelement ("cbc:Description",
                DECODE (tro_c.usl_ozn_cje_vsj, -1, tro_c.vsj_naziv || ' ') ||
                TO_CHAR (tro_c.dat_poc, 'DD.MM.RRRR') || DECODE (tro_c.obr_po_danu, -1, '-' || TO_CHAR (tro_c.dat_zav, 'DD.MM.RRRR')) ||
                DECODE (tro_c.br_uputnice, NULL, NULL, ' ' || tro_c.br_uputnice)
              ),
              xmlelement ("cbc:Name", tro_c.usl_naziv || DECODE (tro_c.usl1_sifra, NULL, NULL, ' (' || tro_c.usl1_sifra || ')')
              ),
              -- 4096
              xmlelement ("cac:CommodityClassification",
                xmlelement ("cbc:ItemClassificationCode", xmlattributes ('CG' "listID"), tro_c.kpd_sifra)
              ),
              xmlelement ("cac:ClassifiedTaxCategory",
                xmlelement ("cbc:ID", DECODE (SUBSTR (tro_c.sk_sifra, 1, 1), '1', 'E', 'S')),
                xmlelement ("cbc:Name", DECODE (tro_c.stopa_pdv, -- 4096
                  25, 'HR:PDV25',
                  13, 'HR:PDV13',
                   5, 'HR:PDV5',
                   0, DECODE (SUBSTR (tro_c.sk_sifra, 1, 1), '1', 'HR:E', 'HR:Z'))
                ),
                xmlelement ("cbc:Percent", TRIM (TO_CHAR (tro_c.stopa_pdv, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
                DECODE (SUBSTR (tro_c.sk_sifra, 1, 1), '1', -- 4096
                  DECODE (sifra_vatex_m, NULL, -- 12.01.2026.
                    xmlelement ("cbc:TaxExemptionReason", TaxExemptionReason0_m),
                    xmlelement ("cbc:TaxExemptionReasonCode", sifra_vatex_m)),
                  NULL),
                xmlelement ("cac:TaxScheme",
                  xmlelement ("cbc:ID", 'VAT')
                )
              )
            ),
            xmlelement ("cac:Price",
              xmlelement ("cbc:PriceAmount",
                xmlattributes (racval_sifra_m "currencyID"),
                TRIM (TO_CHAR (ROUND ((tro_c.osnovica + ruz_l) / tro_c.kol_usluge, 2), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
              xmlelement ("cbc:BaseQuantity",
                xmlattributes ('H87' "unitCode"), '1.000')
            )
          )
        )
      )
    INTO InvoiceLines_l
    FROM dual;

    sum_taxable_racval_m := NVL (sum_taxable_racval_m, 0) + tro_c.osnovica + ruz_l;

  END LOOP;
  -- kraj stavke troškovi

  -- stavke internih POS računa
  FOR pkarnkisp_c IN (
    SELECT
      MIN (pkarnkisp.naziv) AS naziv,
      kpd.sifra AS kpd_sifra, -- 4096
      SUM (pkarnkisp.kolicina) AS kolicina, -- 4096
      SUM (
        ROUND ((
          (pkarnkisp.kolicina * pkarnkisp.cijena)
            * (1 - pkarnkisp.stopa_popusta / 100) -- 22.12.
            - pkarnkisp.izn_pdv
            - NVL (pkarnkisp.izn_ppot, 0)
            - NVL (pkarnkisp.izn_pnaknade, 0))
            / rac_row_m.izn_tecaja, 2)
        ) AS osnovica, -- bez poreza u valuti računa
      SUM (
        ROUND ((
          (pkarnkisp.kolicina * pkarnkisp.cijena)
            * (1 - pkarnkisp.stopa_popusta / 100) -- 22.12.
            - NVL (pkarnkisp.izn_ppot, 0)
            - NVL (pkarnkisp.izn_pnaknade, 0))
            / rac_row_m.izn_tecaja, 2)
        ) AS iznos, -- 17.12. s porezom u valuti računa
      pkarnkisp.stopa_pdv AS stopa_pdv,
      pkarnkisp.vpispz_id AS vpispz_id,
      zmpspisp.sifra AS sk_sifra, -- 4096
      SUM (NVL (pkarnkisp.izn_ppot, 0)) AS izn_ppot,
      SUM (NVL (pkarnkisp.izn_pnaknade, 0)) AS izn_pnaknade
    FROM
      tpka_rnk_isp pkarnkisp,
      s_kpd kpd,
      s_vpisporukaz vpispz,
      szmp_spisporuka zmpspisp,
      tpka_rnkasa pkarnk,
      mgmp_kase gmpkas
    WHERE
      pkarnkisp.pkarnk_id = pkarnk.id
      AND NVL (pkarnkisp.kpd_id, pra_pka_s3.pro_kpd_id (pkarnkisp.sifra)) = kpd.id(+)
      AND pkarnkisp.vpispz_id = vpispz.id
      AND vpispz.zmpspisp_id = zmpspisp.id
      AND pkarnk.rac_id = rac_id_p
      AND pkarnk.gmpkas_id = gmpkas.id
    GROUP BY
      pkarnkisp.sifra,
      kpd.sifra,
      pkarnkisp.cijena,
      pkarnkisp.vpispz_id,
      pkarnkisp.stopa_pdv,
      zmpspisp.sifra
    ORDER BY
      pkarnkisp.sifra -- 10.12.2025
    )
  LOOP

    i := i + 1;

    IF pkarnkisp_c.osnovica * (1 + pkarnkisp_c.stopa_pdv / 100) = pkarnkisp_c.iznos THEN
      ruz_l := 0; -- 17.12.
    ELSE
      ruz_l := zaj_izn_ruz (pkarnkisp_c.vpispz_id);
    END IF;

    -- InvoiceLines_l
    SELECT
      xmlagg (
        xmlconcat (InvoiceLines_l,
          xmlelement ("cac:InvoiceLine",
            xmlelement ("cbc:ID", TO_CHAR (i)),
            xmlelement ("cbc:InvoicedQuantity",
              -- 4096; xmlattributes ('H87' "unitCode"), '1'), -- uvijek 1
              xmlattributes ('H87' "unitCode"),
                TRIM (TO_CHAR (pkarnkisp_c.kolicina, '9999999999999990D999', 'NLS_NUMERIC_CHARACTERS=''.,''')) -- 3 decimalna mjesta
            ),
            xmlelement ("cbc:LineExtensionAmount",
              xmlattributes (racval_sifra_m "currencyID"),
                TRIM (TO_CHAR (pkarnkisp_c.osnovica + ruz_l, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
            ),
            xmlelement ("cac:Item",
              xmlelement ("cbc:Name", pkarnkisp_c.naziv
              ),
              -- 4096
              xmlelement ("cac:CommodityClassification",
                xmlelement ("cbc:ItemClassificationCode", xmlattributes ('CG' "listID"), pkarnkisp_c.kpd_sifra)
              ),
              xmlelement ("cac:ClassifiedTaxCategory",
                xmlelement ("cbc:ID", 'S'),
                xmlelement ("cbc:Name",
                  DECODE (pkarnkisp_c.stopa_pdv, -- 4096
                    25, 'HR:PDV25',
                    13, 'HR:PDV13',
                     5, 'HR:PDV5',
                     0, DECODE (SUBSTR (pkarnkisp_c.sk_sifra, 1, 1), '1', 'HR:E', 'HR:Z'))
                ),
                xmlelement ("cbc:Percent",
                  TRIM (TO_CHAR (pkarnkisp_c.stopa_pdv, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
                xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
              )
            ),
            xmlelement ("cac:Price",
              xmlelement ("cbc:PriceAmount",
                xmlattributes (racval_sifra_m "currencyID"),
                  TRIM (TO_CHAR (ABS (ROUND ((pkarnkisp_c.osnovica + ruz_l) / pkarnkisp_c.kolicina, 2)), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
              ),
              xmlelement ("cbc:BaseQuantity", xmlattributes ('H87' "unitCode"), '1.000')
            )
          )
        )
      )
    INTO InvoiceLines_l
    FROM dual;

    sum_taxable_racval_m := NVL (sum_taxable_racval_m, 0) + pkarnkisp_c.osnovica + ruz_l;

  END LOOP;
  -- kraj stavke internih POS računa

  RETURN (InvoiceLines_l);

END;
PROCEDURE PRIPREMI_REC_TAB_RUZ
 (RAC_ID_P IN t_racuni.id%TYPE
 )
 IS
-- puni pomoćnu tablicu s razlikama u zaokruživanju; poziva se iz rec_stavke_xml
  div_l t_racuni.iznos%TYPE;
  mod_l t_racuni.iznos%TYPE;
BEGIN
  DELETE FROM prec_global2;

  FOR raz_c IN (
    SELECT
      racpdv.vpispz_id,
      (racpdv.iznos / rac_row_m.izn_tecaja) - SUM (stavke.osn_racval) razlika,
      SUM (stavke.br_stavaka) br_stavaka
    FROM
      i_rac_pdv racpdv,
      (
      -- usluge
      SELECT
        tro.vpispz_id,
        SUM (ROUND (
                 (
                   ROUND (tro.cijena_usluge * tro.stopa_placanja / 100 * tro.izn_tecaja / rac_row_m.izn_tecaja, 2)
                   * tro.kol_usluge
                   * DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.dat_poc - tro.obr_zdana, 1)
                   - tro.izn_odbitka
                 )
                 * 100 / (100 + tro.stopa_pdv),
               2)
          ) AS osn_racval,
        -- COUNT (tro.id) br_stavaka -- komentirano 09.01.2026.
        -- 09.01.2026. br_stavaka računati kao u rec_stavke_xml
        COUNT (
          DISTINCT (
            NVL (tro.br_uputnice, '¤') || '|' ||
            tro.val_id || '|' ||
            tro.cijena_usluge || '|' ||
            tro.stopa_placanja || '|' ||
            tro.izn_tecaja || '|' ||
            tro.dat_poc || '|' ||
            tro.dat_zav || '|' ||
            tro.usl_id || '|' ||
            NVL (tro.vsj_id, 0)
            )
          ) AS br_stavaka
        -- KRAJ 09.01.2026. br_stavaka računati kao u rec_stavke_xml
      FROM
        t_troskovi tro
      WHERE
        tro.rac_id = rac_id_p
        -- 09.01.2026. u br_stavaka ne trebaju ulaziti stavke s iznosom = 0
        AND tro.cijena_usluge <> 0
        AND tro.stopa_placanja <> 0
        AND tro.kol_usluge <> 0
        AND DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.dat_poc - tro.obr_zdana, 1) <> 0
        -- KRAJ 09.01.2026. u br_stavaka ne trebaju ulaziti stavke s iznosom = 0
      GROUP BY
        tro.vpispz_id
      UNION ALL
      -- usluge čije je cijena uključena u cijenu druge usluge
      SELECT
        tro.vpispz_id1,
        SUM (ROUND (
                 (
                   ROUND (tro.cijena_usluge * tro.stopa_placanja / 100 * tro.izn_tecaja / rac_row_m.izn_tecaja, 2)
                   * tro.kol_usluge * (-1)
                   * DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.dat_poc - tro.obr_zdana, 1)
                   - tro.izn_odbitka
                 )
                 * 100 / (100 + tro.stopa_pdv1),
               2)
          ) osn_racval,
        -- COUNT (tro.id) br_stavaka -- komentirano 09.01.2026.
        -- 09.01.2026. br_stavaka računati kao u rec_stavke_xml
        COUNT (
          DISTINCT (
            NVL (tro.br_uputnice, '¤') || '|' ||
            tro.val_id || '|' ||
            tro.cijena_usluge || '|' ||
            tro.stopa_placanja || '|' ||
            tro.izn_tecaja || '|' ||
            tro.dat_poc || '|' ||
            tro.dat_zav || '|' ||
            tro.usl_id1 || '|' ||
            NVL (tro.vsj_id, 0)
            )
          ) AS br_stavaka
        -- KRAJ 09.01.2026. br_stavaka računati kao u rec_stavke_xml
      FROM
        t_troskovi tro
      WHERE
        tro.rac_id = rac_id_p
        -- 09.01.2026. u br_stavaka ne trebaju ulaziti stavke s iznosom = 0
        AND tro.cijena_usluge <> 0
        AND tro.stopa_placanja <> 0
        AND tro.kol_usluge <> 0
        AND DECODE (tro.obr_po_danu, -1, tro.dat_zav - tro.dat_poc - tro.obr_zdana, 1) <> 0
        -- KRAJ 09.01.2026. u br_stavaka ne trebaju ulaziti stavke s iznosom = 0
      GROUP BY
        tro.vpispz_id1
      UNION ALL
      -- interni POS računi
      SELECT
        rnkpdv.vpispz_id,
        SUM (ROUND (rnkpdv.izn_osnovice / rac_row_m.izn_tecaja, 2)),
        COUNT (rnkpdv.id) br_stavaka
      FROM
        ipka_rnk_pdv rnkpdv,
        tpka_rnkasa pkarnk
      WHERE
        pkarnk.rac_id = rac_id_p
        AND rnkpdv.pkarnk_id = pkarnk.id
      GROUP BY
        rnkpdv.vpispz_id,
        pkarnk.id
      ) stavke
    WHERE
      racpdv.rac_id = rac_id_p
      AND stavke.vpispz_id = racpdv.vpispz_id
    GROUP BY
      racpdv.vpispz_id, racpdv.iznos
    )
  LOOP
    /*
    ako je jedinična mjera za raspoređivanje 0.01 (1 lipa)
    i ako je razlika u zaokruživanju -0.343521
    i broj stavaka za raspoređivanje je 21,
    onda svakoj stavci dodajemo 1 jedinicu = FLOOR ((ABS (-0.34) / 0.01 / 21)) * SIGN (-0.34)
    i još 13 stavaka dobiva po jednu jedinicu = FLOOR (MOD (ABS (-0.34) / 0.01, 21)) * SIGN (-0.34)
    */

    -- broj jedinica koje dodajemo svakoj stavci
    div_l := FLOOR (ABS (ROUND (raz_c.razlika, 2)) / jm_rasporedi_c / raz_c.br_stavaka);

    -- broj stavaka kojima dodajemo jednu jedinicu
    mod_l := FLOOR (MOD (ABS (ROUND (raz_c.razlika, 2)) / jm_rasporedi_c, raz_c.br_stavaka));

    INSERT INTO prec_global2
      (id1, iznos1, iznos2, iznos3, iznos4, iznos5)
      VALUES
      (raz_c.vpispz_id, div_l, mod_l, SIGN (raz_c.razlika), raz_c.razlika, raz_c.br_stavaka);
  END LOOP;
END;
FUNCTION PKA_INVOICE_XML
 (PKARAC_ID_P IN tpka_racuni.id%TYPE
 ,ZAH_PONUDA_P IN VARCHAR2 := NULL
 ,NARUDZBENICA_P IN VARCHAR2 := NULL
 ,UGOVOR_P IN VARCHAR2 := NULL
 ,PROJEKT_P IN VARCHAR2 := NULL
 ,NAPOMENA_P IN VARCHAR2 := NULL
 ,TIP_P IN VARCHAR2
 )
 RETURN XMLTYPE
 IS
-- poziva se iz izradi_eracun_blob
  OrderReference_l               XMLTYPE;
  OriginatorDocumentReference_l  XMLTYPE;
  ContractDocumentReference_l    XMLTYPE;
  ProjectReference_l             XMLTYPE;
  BillingReference_l             XMLTYPE;
  AccountingSupplierParty_l      XMLTYPE;
  AccountingCustomerParty_l      XMLTYPE;
  PaymentMeans_l                 XMLTYPE;
  InvoiceTaxTotal_l              XMLTYPE;
  LegalMonetaryTotal_l           XMLTYPE;

  Invoice_l                      XMLTYPE;
  InvoiceLines_l                 XMLTYPE;
  TaxSubtotals_l                 XMLTYPE;

  predujam_racval_l              tpka_racuni.iznos%TYPE;

  urac_id_l                      m_uracuni.id%TYPE;
  iban_l                         m_uracuni.iban%TYPE;
BEGIN

  IF pkarac_id_p IS NULL THEN
    RETURN NULL;
  END IF;

  SELECT * INTO pkarac_row_m FROM tpka_racuni WHERE id = pkarac_id_p;

  -- 07.01.2026.
  sifra_vatex_m := NULL;
  IF pkarac_row_m.vpdog_id IS NOT NULL THEN
    SELECT MIN (sifra_vatex) INTO sifra_vatex_m FROM s_vpdogadjaja WHERE id = pkarac_row_m.vpdog_id;
  END IF;

  oib_operatera_m := pra_pka_i3fiskal.oib_test ('45892556322');

  invoice_id_m := NULL;
  racval_sifra_m := NULL;
  vapldok_oznaka_m := NULL;

  SELECT
    xmlelement ("cac:AccountingSupplierParty",
      xmlelement ("cac:Party",
        xmlelement ("cbc:EndpointID", xmlattributes ('9934' "schemeID"), vla.oib),
        xmlelement ("cac:PartyIdentification",
          xmlelement ("cbc:ID", '9934:' || vla.oib)
        ),
        xmlelement ("cac:PartyName",
          xmlelement ("cbc:Name", oj.naziv) -- 18.01.2026.
        ),
        xmlelement ("cac:PostalAddress",
          xmlelement ("cbc:StreetName", zmpojr.naz_ulice),
          DECODE (zmpojr.br_kucni, NULL, NULL, xmlelement ("cbc:BuildingNumber", zmpojr.br_kucni)), -- 4096
          xmlelement ("cbc:CityName", pos.naziv),
          xmlelement ("cbc:PostalZone", pos.broj),
          xmlelement ("cac:Country",
            xmlelement ("cbc:IdentificationCode", domzem_sifra_m)
          )
        ),
        xmlelement ("cac:PartyTaxScheme",
          xmlelement ("cbc:CompanyID", domzem_sifra_m || vla.oib),
          xmlelement ("cac:TaxScheme",
            xmlelement ("cbc:ID", 'VAT')
          )
        ),
        xmlelement ("cac:PartyLegalEntity",
          xmlelement ("cbc:RegistrationName", vla.naziv),
          xmlelement ("cbc:CompanyID", vla.oib),
          DECODE (vla.opis_ztd, NULL, NULL, -- 18.01.2026.
            xmlelement ("cbc:CompanyLegalForm", REPLACE (vla.opis_ztd, CHR(10), ' '))
          )
        )
      ),
      xmlelement ("cac:SellerContact", -- 4096
        (SELECT xmlelement ("cbc:ID", DECODE (TrebaOib, 0, vla.oib, NVL (OibOper, oib_operatera_m))) -- oib operatera
           FROM
             (
             SELECT
               MAX (sifra) AS OibOper,
               MAX (ozn_ima_oib) AS TrebaOib
             FROM
               m_korisnici
             WHERE
               pkarac_row_m.unos_korisnik = naziv
             )
        ),
        xmlelement ("cbc:Name", pkarac_row_m.unos_korisnik) -- oznaka operatera
      )
    ),
    vapldok.urac_id,
    TO_NUMBER (pkarac_row_m.rbr) || '-' || pkarac_row_m.oj_sifra || '-' || pkarac_row_m.vdok_sifra2n, -- 4096
    val.sifra_3a,
    ( -- 4096
    SELECT
      SUM (ROUND (pkaracpred.iznos * pkaracpred.izn_tecaja / pkarac_row_m.izn_tecaja, 2))
    FROM
      tpka_rac_pred pkaracpred
    WHERE
      pkaracpred.pkarac_id = pkarac_row_m.id
    ) AS pkaracpred_iznos,
    vapldok.oznaka -- 4096
  INTO
    AccountingSupplierParty_l,
    urac_id_l,
    invoice_id_m,
    racval_sifra_m,
    predujam_racval_l,
    vapldok_oznaka_m -- 4096
  FROM
    s_vapldok vapldok,
    mzmp_ojr zmpojr,
    m_oj oj,
    m_poste pos,
    s_vlasnik vla,
    s_valute val
  WHERE
    vapldok.id = pkarac_row_m.vapldok_id
    AND zmpojr.id = pkarac_row_m.oj_id
    AND oj.id = pkarac_row_m.oj_id
    AND pos.id = zmpojr.pos_id
    AND val.id = pkarac_row_m.val_id;


  /*
  Ako vrsta apl dokumenata BP nema upisan uplatni račun, onda se kod slanja e-računa za iskorišteni predujam javlja greška
  [BR-50]-A Payment account identifier (BT-84) shall be present if Credit transfer (BG-17) information is provided in the Invoice.
  zato se uplatni račun ovdje čita iz povezanog računa za isporuku
  */
  IF urac_id_l IS NULL -- iz vrste apl dokumenata
    AND pkarac_row_m.urac_id IS NULL
    AND (pkarac_row_m.raci_id IS NOT NULL OR pkarac_row_m.pkaraci_id IS NOT NULL)
  THEN

    SELECT MIN (urac_id)
      INTO urac_id_l
      FROM (
           SELECT urac_id FROM t_racuni WHERE id = pkarac_row_m.raci_id
           UNION
           SELECT urac_id FROM tpka_racuni WHERE id = pkarac_row_m.pkaraci_id
           );

  END IF;

  SELECT MIN (iban) INTO iban_l FROM m_uracuni WHERE id = NVL (pkarac_row_m.urac_id, urac_id_l);
  IF iban_l IS NOT NULL THEN
    SELECT
      xmlelement ("cac:PaymentMeans",
        xmlelement ("cbc:PaymentMeansCode", 30),
        /* Specifikacija, str. 67:
           10 - Gotovina
           30 - Kreditni transfer
           48 - Bankovna kartica
           49 - Izravno terećenje
        */
        DECODE (pkarac_row_m.poziv_na_broj, NULL, NULL, xmlelement ("cbc:PaymentID", 'HR99' || ' ' || pkarac_row_m.poziv_na_broj)),
        xmlelement ("cac:PayeeFinancialAccount",
          xmlelement ("cbc:ID", TRANSLATE (iban_l, 'A' || chr(9) || chr(10) || chr(11) || chr(12) || chr(13) || chr(32), 'A'))
          )
        )
    INTO
      PaymentMeans_l
    FROM
      dual;
  END IF;

  AccountingCustomerParty_l := pka_customer_xml (pkarac_id_p); -- 4096
  -- rekapitulacija poreza i sum_tax_domval_m i sum_neto_domval_m i charge_ppot_m i charge_pnaknada_m
  TaxSubtotals_l := pka_taxes_xml (pkarac_id_p);
  -- stavke računa i charge_tpristojba_m (07.01.2026.) i sum_taxable_racval_m
  InvoiceLines_l := pka_stavke_xml (pkarac_id_p);

  -- vidi ChargeTotalAmount
  -- 07.01.2026. dodan charge_tpristojba_m
  charges_m := ROUND ((NVL (charge_tpristojba_m, 0) + NVL (charge_ppot_m, 0) + NVL (charge_pnaknada_m, 0)) / pkarac_row_m.izn_tecaja, 2);
  -- vidi OutOfScopeOfVATAmount
  charges2_m := ROUND ((NVL (charge_ppot_m, 0) + NVL (charge_pnaknada_m, 0)) / pkarac_row_m.izn_tecaja, 2);

  SELECT
    DECODE (narudzbenica_p, NULL, NULL,
      xmlelement ("cac:OrderReference", xmlelement ("cbc:ID", narudzbenica_p))), -- narudžbenica
    DECODE (zah_ponuda_p, NULL, NULL,
      xmlelement ("cac:OriginatorDocRef", xmlelement ("cbc:ID", zah_ponuda_p))), -- zahtjev za ponudu
    DECODE (ugovor_p, NULL, NULL,
      xmlelement ("cac:ContractDocumentReference", xmlelement ("cbc:ID", ugovor_p))), -- ugovor
    DECODE (projekt_p, NULL, NULL,
      xmlelement ("cac:ProjectReference", xmlelement ("cbc:ID", projekt_p))), -- projekt
    -- AllowanceCharge
    -- 07.01.2026. dodan charge_tpristojba_m
    DECODE (NVL (charge_tpristojba_m, 0), 0, NULL,
      xmlelement ("cac:AllowanceCharge",
        xmlelement ("cbc:ChargeIndicator", 'true'),
        /*
        DECODE (sifra_vatex_m, NULL, -- 07.01.2026.
          NULL,
          xmlelement ("cbc:AllowanceChargeReasonCode", sifra_vatex_m)), -- oslobođeno poreznim događajem
        DECODE (sifra_vatex_m, NULL, -- 07.01.2026.
          xmlelement ("cbc:AllowanceChargeReason", charge_reason_m), -- npr. turistička pristojba
          NULL),
        */
        xmlelement ("cbc:AllowanceChargeReason", charge_reason_m), -- npr. turistička pristojba
        xmlelement ("cbc:Amount", xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (charge_tpristojba_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cac:TaxCategory",
          xmlelement ("cbc:ID", 'E'),
          xmlelement ("cbc:Name", 'HR:E'),
          xmlelement ("cbc:Percent", TRIM (TO_CHAR (0, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          DECODE (sifra_vatex_m, NULL, -- 07.01.2026.
            NULL,
            xmlelement ("cbc:TaxExemptionReasonCode", sifra_vatex_m)),
          DECODE (sifra_vatex_m, NULL, -- 07.01.2026.
            xmlelement ("cbc:TaxExemptionReason", TaxExemptionReason0_m),
            NULL),
          xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
        )
      )),
    DECODE (NVL (charge_ppot_m, 0), 0, NULL,
      xmlelement ("cac:AllowanceCharge",
        xmlelement ("cbc:ChargeIndicator", 'true'),
        xmlelement ("cbc:AllowanceChargeReason", '#HR:PP#'), -- porez na potrošnju
        xmlelement ("cbc:Amount", xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (charge_ppot_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cac:TaxCategory",
          xmlelement ("cbc:ID", 'E'),
          xmlelement ("cbc:Name", 'HR:PP'), -- 4096
          xmlelement ("cbc:Percent",
            TRIM (TO_CHAR (0, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          xmlelement ("cbc:TaxExemptionReason", '#HR:PP#'), -- 4096
          xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
        )
      )),
    DECODE (NVL (charge_pnaknada_m, 0), 0, NULL,
      xmlelement ("cac:AllowanceCharge",
        xmlelement ("cbc:ChargeIndicator", 'true'),
        xmlelement ("cbc:AllowanceChargeReason", '#HR:POVNAK#'), -- povratna naknada
        xmlelement ("cbc:Amount", xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (charge_pnaknada_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cac:TaxCategory",
          xmlelement ("cbc:ID", 'E'),
          xmlelement ("cbc:Name", 'HR:POVNAK'), -- 4096
          xmlelement ("cbc:Percent",
            TRIM (TO_CHAR (0, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          xmlelement ("cbc:TaxExemptionReason", '#HR:POVNAK#'), -- 4096
          xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
        )
      )),
    xmlelement ("cac:TaxTotal",
      xmlelement ("cbc:TaxAmount", xmlattributes (domval_sifra_m "currencyID"),
        TRIM (TO_CHAR (sum_tax_domval_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      TaxSubtotals_l
    ),
    xmlelement ("cac:LegalMonetaryTotal",
      xmlelement ("cbc:LineExtensionAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (sum_taxable_racval_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      xmlelement ("cbc:TaxExclusiveAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (ROUND (sum_neto_domval_m / pkarac_row_m.izn_tecaja, 2), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      xmlelement ("cbc:TaxInclusiveAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (pkarac_row_m.iznos + NVL (predujam_racval_l, 0), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      xmlelement ("cbc:ChargeTotalAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (charges_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      -- 18.01.2026. za BP PrepaidAmount je iznos računa, za BI je iznos predujma
      xmlelement ("cbc:PrepaidAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (DECODE (vapldok_oznaka_m, 'BP', pkarac_row_m.iznos, NVL (predujam_racval_l, 0)), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
      -- 18.01.2026. za BP PayableAmount je 0, a za BI je iznos računa
      xmlelement ("cbc:PayableAmount", xmlattributes (racval_sifra_m "currencyID"),
        TRIM (TO_CHAR (DECODE (vapldok_oznaka_m, 'BP', 0, pkarac_row_m.iznos), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,''')))
    )
  INTO
    OrderReference_l,
    OriginatorDocumentReference_l,
    ContractDocumentReference_l,
    ProjectReference_l,
    AllowanceCharge0_m, -- 7.1.2026. turistička pristojba
    AllowanceCharge1_m, -- porez na potrošnju
    AllowanceCharge2_m, -- povratna naknada
    InvoiceTaxTotal_l,
    LegalMonetaryTotal_l
  FROM
    dual;

  UBLExtensions_m := zaj_prosirenja_xml;
  AdditionalDocumentReference_m := zaj_prilozi_xml ('TPKA_RACUNI', pkarac_id_p, invoice_id_m); -- 4096

  BillingReference_l := pka_billref_xml; -- 4096

  SELECT
    xmlelement ("Invoice",
      xmlattributes (
        'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' "xmlns",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' "xmlns:cac",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' "xmlns:cbc",
        'urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2' "xmlns:cct",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' "xmlns:ext",
        'urn:mfin.gov.hr:schema:xsd:HRExtensionAggregateComponents-1' "xmlns:hrextac", -- 4096
        'urn:oasis:names:specification:ubl:schema:xsd:UnqualifiedDataTypes-2' "xmlns:p3", -- 4096
        'urn:oasis:names:specification:ubl:schema:xsd:SignatureAggregateComponents-2' "xmlns:sac",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonSignatureComponents-2' "xmlns:sig",
        'http://www.w3.org/2001/XMLSchema-instance' "xmlns:xsi", -- 4096
        'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2 ../xsd/ubl/maindoc/UBL-Invoice-2.1.xsd ' "xsi:schemaLocation" -- 4096
      ),
      UBLExtensions_m,
      xmlelement ("cbc:CustomizationID", 'urn:cen.eu:en16931:2017#compliant#urn:mfin.gov.hr:cius-2025:1.0#conformant#urn:mfin.gov.hr:ext-2025:1.0'), -- 4096
      xmlelement ("cbc:ProfileID", DECODE (vapldok_oznaka_m, 'BP', 'P4', 'P1')),
      /* Specifikacija, str. 2: 3.2 Podržani zahtjevi poslovnog procesa
        --
        P1 - Fakturiranje isporuka dobara i usluga preko narudžbi na temelju ugovora
        P2 - Periodicno fakturiranje isporuka na temelju ugovora
        P3 - Fakturiranje isporuka preko nepredvidene narudžbe
        P4 - Placanje predujma (avansno placanje)
        P5 - Placanje na licu mjesta
        P6 - Placanje prije isporuke na temelju narudžbe
        P7 - Racuni s referencom na otpremnicu
        P8 - Racuni s referencom na otpremnicu i primku
        P9 - Odobrenje ili negativno fakturiranje
        P10 - Korektivno fakturiranje
        P11 - Parcijalno i završno fakturiranje
        P12 - Samoizdavanje racuna
      */
      xmlelement ("cbc:ID", invoice_id_m),
      DECODE (CopyIndicator_m, 'true', xmlelement ("cbc:CopyIndicator", CopyIndicator_m), NULL), -- 4096
      xmlelement ("cbc:IssueDate", TO_CHAR (pkarac_row_m.unos_dat, 'rrrr-mm-dd')),
      xmlelement ("cbc:IssueTime", TO_CHAR (pkarac_row_m.unos_dat, 'HH24:MI:SS')), -- 4096
      xmlelement ("cbc:DueDate", TO_CHAR (NVL (pkarac_row_m.dat_dospijeca, pkarac_row_m.datum), 'rrrr-mm-dd')),
      xmlelement ("cbc:InvoiceTypeCode", DECODE (vapldok_oznaka_m, 'BP', '386', '380')),
      /* Specifikacija, str. 23: iz liste UNTDID 1001
        --
        82 - Račun za mjerene usluge
        325 - Predračun
        326 - Parcijalni račun
        380 - Komercijalni račun
        381 - Odobrenje
        383 - Terećenje
        384 - Korektivni račun
        386 - Račun za predujam
        394 - Račun za leasing
      */
      DECODE (pkarac_row_m.napomena, NULL, NULL, xmlelement ("cbc:Note", pkarac_row_m.napomena)),
      xmlelement ("cbc:DocumentCurrencyCode", racval_sifra_m),
      /* Specifikacija, str. 19:
        "
        U racunu ce se koristiti samo jedna valuta, osim za ukupni iznos PDV u racunovodstvenoj valuti (BT-111)
        u skladu s clankom 230 Direktive 2006/112 / EZ o PDV-u.
        Popisi važecih valuta su registrirani kod ISO 4217 Agencije za održavanje "Kodovi za prezentiranje valuta i sredstava".
        "
      */
      DECODE (napomena_p, -- neka vrijednost koju izdavatelj racuna upisuje na zahtjev i za potrebe kupca
        NULL, NULL,
        xmlelement ("cbc:BuyerReference", napomena_p)),
      OrderReference_l,
      BillingReference_l, -- 4096
      OriginatorDocumentReference_l,
      ContractDocumentReference_l,
      AdditionalDocumentReference_m, -- 4096
      ProjectReference_l,
      AccountingSupplierParty_l,
      AccountingCustomerParty_l,
      xmlelement ("cac:Delivery",
        xmlelement ("cbc:ActualDeliveryDate", TO_CHAR (pkarac_row_m.datum, 'RRRR-MM-DD'))),
      PaymentMeans_l,
      AllowanceCharge0_m, -- 7.1.2026. turistička pristojba
      AllowanceCharge1_m, -- porez na potrošnju
      AllowanceCharge2_m, -- povratna naknada
      InvoiceTaxTotal_l,
      LegalMonetaryTotal_l,
      Invoice_l,
      InvoiceLines_l
    )
  INTO
    Invoice_l
  FROM
    dual;

  IF tip_p IN ('1', '2', '5') THEN -- Moj-eRačun, EBA, Pondi
    SELECT xmltype ('<?xml version="1.0" encoding="UTF-8"?>' || Invoice_l.getclobval())
      INTO Invoice_l
      FROM dual;
  ELSIF tip_p = '3' THEN -- FINA
    SELECT xmltype ('<?xml version="1.0" encoding="utf-8" standalone="yes"?>' || Invoice_l.getclobval())
      INTO Invoice_l
      FROM dual;
  END IF;

  RETURN Invoice_l;
END;
FUNCTION PKA_BILLREF_XML
 RETURN XMLTYPE
 IS
-- 4096
  BillingReference_l XMLTYPE;
BEGIN

  /*
  <cac:BillingReference>
    <cac:InvoiceDocumentReference>
      <cbc:ID>1-P1-1</cbc:ID>
      <cbc:IssueDate>2025-05-01</cbc:IssueDate>
      <cbc:DocumentDescription>
        Plaćeno po računu za predujam: (1-P1-1)
        Osnovica PDV 25%: 100.00 EUR
        PDV 25%: 25.00 EUR
        Ukupno predujam: 125.00 EUR
      </cbc:DocumentDescription>
    </cac:InvoiceDocumentReference>
  </cac:BillingReference>
  */

  FOR doc_c IN (
    SELECT
      1 ozn_poretka,
      TO_NUMBER (pkarac.rbr) || '-' || pkarac.oj_sifra || '-' || pkarac.vdok_sifra2n AS id,
      pkarac.unos_dat AS IssueDate,
      'Račun za iskorišteni predujam ' || TRIM (TO_CHAR (pkarac.iznos, '9999999999999990D99')) || ' ' || val.sifra_3a AS Description
    FROM
      tpka_racuni pkarac,
      s_valute val
    WHERE
      pkarac.pkaraci_id = pkarac_row_m.id
      AND pkarac.val_id = val.id
    UNION ALL
    SELECT
      2 ozn_poretka,
      TO_NUMBER (pkarac.rbr) || '-' || pkarac.oj_sifra || '-' || pkarac.vdok_sifra2n AS id,
      pkarac.unos_dat AS IssueDate,
      DECODE (vapldok.oznaka, 'BI', 'Poništeni račun za isporuku ', 'Poništeni račun za primljeni predujam ')
        || TRIM (TO_CHAR (pkarac.iznos, '9999999999999990D99')) || ' ' || val.sifra_3a
        AS Description
    FROM
      tpka_racuni pkarac,
      s_vapldok vapldok,
      s_valute val
    WHERE
      pkarac.id = pkarac_row_m.pkarac_id
      AND pkarac.vapldok_id = vapldok.id
      AND pkarac.val_id = val.id
    UNION ALL -- 18.01.2026. referenca na račun za primljeni predujam u računu za iskorišteni predujam
    SELECT
      3 ozn_poretka,
      TO_NUMBER (pkarac.rbr) || '-' || pkarac.oj_sifra || '-' || pkarac.vdok_sifra2n AS id,
      pkarac.unos_dat AS IssueDate,
      'Račun za primljeni predujam ' || TRIM (TO_CHAR (pkarac.iznos, '9999999999999990D99')) || ' ' || val.sifra_3a AS Description
    FROM
      tpka_racuni pkarac,
      s_valute val
    WHERE
      pkarac.id = pkarac_row_m.pkaracd_id
      AND pkarac.val_id = val.id    
    ORDER BY 1, 2)
  LOOP
    SELECT
      xmlagg (
        xmlconcat (BillingReference_l,
          xmlelement ("cac:BillingReference",
            xmlelement ("cac:InvoiceDocumentReference",
              xmlelement ("cbc:ID", doc_c.id),
              xmlelement ("cbc:IssueDate", TO_CHAR (doc_c.IssueDate, 'RRRR-MM-DD')),
              xmlelement ("cbc:DocumentDescription", doc_c.Description)
            )
          )
        )
      )
    INTO BillingReference_l
    FROM dual;
  END LOOP;

  RETURN BillingReference_l;

END;
FUNCTION PKA_CUSTOMER_XML
 (PKARAC_ID_P IN tpka_racuni.id%TYPE
 )
 RETURN XMLTYPE
 IS
-- Poziva se iz pka_invoice_xml
  AccountingCustomerParty_l XMLTYPE;
BEGIN

  SELECT
    xmlelement ("cac:AccountingCustomerParty",
      xmlelement ("cac:Party",
        DECODE (ozn_identifikatora, 'GLN', -- 4096
          xmlelement ("cbc:EndpointID", xmlattributes ('0088' "schemeID"), gln),
          xmlelement ("cbc:EndpointID", xmlattributes ('9934' "schemeID"), CompanyID)
        ),
        xmlelement ("cac:PartyIdentification",
          DECODE (ozn_identifikatora, 'GLN', -- 4096
            xmlelement ("cbc:ID", xmlattributes ('0088' "schemeID"), gln), -- 4096
            xmlelement ("cbc:ID", '9934:' || CompanyID || DECODE (sifra_pj, NULL, NULL, '::HR99:' || sifra_pj)
            )
          )
        ),
        xmlelement ("cac:PostalAddress", -- 30.11.
          DECODE (naz_ulice, NULL, NULL,
            xmlelement ("cbc:StreetName", naz_ulice)
          ),
          DECODE (pos_naziv, NULL, NULL, -- 30.11.
            xmlelement ("cbc:CityName", pos_naziv)
          ),
          DECODE (pos_broj, NULL, NULL,  -- 30.11.
            xmlelement ("cbc:PostalZone", pos_broj)
          ),
          xmlelement ("cac:Country",
            xmlelement ("cbc:IdentificationCode", zem_sifra_2a)
          )
        ),
        xmlelement ("cac:PartyTaxScheme",
          xmlelement ("cbc:CompanyID", br_porezni),
          xmlelement ("cac:TaxScheme",
            xmlelement ("cbc:ID", 'VAT')
          )
        ),
        xmlelement ("cac:PartyLegalEntity",
          xmlelement ("cbc:RegistrationName", naziv),
          xmlelement ("cbc:CompanyID", CompanyID)
        ),
        DECODE (imep_osobeodg || email, NULL, NULL, -- 30.11.
          xmlelement ("cac:Contact",
            DECODE (imep_osobeodg, NULL, NULL,
              xmlelement ("cbc:Name", imep_osobeodg)
            ),
            DECODE (email, NULL, NULL,
              xmlelement ("cbc:ElectronicMail", email)
            )
          )
        )
      )
    )
  INTO
    AccountingCustomerParty_l
  FROM
    (
    SELECT
      gmpkom2.id AS id,
      DECODE (zem.sifra_2a, domzem_sifra_m, domzem_sifra_m || gmpkom2.oib, gmpkom2.br_porezni) AS br_porezni,
      DECODE (zem.sifra_2a, domzem_sifra_m, gmpkom2.oib, gmpkom2.br_porezni) AS CompanyID,
      gmpkom2.sifra_pj AS sifra_pj,
      gmpkom2.naz_ulice AS naz_ulice,
      pos.naziv AS pos_naziv,
      pos.broj AS pos_broj,
      zem.sifra_2a AS zem_sifra_2a,
      gmpkom2.naziv AS naziv,
      gmpkom2.imep_osobeodg AS imep_osobeodg,
      gmpkom2.email AS email,
      gmpkom2.ozn_identifikatora AS ozn_identifikatora, -- 4096
      gmpkom2.gln AS gln -- 4096
    FROM
      mgmp_komitenti gmpkom2,
      s_zemlje zem,
      m_poste pos
    WHERE
      gmpkom2.id = pkarac_row_m.gmpkom_id
      AND gmpkom2.zem_id = zem.id
      AND gmpkom2.pos_id = pos.id (+)
    );

  RETURN (AccountingCustomerParty_l);

END;
FUNCTION PKA_TAXES_XML
 (PKARAC_ID_P IN tpka_racuni.id%TYPE
 )
 RETURN XMLTYPE
 IS
-- poziva se iz pka_invoice_xml
  TaxSubtotals_l XMLTYPE;
  ozn_e_l NUMBER;
BEGIN

  sum_tax_domval_m := NULL;
  sum_neto_domval_m := NULL;
  charge_ppot_m := NULL;
  charge_pnaknada_m := NULL;
  HRTaxSubtotal_m := NULL;

  TaxExemptionReason0_m := 'Oslobođeno poreza na temelju čl. 33. st. 3. Zakona o PDV-u'; -- 18.01.2026.

  SELECT
    xmlagg (
      xmlelement ("cac:TaxSubtotal",
        xmlelement ("cbc:TaxableAmount",
          xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (osnovica, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cbc:TaxAmount",
          xmlattributes (domval_sifra_m "currencyID"),
          TRIM (TO_CHAR (porez, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cac:TaxCategory",
          xmlelement ("cbc:ID", TaxCategory_ID),
          xmlelement ("cbc:Name", TaxCategory_Name),
          xmlelement ("cbc:Percent",
            TRIM (TO_CHAR (stopa, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          DECODE (TaxCategory_ID, 'E',
            DECODE (sifra_vatex_m, NULL, -- 07.01.2026.
               xmlelement ("cbc:TaxExemptionReason", TaxExemptionReason0_m),
               xmlelement ("cbc:TaxExemptionReasonCode", sifra_vatex_m)),
            NULL),
          xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
        )
      )
    ),
    SUM (porez),
    SUM (osnovica),
    SUM (izn_ppot),
    SUM (izn_pnaknade),
    MAX (ozn_e)
  INTO
    TaxSubtotals_l,
    sum_tax_domval_m,
    sum_neto_domval_m,
    charge_ppot_m,
    charge_pnaknada_m,
    ozn_e_l
  FROM
    (
    SELECT
      TaxCategory_ID,
      TaxCategory_Name,
      stopa,
      SUM (neto) AS osnovica,
      SUM (pdv) AS porez,
      SUM (DECODE (ozn_ppot, 1, neto, 0)) AS izn_ppot,
      SUM (DECODE (ozn_pnaknade, 1, neto, 0)) AS izn_pnaknade,
      DECODE (TaxCategory_ID, 'E', 1, 0) AS ozn_e
    FROM
      (
      SELECT
        DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1', 'E', 'S') AS TaxCategory_ID,
        DECODE (pkaracpdv.stopa_pdv,
          25, 'HR:PDV25',
          13, 'HR:PDV13',
           5, 'HR:PDV5',
           0, DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1', 'HR:E', 'HR:Z')
          ) AS TaxCategory_Name,
        pkaracpdv.stopa_pdv AS stopa,
        ROUND (pkaracpdv.iznos, 2) AS neto,
        pkaracpdv.izn_domval - ROUND (pkaracpdv.iznos, 2) AS pdv,
        0 AS ozn_ppot,
        0 AS ozn_pnaknade
      FROM
        ipka_rac_pdv pkaracpdv,
        s_vpisporukaz vpispz,
        szmp_spisporuka zmpspisp
      WHERE
        pkaracpdv.pkarac_id = pkarac_id_p
        AND pkaracpdv.vpispz_id = vpispz.id
        AND vpispz.zmpspisp_id = zmpspisp.id
      UNION ALL
      SELECT
        'E',                -- TaxCategory_ID
        'HR:E',             -- TaxCategory_Name
        0,                  -- stopa
        SUM (izn_ppot),     -- neto
        0,                  -- pdv
        1,                  -- ozn_ppot
        0                   -- ozn_pnaknade
      FROM
        tpka_rnkasa
      WHERE
        pkarac_id = pkarac_id_p
        AND NVL (stopa_ppot, 0) <> 0
      GROUP BY
        stopa_ppot
      HAVING SUM (izn_ppot) <> 0
      UNION ALL
      SELECT
        'E',                -- TaxCategory_ID
        'HR:E',             -- TaxCategory_Name
        0,                  -- stopa
        SUM (izn_pnaknade), -- neto
        0,                  -- pdv
        0,                  -- ozn_ppot
        1                   -- ozn_pnaknade
      FROM
        tpka_rnkasa
      WHERE
        pkarac_id = pkarac_id_p
        AND NVL (izn_pnaknade, 0) <> 0
      GROUP BY
        pkarac_id
      HAVING SUM (izn_pnaknade) <> 0)
    GROUP BY
      TaxCategory_ID,
      TaxCategory_Name,
      stopa
    ORDER BY
      stopa DESC
    );

  IF ozn_e_l = 0 THEN
    RETURN (TaxSubtotals_l); -- nije potrebno slati HRTaxSubtotal
  END IF;

  -- 4096; HRTaxSubtotal
  SELECT
    xmlagg (
      xmlelement ("hrextac:HRTaxSubtotal",
        xmlelement ("cbc:TaxableAmount",
          xmlattributes (domval_sifra_m "currencyID"), TRIM (TO_CHAR (osnovica, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("cbc:TaxAmount",
          xmlattributes (domval_sifra_m "currencyID"), TRIM (TO_CHAR (porez, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
        xmlelement ("hrextac:HRTaxCategory",
          xmlelement ("cbc:ID", TaxCategory_ID),
          xmlelement ("cbc:Name", TaxCategory_Name),
          xmlelement ("cbc:Percent", TRIM (TO_CHAR (stopa, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
          DECODE (TaxCategory_ID, 'E', -- 07.01.2026.
            DECODE (sifra_vatex_m, NULL, NULL,
              xmlelement ("cbc:TaxExemptionReasonCode", sifra_vatex_m)),
            NULL),
          DECODE (TaxExemptionReason, NULL, NULL, xmlelement ("cbc:TaxExemptionReason", TaxExemptionReason)),
          xmlelement ("hrextac:HRTaxScheme", xmlelement ("cbc:ID", TaxScheme_ID))
        )
      )
    )
  INTO
    HRTaxSubtotal_m
  FROM
    (
    SELECT
      TaxCategory_ID,     -- TaxCategory/ID
      TaxCategory_Name,   -- TaxCategory/Name
      stopa,
      TaxExemptionReason, -- TaxCategory/TaxExemptionReason
      TaxScheme_ID,       -- TaxScheme/ID
      SUM (neto) AS osnovica,
      SUM (pdv) AS porez
    FROM
      (
      SELECT
        DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1', 'E', 'S') AS TaxCategory_ID,
        DECODE (rac_pdv.stopa_pdv,
          25, 'HR:PDV25',
          13, 'HR:PDV13',
           5, 'HR:PDV5',
           0, DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1', 'HR:E', 'HR:Z')
          ) AS TaxCategory_Name,
        rac_pdv.stopa_pdv AS stopa,
        DECODE (SUBSTR (zmpspisp.sifra, 1, 1), '1',
          DECODE (sifra_vatex_m, NULL, TaxExemptionReason0_m, NULL), -- 07.01.2026 turistička pristojba
          NULL
          ) AS TaxExemptionReason,
        'VAT' AS TaxScheme_ID,
        ROUND (rac_pdv.iznos, 2) AS neto,
        rac_pdv.izn_domval - ROUND (rac_pdv.iznos, 2) AS pdv
      FROM
        ipka_rac_pdv rac_pdv,
        s_vpisporukaz vpispz,
        szmp_spisporuka zmpspisp
      WHERE
        rac_pdv.pkarac_id = pkarac_id_p
        AND rac_pdv.vpispz_id = vpispz.id
        AND zmpspisp.id = vpispz.zmpspisp_id
      UNION ALL
      SELECT
        'O',                -- TaxCategory_ID
        'HR:PP',            -- TaxCategory_Name
        0,                  -- stopa
        '#HR:PP#',          -- TaxExemptionReason
        'LOC',              -- TaxScheme_ID
        charge_ppot_m,      -- neto
        0                   -- pdv
      FROM
        dual
      WHERE
        NVL (charge_ppot_m, 0) <> 0
      UNION ALL
      SELECT
        'O',                -- TaxCategory_ID
        'HR:POVNAK',        -- TaxCategory_Name
        0,                  -- stopa
        '#HR:POVNAK#',      -- TaxExemptionReason
        'OTH',              -- TaxScheme_ID
        charge_pnaknada_m,  -- neto
        0                   -- pdv
      FROM
        dual
      WHERE
        NVL (charge_pnaknada_m, 0) <> 0
      )
    GROUP BY
      TaxCategory_ID,
      TaxCategory_Name,
      stopa,
      TaxExemptionReason,
      TaxScheme_ID
    ORDER BY
      stopa DESC
    );

  RETURN (TaxSubtotals_l);

END;
FUNCTION PKA_STAVKE_XML
 (PKARAC_ID_P IN tpka_racuni.id%TYPE
 )
 RETURN XMLTYPE
 IS
-- poziva se iz pka_invoice_xml

  -- 29.12.
  izn_isp_l        tpka_racuni.iznos%TYPE;
  izn_smjesta_l    tpka_racuni.izn_smjesta%TYPE;
  izn_popusta_l    tpka_racuni.izn_popusta%TYPE;
  izn_provizije_l  tpka_racuni.izn_provizije%TYPE;
  -- KRAJ 29.12.
  InvoiceLines_l   XMLTYPE;
  i                NUMBER (5) := 0;
  ruz_l            tpka_racuni.iznos%TYPE := 0;
BEGIN

  sum_taxable_racval_m := NULL;

  -- 29.12.2025.
  -- izračunaj stopu odbitka (po uzoru na pra_tpka_racuni.puni_ipka_rac_pdv)
  -- odbici se odobravaju samo na tpka_rac_isp
  -- 07.01.2026.
  -- stopa_odbitka_l "preimenovana" u stopa_odbitka_m jer se čita i u pripremi_pka_tab_ruz
  SELECT SUM (kolicina * cijena)
    INTO izn_isp_l
    FROM tpka_rac_isp
   WHERE pkarac_id = pkarac_id_p;
  IF NVL (izn_isp_l, 0) = 0 THEN
    stopa_odbitka_m := 0;
  ELSE
    izn_smjesta_l := NVL (pkarac_row_m.izn_smjesta, 0);
    IF pkarac_row_m.stopa_popusta IS NULL THEN
      izn_popusta_l := NVL (pkarac_row_m.izn_popusta, 0);
    ELSE
      izn_popusta_l := (izn_isp_l - izn_smjesta_l) * pkarac_row_m.stopa_popusta / 100;
    END IF;
    IF pkarac_row_m.stopa_provizije IS NULL THEN
      izn_provizije_l := NVL (pkarac_row_m.izn_provizije, 0);
    ELSE
      izn_provizije_l := (izn_isp_l - izn_smjesta_l - izn_popusta_l) * pkarac_row_m.stopa_provizije / 100;
    END IF;
    stopa_odbitka_m := (izn_smjesta_l + izn_popusta_l + izn_provizije_l) / izn_isp_l;
  END IF;
  -- KRAJ 29.12.2025.

  -- 7.1.2026; stavke povezane na PDV stopu 0% prikazati u cac:AllowanceCharge
  charge_tpristojba_m := NULL;
  charge_reason_m := NULL;
  SELECT
    SUM (
      ROUND (pkaracisp.cijena * pkaracisp.kolicina * (1 - stopa_odbitka_m) / pkarac_row_m.izn_tecaja, 2)
      ),
    MIN (pkaracisp.naziv)
  INTO
    charge_tpristojba_m,
    charge_reason_m
  FROM
    tpka_rac_isp pkaracisp,
    s_vpisporukaz vpispz
  WHERE
    pkaracisp.pkarac_id = pkarac_id_p
    AND pkaracisp.vpispz_id = vpispz.id
    AND vpispz.stopa = 0       -- za sada u MISH praksi ne postoji oporezivo po stopi 0%
    AND sifra_vatex_m IS NULL; -- nije oslobođeno poreznim događajem
  pripremi_pka_tab_ruz (pkarac_id_p);

  FOR stavke_c IN (
    SELECT
      pkaracisp.kolicina AS kolicina,
      pkaracisp.naziv AS naziv,
      -- 29.12.2025. dodano množenje s (1 - stopa_odbitka_l)
      -- 07.01.2026. stopa_odbitka_l preimenovana u stopa_odbitka_m
      ROUND (pkaracisp.cijena * pkaracisp.kolicina * (1 - stopa_odbitka_m) * 100 / (100 + vpispz.stopa) / pkarac_row_m.izn_tecaja, 2)
        AS osnovica,
      ROUND (pkaracisp.cijena * pkaracisp.kolicina * (1 - stopa_odbitka_m) / pkarac_row_m.izn_tecaja, 2)
        AS iznos, -- 17.12.2025. iznos stavke s porezom
      vpispz.stopa AS vpispz_stopa,
      vpispz.id AS vpispz_id,
      zmpspisp.sifra AS sk_sifra,
      kpd.sifra AS kpd_sifra -- 4096
    FROM
      tpka_rac_isp pkaracisp,
      s_vpisporukaz vpispz,
      szmp_spisporuka zmpspisp,
      s_kpd kpd
    WHERE
      pkaracisp.pkarac_id = pkarac_id_p
      AND pkaracisp.vpispz_id = vpispz.id
      AND vpispz.zmpspisp_id = zmpspisp.id
      AND pkaracisp.kpd_id = kpd.id(+)
      AND (vpispz.stopa <> 0 OR sifra_vatex_m IS NOT NULL) -- 07.01.2026.
    ORDER BY
      pkaracisp.rbr) -- 17.12.2025.
  LOOP

    i := i + 1;

    IF stavke_c.osnovica * (1 + stavke_c.vpispz_stopa / 100) = stavke_c.iznos THEN
      ruz_l := 0; -- 17.12.
    ELSE
      ruz_l := zaj_izn_ruz (stavke_c.vpispz_id);
    END IF;

    -- InvoiceLines_l
    SELECT
      xmlagg (
        xmlconcat (InvoiceLines_l,
          xmlelement ("cac:InvoiceLine",
            xmlelement ("cbc:ID", TO_CHAR (i)),
            xmlelement ("cbc:InvoicedQuantity", xmlattributes ('H87' "unitCode"), stavke_c.kolicina),
            xmlelement ("cbc:LineExtensionAmount",
              xmlattributes (racval_sifra_m "currencyID"),
                TRIM (TO_CHAR (stavke_c.osnovica + ruz_l, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
            ),
            xmlelement ("cac:Item",
              xmlelement ("cbc:Name", stavke_c.naziv),
              -- 4096
              DECODE (vapldok_oznaka_m, 'BP', NULL,
                xmlelement ("cac:CommodityClassification",
                  xmlelement ("cbc:ItemClassificationCode", xmlattributes ('CG' "listID"), stavke_c.kpd_sifra)
                )
              ),
              xmlelement ("cac:ClassifiedTaxCategory",
                xmlelement ("cbc:ID", DECODE (SUBSTR (stavke_c.sk_sifra, 1, 1), '1', 'E', 'S')),
                xmlelement ("cbc:Name", DECODE (stavke_c.vpispz_stopa, -- 4096
                  25, 'HR:PDV25',
                  13, 'HR:PDV13',
                   5, 'HR:PDV5',
                   0, DECODE (SUBSTR (stavke_c.sk_sifra, 1, 1), '1', 'HR:E', 'HR:Z'))
                ),
                xmlelement ("cbc:Percent",
                  TRIM (TO_CHAR (stavke_c.vpispz_stopa, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
                DECODE (SUBSTR (stavke_c.sk_sifra, 1, 1), '1', -- 07.01.2026.
                  DECODE (sifra_vatex_m, NULL, NULL,
                    xmlelement ("cbc:TaxExemptionReasonCode", sifra_vatex_m)
                    )
                  ),
                xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT')
                )
              )
            ),
            xmlelement ("cac:Price",
              xmlelement ("cbc:PriceAmount",
                xmlattributes (racval_sifra_m "currencyID"),
                  TRIM (TO_CHAR (ROUND ((stavke_c.osnovica + ruz_l) / stavke_c.kolicina, 2), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
              xmlelement ("cbc:BaseQuantity",
                xmlattributes ('H87' "unitCode"), '1.000'
              )
            )
          )
        )
      )
    INTO InvoiceLines_l
    FROM dual;

    sum_taxable_racval_m := NVL (sum_taxable_racval_m, 0) + stavke_c.osnovica + ruz_l;

  END LOOP;
  -- kraj stavke tpka_rac_isp

  IF vapldok_oznaka_m = 'BP' THEN
    -- BP nemaju stavaka internih POS računa niti otpremnica
    GOTO kraj;
  END IF;

  -- stavke internih POS računa
  FOR pkarnkisp_c IN (
    SELECT
      MIN (pkarnkisp.naziv) AS naziv,
      kpd.sifra AS kpd_sifra, -- 4096
      SUM (pkarnkisp.kolicina) AS kolicina, -- 4096
      SUM (
        ROUND ((
          (pkarnkisp.kolicina * pkarnkisp.cijena)
            * (1 - pkarnkisp.stopa_popusta / 100) -- 22.12.
            - pkarnkisp.izn_pdv
            - NVL (pkarnkisp.izn_ppot, 0)
            - NVL (pkarnkisp.izn_pnaknade, 0))
            / pkarac_row_m.izn_tecaja, 2)
        ) AS osnovica, -- bez poreza u valuti računa
      SUM (
        ROUND ((
          (pkarnkisp.kolicina * pkarnkisp.cijena)
            * (1 - pkarnkisp.stopa_popusta / 100) -- 22.12.
            - NVL (pkarnkisp.izn_ppot, 0)
            - NVL (pkarnkisp.izn_pnaknade, 0))
            / pkarac_row_m.izn_tecaja, 2)
        ) AS iznos, -- 17.12. s porezom u valuti računa
      pkarnkisp.stopa_pdv AS stopa_pdv,
      pkarnkisp.vpispz_id AS vpispz_id,
      zmpspisp.sifra AS sk_sifra, -- 4096
      SUM (NVL (pkarnkisp.izn_ppot, 0)) AS izn_ppot,
      SUM (NVL (pkarnkisp.izn_pnaknade, 0)) AS izn_pnaknade
    FROM
      tpka_rnk_isp pkarnkisp,
      s_kpd kpd,
      s_vpisporukaz vpispz,
      szmp_spisporuka zmpspisp,
      tpka_rnkasa pkarnk,
      mgmp_kase gmpkas
    WHERE
      pkarnkisp.pkarnk_id = pkarnk.id
      AND NVL (pkarnkisp.kpd_id, pra_pka_s3.pro_kpd_id (pkarnkisp.sifra)) = kpd.id(+)
      AND pkarnkisp.vpispz_id = vpispz.id
      AND vpispz.zmpspisp_id = zmpspisp.id
      AND pkarnk.pkarac_id = pkarac_id_p
      AND pkarnk.gmpkas_id = gmpkas.id
    GROUP BY
      pkarnkisp.sifra,
      kpd.sifra,
      pkarnkisp.cijena,
      pkarnkisp.vpispz_id,
      pkarnkisp.stopa_pdv,
      zmpspisp.sifra
    ORDER BY
      pkarnkisp.sifra -- 10.12.2025
    )
  LOOP

    i := i + 1;

    IF pkarnkisp_c.osnovica * (1 + pkarnkisp_c.stopa_pdv / 100) = pkarnkisp_c.iznos THEN
      ruz_l := 0; -- 17.12.
    ELSE
      ruz_l := zaj_izn_ruz (pkarnkisp_c.vpispz_id);
    END IF;

    -- InvoiceLines_l
    SELECT
      xmlagg (
        xmlconcat (InvoiceLines_l,
          xmlelement ("cac:InvoiceLine",
            xmlelement ("cbc:ID", TO_CHAR (i)),
            xmlelement ("cbc:InvoicedQuantity",
              -- 4096; xmlattributes ('H87' "unitCode"), '1'), -- uvijek 1
              xmlattributes ('H87' "unitCode"),
                TRIM (TO_CHAR (pkarnkisp_c.kolicina, '9999999999999990D999', 'NLS_NUMERIC_CHARACTERS=''.,''')) -- 3 decimale
            ),
            xmlelement ("cbc:LineExtensionAmount",
              xmlattributes (racval_sifra_m "currencyID"),
                TRIM (TO_CHAR (pkarnkisp_c.osnovica + ruz_l, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
            ),
            xmlelement ("cac:Item",
              xmlelement ("cbc:Name", pkarnkisp_c.naziv
              ),
              -- 4096
              xmlelement ("cac:CommodityClassification",
                xmlelement ("cbc:ItemClassificationCode", xmlattributes ('CG' "listID"), pkarnkisp_c.kpd_sifra)
              ),
              xmlelement ("cac:ClassifiedTaxCategory",
                xmlelement ("cbc:ID", 'S'),
                xmlelement ("cbc:Name",
                  DECODE (pkarnkisp_c.stopa_pdv, -- 4096
                    25, 'HR:PDV25',
                    13, 'HR:PDV13',
                     5, 'HR:PDV5',
                     0, DECODE (SUBSTR (pkarnkisp_c.sk_sifra, 1, 1), '1', 'HR:E', 'HR:Z'))
                ),
                xmlelement ("cbc:Percent",
                  TRIM (TO_CHAR (pkarnkisp_c.stopa_pdv, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
                xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT'))
              )
            ),
            xmlelement ("cac:Price",
              xmlelement ("cbc:PriceAmount",
                xmlattributes (racval_sifra_m "currencyID"),
                  TRIM (TO_CHAR (ABS (ROUND ((pkarnkisp_c.osnovica + ruz_l) / pkarnkisp_c.kolicina, 2)), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
              ),
              xmlelement ("cbc:BaseQuantity", xmlattributes ('H87' "unitCode"), '1.000')
            )
          )
        )
      )
    INTO InvoiceLines_l
    FROM dual;

    sum_taxable_racval_m := NVL (sum_taxable_racval_m, 0) + pkarnkisp_c.osnovica + ruz_l;

  END LOOP;
  -- kraj stavke internih POS računa

  -- stavke otpremnice
  FOR gasizldokart_c IN (
    SELECT
      gasizldokart.kolicina * DECODE (pkarac_row_m.oznaka, 'N', -1, 1) AS kolicina, -- 10.12.2025
      ROUND (gasizldokart.prod_cijena * (1 - gasizldokart.rabat / 100), 2) AS cijena, -- 17.12.
      pro.naziv AS pro_naziv,
      ROUND (
        gasizldokart.prod_cijena
          * gasizldokart.kolicina
          * DECODE (pkarac_row_m.oznaka, 'N', -1, 1) -- 10.12.2025
          * (1 - gasizldokart.rabat / 100),
        2) AS osnovica,
      gasizldokart.pdv AS stopa_pdv, -- 17.12.
      zmpspisp.sifra AS sk_sifra,
      kpd.sifra AS kpd_sifra,
      gasizldokart.vpispz_id AS vpispz_id
    FROM
      tgas_izlazdok_art gasizldokart,
      tpka_rac_gasizldok pkaracgas,
      s_vpisporukaz vpispz,
      szmp_spisporuka zmpspisp,
      m_proizvodi pro,
      s_kpd kpd
    WHERE
      pkaracgas.pkarac_id = pkarac_id_p
      AND gasizldokart.gasizldok_id = pkaracgas.gasizldok_id
      AND gasizldokart.vpispz_id = vpispz.id
      AND vpispz.zmpspisp_id = zmpspisp.id
      AND gasizldokart.pro_id = pro.id
      AND pro.kpd_id = kpd.id(+)
    ORDER BY
      gasizldokart.id -- 17.12.
    )
  LOOP

    i := i + 1;

    IF gasizldokart_c.osnovica * (1 + gasizldokart_c.stopa_pdv / 100)
      = ROUND (gasizldokart_c.osnovica * (1 + gasizldokart_c.stopa_pdv / 100), 2)
    THEN
      ruz_l := 0; -- 17.12.
    ELSE
      ruz_l := zaj_izn_ruz (gasizldokart_c.vpispz_id);
    END IF;

    -- InvoiceLines_l
    SELECT
      xmlagg (
        xmlconcat (InvoiceLines_l,
          xmlelement ("cac:InvoiceLine",
            xmlelement ("cbc:ID", TO_CHAR (i)),
            xmlelement ("cbc:InvoicedQuantity",
              xmlattributes ('H87' "unitCode"),
                TRIM (TO_CHAR (gasizldokart_c.kolicina, '9999999999999990D999', 'NLS_NUMERIC_CHARACTERS=''.,'''))), -- 3 decimale
            xmlelement ("cbc:LineExtensionAmount",
              xmlattributes (racval_sifra_m "currencyID"),
                TRIM (TO_CHAR (gasizldokart_c.osnovica + ruz_l, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
            ),
            xmlelement ("cac:Item",
              xmlelement ("cbc:Name", gasizldokart_c.pro_naziv),
              -- 4096
              xmlelement ("cac:CommodityClassification",
                xmlelement ("cbc:ItemClassificationCode", xmlattributes ('CG' "listID"), gasizldokart_c.kpd_sifra)
              ),
              xmlelement ("cac:ClassifiedTaxCategory",
                xmlelement ("cbc:ID",
                  DECODE (SUBSTR (gasizldokart_c.sk_sifra, 1, 1), '1', 'E', 'S')),
                xmlelement ("cbc:Name",
                  DECODE (gasizldokart_c.stopa_pdv, -- 4096
                    25, 'HR:PDV25',
                    13, 'HR:PDV13',
                     5, 'HR:PDV5',
                     0, DECODE (SUBSTR (gasizldokart_c.sk_sifra, 1, 1), '1', 'HR:E', 'HR:Z'))
                ),
                xmlelement ("cbc:Percent",
                  TRIM (TO_CHAR (gasizldokart_c.stopa_pdv, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
                xmlelement ("cac:TaxScheme", xmlelement ("cbc:ID", 'VAT')
                )
              )
            ),
            xmlelement ("cac:Price",
              xmlelement ("cbc:PriceAmount",
                xmlattributes (racval_sifra_m "currencyID"),
                  TRIM (TO_CHAR (gasizldokart_c.cijena, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
              xmlelement ("cbc:BaseQuantity",
                xmlattributes('H87' "unitCode"), '1.000'
              )
            )
          )
        )
      )
    INTO InvoiceLines_l
    FROM dual;

    sum_taxable_racval_m := NVL (sum_taxable_racval_m, 0) + gasizldokart_c.osnovica + ruz_l;

  END LOOP;
  -- kraj stavke otpremnice

  <<kraj>> NULL;

  RETURN (InvoiceLines_l);

END;
PROCEDURE PRIPREMI_PKA_TAB_RUZ
 (PKARAC_ID_P IN tpka_racuni.id%TYPE
 )
 IS
-- puni pomoćnu tablicu s razlikama u zaokruživanju; poziva se iz pka_stavke_xml
  div_l t_racuni.iznos%TYPE;
  mod_l t_racuni.iznos%TYPE;
BEGIN
  DELETE FROM prec_global2;

  FOR raz_c IN (
    SELECT
      pkaracpdv.vpispz_id,
      (pkaracpdv.iznos / pkarac_row_m.izn_tecaja) - SUM (stavke.osn_racval) razlika,
      SUM (stavke.br_stavaka) br_stavaka
    FROM
      ipka_rac_pdv pkaracpdv,
      (
      -- isporuke na računu
      SELECT
        pkaracisp.vpispz_id,
        -- 07.01.2026 dodano množenje s (1 - stopa_odbitka_m)
        SUM (ROUND ((pkaracisp.cijena * pkaracisp.kolicina * (1 - stopa_odbitka_m) * 100 / (100 + vpispz.stopa)) / pkarac_row_m.izn_tecaja, 2)) osn_racval,
        COUNT (pkaracisp.id) br_stavaka
      FROM
        tpka_rac_isp pkaracisp,
        s_vpisporukaz vpispz
      WHERE
        pkaracisp.pkarac_id = pkarac_id_p
        AND pkaracisp.vpispz_id = vpispz.id
      GROUP BY
        pkaracisp.vpispz_id
      UNION ALL
      -- interni POS računi
      SELECT
        rnkpdv.vpispz_id,
        SUM (ROUND (rnkpdv.izn_osnovice / pkarac_row_m.izn_tecaja, 2)),
        COUNT (rnkpdv.id) br_stavaka
      FROM
        ipka_rnk_pdv rnkpdv,
        tpka_rnkasa pkarnk
      WHERE
        pkarnk.pkarac_id = pkarac_id_p
        AND rnkpdv.pkarnk_id = pkarnk.id
      GROUP BY
        rnkpdv.vpispz_id,
        pkarnk.id
      UNION ALL
      -- otpremnice
      -- 10.12.2025
      SELECT
        vpispz_id,
        SUM (ROUND (izn_osnovice / pkarac_row_m.izn_tecaja, 2)),
        COUNT (id) br_stavaka
      FROM
        ipka_rac_pdvg
      WHERE
        pkarac_id = pkarac_id_p
      GROUP BY
        vpispz_id
      -- KRAJ 10.12.2025
      ) stavke
    WHERE
      pkaracpdv.pkarac_id = pkarac_id_p
      AND stavke.vpispz_id = pkaracpdv.vpispz_id
    GROUP BY
      pkaracpdv.vpispz_id,
      pkaracpdv.iznos)
  LOOP
    /*
    ako je jedinična mjera za raspoređivanje 0.01 (1 lipa)
    i ako je razlika u zaokruživanju -0.343521
    i broj stavaka za raspoređivanje je 21,
    onda svakoj stavci dodajemo 1 jedinicu = FLOOR ((ABS (-0.34) / 0.01 / 21)) * SIGN (-0.34)
    i još 13 stavaka dobiva po jednu jedinicu = FLOOR (MOD (ABS (-0.34) / 0.01, 21)) * SIGN (-0.34)
    */

    -- broj jedinica koje dodajemo svakoj stavci
    div_l := FLOOR (ABS (ROUND (raz_c.razlika, 2)) / jm_rasporedi_c / raz_c.br_stavaka);

    -- broj stavaka kojima dodajemo jednu jedinicu
    mod_l := FLOOR (MOD (ABS (ROUND (raz_c.razlika, 2)) / jm_rasporedi_c, raz_c.br_stavaka));

    INSERT INTO prec_global2
      (id1, iznos1, iznos2, iznos3, iznos4, iznos5)
      VALUES
      (raz_c.vpispz_id, div_l, mod_l, SIGN (raz_c.razlika), raz_c.razlika, raz_c.br_stavaka);
  END LOOP;
END;
FUNCTION ZAJ_IZN_RUZ
 (VPISPZ_ID_P IN s_vpisporukaz.id%TYPE
 )
 RETURN NUMBER
 IS
-- poziva se iz rec_stavke_xml i pka_stavke_xml
  CURSOR raz_c IS
    SELECT (iznos1 +           -- div
            DECODE (iznos2,    -- mod
                    0, 0,
                    1)) *
           iznos3 *            -- sign
           jm_rasporedi_c
      FROM prec_global2
     WHERE id1 = vpispz_id_p;

  retval_l NUMBER;
BEGIN
  OPEN raz_c;
  FETCH raz_c INTO retval_l;
  IF raz_c%NOTFOUND THEN
    CLOSE raz_c; -- 10.12.2025
    RETURN 0;
  END IF;
  CLOSE raz_c;

  UPDATE prec_global2
     SET iznos2 = iznos2 - 1
   WHERE id1 = vpispz_id_p
     AND iznos2 > 0;

  RETURN retval_l;
END;
FUNCTION ZAJ_PROSIRENJA_XML
 RETURN XMLTYPE
 IS
-- poziva se iz rec_invoice_xml i pka_invoice_xml
  UBLExtensions_l XMLTYPE;
BEGIN

  IF HRTaxSubtotal_m IS NULL THEN
    SELECT
      xmlelement ("ext:UBLExtensions",
        xmlelement ("ext:UBLExtension",
          xmlelement ("ext:ExtensionContent",
            xmlelement ("sig:UBLDocumentSignatures",
              xmlelement ("sac:SignatureInformation"
              )
            )
          )
        )
      )
    INTO UBLExtensions_l
    FROM dual;
  ELSE
    SELECT
      xmlelement ("ext:UBLExtensions",
        xmlelement ("ext:UBLExtension",
          xmlelement ("ext:ExtensionContent",
            xmlelement ("hrextac:HRFISK20Data",
              xmlelement ("hrextac:HRTaxTotal",
                xmlelement ("cbc:TaxAmount",
                  xmlattributes (domval_sifra_m "currencyID"),
                  TRIM (TO_CHAR (sum_tax_domval_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))),
                  HRTaxSubtotal_m
              ),
              xmlelement ("hrextac:HRLegalMonetaryTotal",
                xmlelement ("cbc:TaxExclusiveAmount",
                  xmlattributes (domval_sifra_m "currencyID"),
                  TRIM (TO_CHAR (sum_taxable_racval_m + NVL (charge_tpristojba_m, 0), '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
                ),
                xmlelement ("hrextac:OutOfScopeOfVATAmount",
                  xmlattributes (domval_sifra_m "currencyID"),
                  -- 4096; tpristojba nije OutOfScopeOfVAT
                  TRIM (TO_CHAR (charges2_m, '9999999999999990D99', 'NLS_NUMERIC_CHARACTERS=''.,'''))
                )
              )
            )
          )
        ),
        xmlelement ("ext:UBLExtension",
          xmlelement ("ext:ExtensionContent",
            xmlelement ("sig:UBLDocumentSignatures",
              xmlelement ("sac:SignatureInformation"
              )
            )
          )
        )
      )
    INTO UBLExtensions_l
    FROM dual;
  END IF;

  RETURN UBLExtensions_l;

END;
FUNCTION ZAJ_PRILOZI_XML
 (NAZ_TABLICE_P IN tpka_prilozi.naz_tablice%TYPE
 ,ID_RETKA_P IN tpka_prilozi.id_retka%TYPE
 ,INVOICE_ID_P IN VARCHAR2
 )
 RETURN XMLTYPE
 IS
-- 4096; poziva se iz rec_invoice_xml i pka_invoice_xml
  prilog_clob_l CLOB;
  i NUMBER (5) := 0;

  AdditionalDocumentReference_l XMLTYPE;
BEGIN

  /*
  <cac:AdditionalDocumentReference>
    <cbc:ID>16-801-13-1</cbc:ID>
    <cac:Attachment>
      <cbc:EmbeddedDocumentBinaryObject mimeCode="application/pdf" filename="16-801-13.pdf">JVB...go=</cbc:EmbeddedDocumentBinaryObject>
    </cac:Attachment>
  </cac:AdditionalDocumentReference>

  NAPOMENA
  Nazivi AdditionalDocumentReference i EmbeddedDocumentBinaryObject su predugi
  za Oracle, pa se ovdje koriste kraći nazivi AdditionalDocRef i EmbeddedDocument
  koji se u proceduri izradi_eracun_blob izmijene u točne.
  */

  FOR pkapri_c IN (
    SELECT
      id,
      prilog,
      naz_priloga,
      SUBSTR (naz_priloga, INSTR (naz_priloga, '.') + 1, LENGTH (naz_priloga) - INSTR (naz_priloga, '.')) ext
    FROM
      tpka_prilozi
    WHERE
      naz_tablice = naz_tablice_p
      AND id_retka = id_retka_p
    ORDER BY id)
  LOOP
    IF DBMS_LOB.getlength (pkapri_c.prilog) > 0
      AND pkapri_c.ext IN ('pdf', 'png', 'jpg', 'jpeg', 'txt', 'csv')
    THEN
      i := i + 1;
      prilog_clob_l := pra_pka2.blob2base64 (pkapri_c.prilog);
      SELECT
        xmlagg (
          xmlconcat (AdditionalDocumentReference_l,
            xmlelement ("cac:AdditionalDocRef",     -- AdditionalDocumentReference
              xmlelement ("cbc:ID", 'a-' || invoice_id_p || '-' || i),
              xmlelement ("cac:Attachment",
                xmlelement ("cbc:EmbeddedDocument", -- EmbeddedDocumentBinaryObject
                  xmlattributes (
                    /*
                    (@mimeCode = 'application/pdf'
                    or @mimeCode = 'image/png'
                    or @mimeCode = 'image/jpeg'
                    or @mimeCode = 'text/csv'
                    or @mimeCode = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
                    or @mimeCode = 'application/vnd.oasis.opendocument.spreadsheet')
                    */
                    DECODE (pkapri_c.ext,
                      'pdf',  'application/pdf',
                      'png',  'image/png',
                      'jpg',  'image/jpeg',
                      'jpeg', 'image/jpeg',
                      'txt',  'text/csv',
                      'csv',  'text/csv'
                      ) "mimeCode",
                    pkapri_c.naz_priloga "filename"),
                  prilog_clob_l
                )
              )
            )
          )
        )
      INTO AdditionalDocumentReference_l
      FROM dual;
    END IF;
  END LOOP;

  RETURN AdditionalDocumentReference_l;

END;
PROCEDURE POSALJI_ERACUN
 (RAC_ID_P IN t_racuni_blob.rac_id%TYPE
 ,PKARAC_ID_P IN t_racuni_blob.pkarac_id%TYPE
 ,FILENAME_P IN VARCHAR2
 ,RACBLOB_ID_P IN t_racuni_blob.id%TYPE
 )
 IS
-- poziva se iz modula pka2070.fmb - Računi za predujam, rec2250.fmb - Računi i pka2050.fmb - Računi

  CURSOR rec_blobs_c IS
    SELECT id, datoteka
      FROM t_racuni_blob
     WHERE rac_id = rac_id_p
       AND NVL (racblob_id_p, id) = id
       AND oznaka = 'xml' -- 4096
     ORDER BY id DESC;

  CURSOR pka_blobs_c IS
    SELECT id, datoteka
      FROM t_racuni_blob
     WHERE pkarac_id = pkarac_id_p
       AND NVL (racblob_id_p, id) = id
       AND oznaka = 'xml' -- 4096
     ORDER BY id DESC;

  datoteka_l BLOB;

  FUNCTION blob_to_xmltype (blob_p IN BLOB) RETURN XMLTYPE IS
    clob_l CLOB;
    varchar_l VARCHAR2 (32767);
    start_l PLS_INTEGER := 1;
    buffer_l PLS_INTEGER := 32767;
  BEGIN
    DBMS_LOB.createtemporary (clob_l, TRUE);

    FOR i IN 1..CEIL (DBMS_LOB.getlength (blob_p) / buffer_l) LOOP
      varchar_l := UTL_RAW.cast_to_varchar2 (DBMS_LOB.substr (blob_p, buffer_l, start_l));
      DBMS_LOB.writeappend (clob_l, LENGTH (varchar_l), varchar_l);
      start_l := start_l + buffer_l;
    END LOOP;

    RETURN XMLTYPE (clob_l);
  END;
BEGIN

  -- REC param 1175: Oznaka sustava za slanje e-računa: 1 = Moj-eRačun, 2 = EBA, 3 = FINA, 5 = Pondi ili nebitno.
  tip_m := pra_zmp_zaj.zmppar_vrijednost ('REC', '1175');
  IF tip_m = 'nebitno' THEN
    RETURN;
  END IF;

  log_filename_m := filename_p; -- 4096
  racblob_id_m := NULL;

  -- REC račun
  OPEN rec_blobs_c;
  FETCH rec_blobs_c INTO racblob_id_m, datoteka_l;
  IF rec_blobs_c%FOUND THEN
    clob_m := blob_to_xmltype (datoteka_l).getclobval();
    IF tip_m = '1' THEN    -- Moj-eRačun
      request2mer ('send');
    ELSIF tip_m = '5' THEN -- Pondi
      request2pondi ('send');
    END IF;
  END IF;
  CLOSE rec_blobs_c;

  -- PKA račun
  OPEN pka_blobs_c;
  FETCH pka_blobs_c INTO racblob_id_m, datoteka_l;
  IF pka_blobs_c%FOUND THEN
    clob_m := blob_to_xmltype (datoteka_l).getclobval();
    IF tip_m = '1' THEN    -- Moj-eRačun
      request2mer ('send');
    ELSIF tip_m = '5' THEN -- Pondi
      request2pondi ('send');
    END IF;
  END IF;
  CLOSE pka_blobs_c;
END;
PROCEDURE REQUEST2MER
 (METHOD_P IN VARCHAR2
 ,FILENAME_P IN VARCHAR2 := NULL
 ,RACBLOB_ID_P IN t_racuni_blob.id%TYPE := NULL
 ,ID_VANJSUS_P IN NUMBER := NULL
 ,INVOICE_ID_P IN VARCHAR2 := NULL
 ,STATUSID_P IN NUMBER := NULL
 ,STARTDATE_P IN DATE := NULL
 ,ENDDATE_P IN DATE := NULL
 )
 IS
-- poziva se iz posalji_eracun za informacijskog posrednika 1 = Moj-eRačun
  CURSOR vla_c IS
    SELECT oib, erac_username, erac_password
      FROM s_vlasnik;
  oib_l      s_vlasnik.oib%TYPE;           -- OIB izdavatelja računa
  username_l s_vlasnik.erac_username%TYPE; -- korisnicko ime Moj-eRačun
  pass_l     s_vlasnik.erac_password%TYPE; -- pass izdavatelja računa u sustavu Moj-eRačun
BEGIN

  OPEN vla_c;
  FETCH vla_c INTO oib_l, username_l, pass_l;
  CLOSE vla_c;

  SELECT MIN (staza)
    INTO url_m
    FROM s_parametri_prijepisa
   WHERE sifra = 'ERACUN_URL';

  IF method_p = 'send' THEN
    url_m := url_m || '/apis/v2/send';

    APEX_JSON.initialize_clob_output;
    APEX_JSON.open_object; -- {
      APEX_JSON.write ('Username', username_l);
      APEX_JSON.write ('Password', pass_l);
      APEX_JSON.write ('CompanyId', oib_l);
      APEX_JSON.write ('SoftwareId', 'MISH-001');
      APEX_JSON.write ('File', clob_m);
    APEX_JSON.close_object; -- }
    data_m := APEX_JSON.get_clob_output;

  ELSIF method_p = 'query' THEN
    url_m := url_m || '/apis/v2/queryOutbox';
    -- query se poziva izravno iz modula, pa ovdje treba napuniti varijable paketa
    log_filename_m := filename_p;
    racblob_id_m := racblob_id_p;
    tip_m := '1';

    APEX_JSON.initialize_clob_output;
    APEX_JSON.open_object; -- {
      APEX_JSON.write ('Username', username_l);
      APEX_JSON.write ('Password', pass_l);
      APEX_JSON.write ('CompanyId', oib_l);
      APEX_JSON.write ('SoftwareId', 'MISH-001');
      APEX_JSON.write ('ElectronicId', id_vanjsus_p);
      APEX_JSON.write ('InvoiceNumber', invoice_id_p);
      APEX_JSON.write ('StatusId', CASE WHEN NVL (statusId_p, 0) = 0 THEN NULL ELSE statusId_p END);
      APEX_JSON.write ('From', CASE WHEN startdate_p IS NULL THEN NULL ELSE TO_CHAR (startdate_p, 'yyyy-mm-dd') || 'T00:00:00' END);
      APEX_JSON.write ('To', CASE WHEN enddate_p IS NULL THEN NULL ELSE TO_CHAR (enddate_p, 'yyyy-mm-dd') || 'T00:00:00' END);
    APEX_JSON.close_object; -- }
    data_m := APEX_JSON.get_clob_output;

  END IF;

  IF data_m IS NULL THEN
    RETURN;
  END IF;

  zaj_request;
  mer_obradi_odgovor (method_p);
END;
PROCEDURE MER_OBRADI_ODGOVOR
 (METHOD_P IN VARCHAR2
 )
 IS
-- poziva se iz request2mer
  values_l      APEX_JSON.t_values;
  id_vanjsus_l  VARCHAR2(20);
  status_l      VARCHAR2(5);
  status_name_l VARCHAR2(120);

  pka_rac_id_l  NUMBER(32);

  path_l        VARCHAR2(128);
  br_zapisa_l   NUMBER;
  text_l        VARCHAR2(10000);
BEGIN
  /*
  [
    {
      "ElectronicId": 488897,
      "DocumentNr": "2016-000002-017-11",
      "DocumentTypeId": 1,
      "DocumentTypeName": "eInvoice",
      "StatusId": 10,
      "StatusName": "Processing",
      "RecipientBusinessNumber": "94114100359",
      "RecipientBusinessUnit": "",
      "RecipientBusinessName": "ISTRA TECH d.o.o.",
      "Created": "2016-07-01T14:37:26.847",
      "Updated": null,
      "Sent": null,
      "Delivered": null
    },
    {
      "ElectronicId": 488907,
      "DocumentNr": "2016-000003-013-11",
      "DocumentTypeId": 1,
      "DocumentTypeName": "eInvoice",
      "StatusId": 10,
      "StatusName": "Processing",
      "RecipientBusinessNumber": "54333789665",
      "RecipientBusinessUnit": "",
      "RecipientBusinessName": "Probni primatelj d.o.o.",
      "Created": "2016-07-14T12:14:14.83",
      "Updated": null,
      "Sent": null,
      "Delivered": null
    }
  ]
  --------------------
  {
    "Username": {
      "Value": "1808",
      "Messages": [
        "Username and password are not valid"
      ]
    }
  }
  --------------------
  {
    "CompanyId": {
      "Value": "35465465468",
      "Messages": [
        "User: \"1808\" is not authorized with company"
      ]
    }
  }
  --------------------
  {
    "SoftwareId": {
      "Value": "eRa-001",
      "Messages": [
        "SoftwareId is invalid."
      ]
    }
  }
  --------------------
  {
    "Apply": {
    "Value": "resend",
    "Messages": [
        "Action resend can be applied only on documents in status 30 (Sent) or 50 (Unsuccessful)."
        ]
    }
  }
  --------------------

  10 In preparation - Document is successfully uploaded, but pending validation and sending
  20 In validation - Document is successfully uploaded, pending recipient company data validation
  30 Sent - Document is successfully uploaded, digitally signed and time stamped. Notification email is sent to customer.
  40 Delivered - Customer accepted and downloaded digital invoice
  45 Canceled - Sender canceled digital invoicing process, as customer didn't accept invoice. Customer can no longer download document.
  50 Unsuccessful - Customer did not download invoice in 5 days ? from this point usual flow is to resend document
  */

  IF TRIM (odgovor_m) IS NULL OR odgovor_m = '[]' THEN
    RETURN;
  END IF;

  APEX_JSON.parse (values_l, odgovor_m);
  -- endpoint /queryOutbox u odgovoru vrati uglate zagrade
  id_vanjsus_l := APEX_JSON.get_varchar2 (p_path => '[%d].ElectronicId', p0 => 1, p_values => values_l);
  status_l := APEX_JSON.get_varchar2 (p_path => '[%d].StatusId', p0 => 1, p_values => values_l);
  status_name_l :=  APEX_JSON.get_varchar2 (p_path => '[%d].StatusName', p0 => 1, p_values => values_l);
  IF id_vanjsus_l IS NULL THEN
    -- endpoint /send u odgovoru ne vrati uglate zagrade
    id_vanjsus_l := APEX_JSON.get_varchar2 (p_path => 'ElectronicId', p_values => values_l);
    status_l := APEX_JSON.get_varchar2 (p_path => 'StatusId', p_values => values_l);
    status_name_l :=  APEX_JSON.get_varchar2 (p_path => 'StatusName', p_values => values_l);
  END IF;

  IF racblob_id_m IS NOT NULL AND id_vanjsus_l IS NOT NULL THEN
    UPDATE t_racuni_blob racblob
       SET id_vanjsus = id_vanjsus_l,
           status = DECODE (status_l,
                           '10', '1',
                           '20', '1',
                           '30', '1',
                           '40', '2',
                           '45', '-1',
                           '50', '4',
                           '1')
     WHERE racblob.id = racblob_id_m;

    COMMIT;

  ELSE
    BEGIN
      path_l := APEX_JSON.get_members (p_path => '.', p_values => values_l)(1) || '.Messages'; -- npr. Username.Messages
      br_zapisa_l := APEX_JSON.get_count (p_path => path_l, p_values => values_l);
      path_l := path_l || '[%d]'; -- npr. Username.Messages[%d]
      FOR i IN 1 .. NVL (br_zapisa_l, -1)
      LOOP
        IF text_l IS NOT NULL THEN
          text_l := text_l || CHR(10);
        END IF;
        text_l := text_l || APEX_JSON.get_varchar2 (p_path => path_l, p_values => values_l, p0 => i);
      END LOOP;
    EXCEPTION
      WHEN OTHERS THEN -- nije uspjelo učitavanje via APEX_JSON
        text_l := SUBSTR (odgovor_m, 1, 240);
    END;

  END IF;

  IF racblob_id_m IS NULL THEN
    RETURN;
  END IF;

  INSERT INTO t_racblob_log (racblob_id, status)
    VALUES (racblob_id_m, SUBSTR ('(IP 1) ' || INITCAP (method_p) || ' status ' || status_l || ': ' || DECODE (text_l, NULL, status_name_l, 'Error - ' || text_l), 1, 240));

  COMMIT;
END;
PROCEDURE REQUEST2PONDI
 (METHOD_P IN VARCHAR2
 ,FILENAME_P IN VARCHAR2 := NULL
 ,RACBLOB_ID_P IN t_racuni_blob.id%TYPE := NULL
 ,ID_VANJSUS_P IN t_racuni_blob.id_vanjsus%TYPE := NULL
 )
 IS
-- poziva se iz posalji_eracun za informacijskog posrednika 5 = Pondi
BEGIN

  SELECT MIN (staza) -- npr. http://it-gf4:9096
    INTO url_m
    FROM s_parametri_prijepisa
   WHERE sifra = 'ERACUN_URL';

  SELECT erac_password INTO erac_password_m FROM s_vlasnik; -- npr. 531...2b0

  IF method_p = 'send' THEN
    url_m := url_m || '/api/v2/document/send';

    APEX_JSON.initialize_clob_output;
    APEX_JSON.open_object; -- {
    APEX_JSON.write ('document', clob_m);
    APEX_JSON.write ('softwareId', 'MISH-v25.2(5)');
    APEX_JSON.close_object; -- }
    data_m := APEX_JSON.get_clob_output;

  ELSIF method_p = 'get' THEN
    url_m := url_m || '/api/v2/document/status/' || id_vanjsus_p;
    -- get se poziva izravno iz modula, pa ovdje treba napuniti varijable paketa
    log_filename_m := filename_p;
    racblob_id_m := racblob_id_p;
    tip_m := '5';

  END IF;

  zaj_request (method_p);
  pondi_obradi_odgovor (method_p);
END;
PROCEDURE PONDI_OBRADI_ODGOVOR
 (METHOD_P IN VARCHAR2
 )
 IS
-- poziva se iz request2pondi
  values_l      APEX_JSON.t_values;
  id_vanjsus_l  t_racuni_blob.id_vanjsus%TYPE;
  status_l      NUMBER;
  status_text_l VARCHAR2(2000);
  text_l        VARCHAR2(4000);
  count_l       NUMBER;
  status_h_l    NUMBER;
BEGIN
  /*
  Response za /send
  {
    "id": 12345,
    "insertedOn": "2025-07-15T12:00:00.123+02:00",
    "message": "Dokument uspješno zaprimljen i digitalno potpisan. Čeka fiskalizaciju ulaznog računa i slanje primatelju."
  }

  ili

  {
  "error": "Greška pri autentifikaciji korisnika",
  "details": "Korisnički račun nije aktiviran"
  }

  ili

  Response za /status
  {
    "id": 181779,
    "insertedOn": "2025-11-18T20:00:39.483",
    "modifiedOn": "2025-11-18T20:02:01",
    "documentId": "21-801-1325",
    "documentType": "Invoice",
    "issuedOn": "2025-11-18T19:52:17",
    "amount": 1000.00,
    "currency": "EUR",
    "supplierPartyName": "ISTRA TECH d.o.o.",
    "supplierPartyVATId": "94114100359",
    "customerPartyName": "Test d.o.o.",
    "customerPartyVATId": "50930104221",
    "status": 3,
    "statusHistory": [
      {
        "createdOn": "2025-11-18T20:00:39.483",
        "status": 1,
        "statusText": "Zaprimljeno u sustav, čeka validaciju"
      },
      {
        "createdOn": "2025-11-18T20:00:39.508",
        "status": 2,
        "statusText": "Validacija završena, čeka slanje"
      },
      {
        "createdOn": "2025-11-18T20:02:01",
        "status": 3,
        "statusText": "Poslano inf. posredniku primatelja",
        "note": ""
      }
    ],
    "eReportingRequests": [
      {
        "type": 1,
        "description": "Evidencija izlaznog računa",
        "createdOn": "2025-11-18T20:00:39.53",
        "completedOn": "2025-11-18T20:00:39.59"
      }
    ]
  }

  Status dokumenta

  1 - Na čekanju (zaprimljeno u ePoslovanje, čeka validaciju)
  2 - Na slanju (dokument je zaprimljen u ePoslovanje, potpisan i čeka slanje prema informatičkom posredniku primatelja)
  3 - Poslan (dokument je uspješno poslan prema informatičkom posredniku primatelja)
  4 - Isporučen (dokument je uspješno isporučen kupcu)
  5 - Odobren (dokument je odobren od strane kupca)
  6 - Odbijen (dokument je odbijen od strane kupca)
  7 - Plaćen u potpunosti (dokument je u potpunosti plaćen)
  8 - Parcijalno plaćen (dokument je djelomično plaćen)
  9 - Neuspješna isporuka (neuspješno slanje dokumenta prema informatičkom posredniku primatelja)

  */

  IF TRIM (odgovor_m) IS NULL OR odgovor_m = '{}' THEN
    RETURN;
  END IF;

  APEX_JSON.parse (values_l, odgovor_m);
  IF method_p = 'send' THEN
    id_vanjsus_l := APEX_JSON.get_varchar2 (p_path => 'id', p_values => values_l);
    status_text_l := APEX_JSON.get_varchar2 (p_path => 'message', p_values => values_l);
  ELSE
    -- method_p = 'get'
    id_vanjsus_l := APEX_JSON.get_varchar2 (p_path => 'id', p_values => values_l);
    status_l := APEX_JSON.get_number (p_path => 'status', p_values => values_l);
    count_l := APEX_JSON.get_count (p_path => 'statusHistory', p_values => values_l);
    FOR i IN REVERSE 1 .. NVL (count_l, 0) LOOP
      status_h_l := APEX_JSON.get_number (p_path => 'statusHistory[%d].status', p0 => i, p_values => values_l);
      IF status_h_l = status_l THEN
        status_text_l := APEX_JSON.get_varchar2 (p_path => 'statusHistory[%d].statusText', p0 => i, p_values => values_l);
      END IF;
      EXIT WHEN status_text_l IS NOT NULL;
    END LOOP;
  END IF;

  IF racblob_id_m IS NOT NULL AND (id_vanjsus_l IS NOT NULL OR status_l IS NOT NULL) THEN
    UPDATE t_racuni_blob
       SET id_vanjsus = NVL (id_vanjsus_l, id_vanjsus),
           status = DECODE (status_l,
                      '1', '1',   -- 1 = Na čekanju          => 1 = Poslan
                      '2', '1',   -- 2 = Na slanju           => 1 = Poslan
                      '3', '1',   -- 3 = Poslan              => 1 = Poslan
                      '4', '2',   -- 4 = Isporučen           => 2 = Primljen
                      '5', '2',   -- 5 = Odobren             => 2 = Primljen
                      '6', '-1',  -- 6 = Odbijen             => -1 = Poništen
                      '7', '3',   -- 7 = Plaćen u potpunosti => 3 = Plaćen
                      '8', '2',   -- 8 = Parcijalno plaćen   => 2 = Primljen
                      '9', '4',   -- 9 = Neuspješna isporuka => 4 = Nije preuzet
                      '1')
     WHERE id = racblob_id_m;

    COMMIT;

    status_l := NVL (status_l, 1); -- 1 = Poslan

    /*
    domena STATUS_ERAC
    0 = Izrađen
    1 = Poslan
    2 = Primljen
    3 = Plaćen
    4 = Nije preuzet
    -1 = Poništen -- Odbijen ???
    */

  ELSE
    text_l := APEX_JSON.get_varchar2 (p_path => 'error', p_values => values_l);
    IF text_l IS NOT NULL THEN
      text_l := text_l || CHR(10);
    END IF;
    text_l := text_l || APEX_JSON.get_varchar2 (p_path => 'details', p_values => values_l);
  END IF;

  IF racblob_id_m IS NULL THEN
    RETURN;
  END IF;

  INSERT INTO t_racblob_log (racblob_id, status)
    VALUES (racblob_id_m, SUBSTR ('(IP 5) ' || INITCAP (method_p) || ' status ' || status_l || ': ' || DECODE (text_l, NULL, status_text_l, 'Error - ' || text_l), 1, 240));

  COMMIT;
END;
PROCEDURE ZAJ_REQUEST
 (METHOD_P IN VARCHAR2 := NULL
 )
 IS
-- poziva se iz request2mer i request2pondi
  req_l  UTL_HTTP.req;
  res_l  UTL_HTTP.resp;
  line_l CLOB;
BEGIN
  odgovor_m := NULL;

  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 || CHR(10), log_filename_m, racblob_id_m, 'ERACUN');
    pra_pka2.clob_to_table (data_m || CHR(10), log_filename_m, racblob_id_m, 'ERACUN');
  END IF;

  UTL_HTTP.set_detailed_excp_support (enable => true);
  UTL_HTTP.set_body_charset (charset => 'UTF-8');
  IF tip_m = '5' AND method_p = 'get' THEN -- Pondi
    req_l := UTL_HTTP.begin_request (url => url_m, method => 'GET', http_version => 'HTTP/1.1');
  ELSE
    req_l := UTL_HTTP.begin_request (url => url_m, method => 'POST', http_version => 'HTTP/1.1');
  END IF;
  UTL_HTTP.set_header (req_l, 'Content-Type', 'application/json; charset=utf-8');
  IF tip_m = '5' THEN -- Pondi
    UTL_HTTP.set_header (req_l, 'Authorization', erac_password_m); -- npr. 5312e2f7c7ffba36edde753fbe5531b64f06a7e48b057e686efed406f48922b0
  END IF;

  IF tip_m = '5' AND method_p = 'get' THEN -- Pondi
    NULL;
  ELSE
    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);
      UTL_HTTP.set_header (req_l, 'Content-Length', req_length_l);

      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;
  END IF;

  res_l := UTL_HTTP.get_response (req_l);
  UTL_HTTP.set_body_charset (res_l, charset => 'UTF-8');

  IF log_filename_m IS NOT NULL THEN
    pra_pka2.clob_to_table (res_l.status_code || ' ' || res_l.reason_phrase || CHR(10), log_filename_m, racblob_id_m, 'ERACUN');
  END IF;

  BEGIN
    LOOP
      UTL_HTTP.read_line (res_l, line_l);
      odgovor_m := odgovor_m || line_l;
    END LOOP;
    UTL_HTTP.end_response (res_l);
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response (res_l);
  END;

  IF log_filename_m IS NOT NULL THEN
    pra_pka2.clob_to_table (odgovor_m || CHR(10), log_filename_m, racblob_id_m, 'ERACUN');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    UTL_HTTP.end_response (res_l);
    IF log_filename_m IS NOT NULL THEN
      pra_pka2.clob_to_table ('[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] ' || SQLERRM || CHR(10), log_filename_m, racblob_id_m, 'ERACUN');
    END IF;
    RAISE;
END;

END PRA_ERACUN2;
/
SHOW ERROR

