<alerte_def xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<row>
  <codalertadef>101</codalertadef>
  <denumire>Stocuri expirate</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' MailTo,
       '?' MessTo,
       '?' TaskTo,
       '?' SMSTo,
       'Art :' || replace(OBJ.DENUMIRE,'''','"')
       ||'; seria:'||OBJ.serie||'; Data achiz:'||OBJ.dataachiz||'; Stoc:'
       ||coalesce(OBJ.restser,OBJ.rest) ||' '||NUM.Den_UM||'; Furn:'||OBJ.den_part REZULTAT
FROM ( SELECT NOBJ.CODARTICOL, NOBJ.CODOBIECT,NOBJ.DENUMIRE,CODNIRSER,NS.DATAACHIZ, TRUNC(coalesce(NSER.TG,NS.TG) - current_date) NRZILEEXP,
             SER.SERIE,coalesce(NP.DEN_PART,'...') DEN_PART,NS.REST,NSER.REST RESTSER
       FROM NARTOBJ NOBJ INNER JOIN NART N ON N.CODARTICOL = NOBJ.CODARTICOL AND N.ARETG = 1
                         INNER JOIN NIRSTOC NS ON NOBJ.CODOBIECT = NS.CODOBIECT
       LEFT JOIN NPART NP ON NP.CODPART = NS.FURN
                         LEFT JOIN NIRSER NSER ON NS.CODNIRSTOC = NSER.CODNIRSTOC
                         LEFT JOIN NSERII SER ON SER.CODSERIE = NSER.CODSERIE
       WHERE NS.REST &gt; 0) OBJ
   INNER JOIN NART NA ON NA.CODARTICOL = OBJ.CODARTICOL
   INNER JOIN NUM NUM ON NUM.CODUM = NA.UM
WHERE OBJ.NRZILEEXP &lt;= 0</sql>
</row>

<row>
  <codalertadef>102</codalertadef>
  <denumire>Stoc sub limita minima</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       '?' MailTo,
       '?' MessTo,
       '?' TaskTo,
       OBJ.DENUMIRE ||' min '|| coalesce(OBJ.STOCMIN,0)||' - stoc '|| coalesce(OBJ.REST,0)||' = '||Round(coalesce(OBJ.STOCMIN,0)-coalesce(OBJ.REST,0),2)||' '||NU.Den_UM REZULTAT
FROM ( SELECT NOBJ.CODARTICOL, NOBJ.CODOBIECT, NOBJ.DENUMIRE, coalesce(NAS.STOCMIN,0) STOCMIN, coalesce(SUM(NS.REST),0) REST
       FROM NARTOBJ NOBJ INNER JOIN NARTOBJSUBUNIT NAS ON NAS.CODOBIECT = NOBJ.CODOBIECT
       LEFT  JOIN NIRSTOC NS  ON NS.CODOBIECT = NOBJ.CODOBIECT
       LEFT JOIN NIR N ON NS.CODNIR = N.CODNIR
       LEFT JOIN NGEST NG ON NG.CODGEST = N.GEST
       WHERE NG.CODSUBUNIT = 2
       AND NAS.SUBUNITATE = NG.CODSUBUNIT
       AND coalesce(NAS.STOCMIN,0) &lt;&gt; 0
       GROUP BY NOBJ.CODARTICOL, NOBJ.CODOBIECT, NOBJ.DENUMIRE, coalesce(NAS.STOCMIN,0)) OBJ
  INNER JOIN NART NA ON NA.CODARTICOL = OBJ.CODARTICOL
  INNER JOIN NUM NU ON NU.CODUM = NA.UM
WHERE OBJ.REST &lt; OBJ.STOCMIN</sql>
</row>

<row>
  <codalertadef>104</codalertadef>
  <denumire>Intrari cu certificat de calitate nesosit in 10 zile</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       '?' MailTo,
       '?' MessTo,
       '?' TaskTo,
       'Art :'|| replace(OBJ.DENUMIRE,'''','"')||'; achiz:'||OBJ.dataachiz||'; Stoc:'||coalesce(OBJ.rest,0)||' ('||coalesce(OBJ.CantNIR,0) ||') '||NUM.Den_UM REZULTAT
FROM ( SELECT NOBJ.CODARTICOL, NOBJ.CODOBIECT,NOBJ.DENUMIRE,TRUNC(current_date - NS.DATAACHIZ) NrZileAchizitie,ns.dataachiz, ns.cantnir, ns.rest
       FROM NART N INNER JOIN NARTOBJ NOBJ ON N.CODARTICOL = NOBJ.CODARTICOL
       INNER JOIN NIRSTOC NS ON NS.CODOBIECT = NOBJ.CODOBIECT
       INNER JOIN NIRSER NSE ON NSE.CODNIRSTOC = NS.CODNIRSTOC
       LEFT  JOIN IOART1 IO1 ON IO1.CODIOART = NS.CODIOART
       WHERE N.MUSAICALITATE = 1
       AND IO1.ANSTART *12 +IO1.LUNASTART = ( SELECT MAX(ANSTART *12 + LUNASTART)
       FROM IOART1
       WHERE CODIOART = IO1.CODIOART AND ANSTART *12 + LUNASTART &lt;= Extract(Year from current_date)*12+Extract(Month from current_date))
       AND coalesce(NSE.CODCERTIF,0) = 0
       AND to_number(PKG_CATEGCONST.GetValConstanta('CMatPrime',to_number(to_char(Extract(Year from current_date),'9999'),'9999'), to_number(to_char(Extract(Month from current_date),'99'),'99')),'999999999999999999') = IO1.CONT ) OBJ
  INNER JOIN NART NA ON NA.CODARTICOL = OBJ.CODARTICOL
  INNER JOIN NUM NUM ON NUM.CODUM = NA.UM
WHERE OBJ.NrZileAchizitie &gt; 10 </sql>
</row>

<row>
  <codalertadef>105</codalertadef>
  <denumire>Stocuri ce expira in 10 zile</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       '?' MailTo,
       '?' MessTo,
       '?' TaskTo,
       'Art :'|| replace(OBJ.DENUMIRE,'''','"') ||'; seria:'||OBJ.serie||'; Data achiz:'||OBJ.dataachiz||'; Stoc:'||coalesce(OBJ.restser,OBJ.rest) ||' '||NUM.Den_UM||'; Furn:'||OBJ.den_part||'; zile ramase: '||OBJ.NRZILEEXP REZULTAT
FROM ( SELECT NOBJ.CODARTICOL, NOBJ.CODOBIECT,NOBJ.DENUMIRE,CODNIRSER,NS.DATAACHIZ, TRUNC(coalesce(NSER.TG,NS.TG) - current_date) NRZILEEXP,
             SER.SERIE,coalesce(NP.DEN_PART,'...') DEN_PART,NS.REST,NSER.REST RESTSER
       FROM NARTOBJ NOBJ INNER JOIN NART    N  ON N.CODARTICOL = NOBJ.CODARTICOL AND N.ARETG = 1
                         INNER JOIN NIRSTOC NS ON NOBJ.CODOBIECT = NS.CODOBIECT
       LEFT JOIN NPART NP ON NP.CODPART = NS.FURN
                         LEFT JOIN NIRSER NSER ON NS.CODNIRSTOC = NSER.CODNIRSTOC
                         LEFT JOIN NSERII SER  ON SER.CODSERIE = NSER.CODSERIE
       WHERE NS.REST &gt; 0) OBJ
   INNER JOIN NART NA ON NA.CODARTICOL = OBJ.CODARTICOL
   INNER JOIN NUM NUM ON NUM.CODUM = NA.UM
WHERE OBJ.NRZILEEXP &lt;= 10</sql>
</row>

<row>
  <codalertadef>106</codalertadef>
  <denumire>Comenzi nefacturate, toate subunitatile</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       '?' MailTo,
       '?' MessTo,
       '?' TaskTo,
       Den_Part||' Comanda: '||Document REZULTAT
FROM ( SELECT p.Den_Part, coalesce(c.prefixdoc,'')||'.'||coalesce(c.Seriadoc,'')||coalesce(to_char(c.nrdoc,'999999999999999999'),'')||'/'||to_char(c.datadoc,'dd.mm.yyyy') as document,
              (SELECT SUM(CASE WHEN C1.RESTACCEPTAT &lt; 0 THEN 0 ELSE C1.RESTACCEPTAT END) FROM COMANDA1 C1 WHERE CODCOMANDA = C.CODCOMANDA) REST
       FROM COMANDA c
            LEFT OUTER JOIN NPART P      ON P.CODPART = C.PARTENER
       WHERE coalesce(C.DISPLIVRARE, 0) = 0
         AND coalesce(C.FARAFACTURA, 0) = 0
         AND coalesce(C.INCHISA,0) = 0
         AND coalesce(C.OPERAT,0)  = 1
         AND C.TIPDOC = 18
         AND C.AN * 12 + C.LUNA &lt;= Extract(Year from current_date)*12+Extract(Month from current_date)) AA
WHERE REST &gt; 0</sql>
</row>

<row>
  <codalertadef>107</codalertadef>
  <denumire>Lista partenerilor fara adresa de email dar cu facturi scadente </denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       '?' MailTo,
       '?'  MessTo,
       '?'  TaskTo,
       DenumirePartener REZULTAT
FROM
    ( select
        distinct np.DEN_PART DenumirePartener
  from
        iesiri ies
        inner join general  g  on g.coddoc=ies.codies and g.tipdoc=ies.tipdoc
        inner join obligpif o  on o.CODGENERAL = g.codgeneral
        left  join nagenda  na on na.CODAGENDA = ies.codlocatie
        inner join npart    np ON np.CODPART = na.CODPART
  WHERE na.eMail IS NULL and coalesce(o.rest,0)&gt;0 and o.SEMNIFICATIE=200) OBJ</sql>
</row>

<row>
  <codalertadef>108</codalertadef>
  <denumire>Scadente cec-uri de incasat</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       '?' MailTo,
       '?' MessTo,
       '?'  TaskTo,
       Detalii REZULTAT
FROM
    (
    select coalesce(bank.denumire,'') || ' ' || coalesce(t1.doc,'') || ' ' || coalesce(to_char(t1.NRDOC,'999999999999999999'),'') || ' din ' || coalesce(to_char(t1.DATADOC,'dd.mm.yyyy'),'') || ' scadent la ' || coalesce(to_char(t1.datadecont,'dd.mm.yyyy'),'') ||' |' Detalii
    from trezor1 t1
        INNER JOIN Trezor    tr   ON tr.codtrezor = t1.codtrezor
        INNER JOIN nbankcont nbc  ON nbc.codbankcont = tr.codbanca
        INNER JOIN nbank     bank ON bank.codbank = nbc.codbank
        INNER join general   g    on g.coddoc = t1.codtrezor1
        INNER JOIN obligpif  op   ON op.codgeneral = g.codgeneral
        WHERE
             g.semnificatie = 600
             AND coalesce( op.rest, 0 ) &gt; 0
             AND TRUNC( t1.datadecont - current_date ) &lt; 10
     ) OBJ</sql>
</row>

<row>
  <codalertadef>109</codalertadef>
  <denumire>Alerta pentru expirare autorizatii parc auto</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       '?' MailTo,
       '?'  MessTo,
       '?'  TaskTo,
       Detalii Rezultat
FROM
    (SELECT
         na.den_articol,npa.seriec,npa.cilindree,npa.nrinreg,naa.denumire,np2.expira
     from
        nparcaut npa
        left  join nparc2           np2 on npa.codnparcaut= np2.cod
        inner join nartobj          nao on nao.codobiect=npa.codart
        inner join nart             na  on na.codarticol=nao.codarticol
        inner join nautorizatiiauto naa on naa.CODAUTORIZATII=np2.DOC
      WHERE to_date(to_char(current_date,'dd.mm.yyyy'),'dd.mm.yyyy') + CAST(coalesce(np2.atentie,0) as integer)&gt;np2.expira
        AND to_date(to_char(current_date,'dd.mm.yyyy'),'dd.mm.yyyy') &lt;= np2.expira
   ) OBJ</sql>
</row>

<row>
  <codalertadef>110</codalertadef>
  <denumire>Alerta pentru scadenta ratelor de leasing (data din graficul de pe contract)</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA
</codintern>
  <sql>SELECT
       '?' SMSTo,
       '?' MailTo,
       '?' MessTo,
       '?'  TaskTo,
       Detalii REZULTAT
FROM
(
SELECT 'Contract nr ' || coalesce(to_char(c.nrdoc,'999999999999999999'),'') || ' din ' || coalesce(to_char(c.datadoc,'dd.mm.yyyy'),'') || ', Partener ' || coalesce(np.den_part,'')
       || ', scadent la ' || coalesce(to_char(c2.data,'dd.mm.yyyy'),'') Detalii
FROM
       Contract c
       INNER JOIN Contract2 c2 ON c2.codcontract = c.codcontract
       INNER JOIN NPart     np ON np.codpart = c.partener
       WHERE TRUNC( c2.data - current_date ) &lt; 10 and TRUNC( c2.data - current_date )&gt;0
) OBJ</sql>
</row>

<row>
  <codalertadef>111</codalertadef>
  <denumire>Alerta pentru scadenta ratelor de leasing (data scadenta din factura generata)</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       '?' MailTo,
       '?' MessTo,
       '?'  TaskTo,
       Detalii REZULTAT
FROM
(
SELECT 'Contract nr ' || coalesce(to_char(c.nrdoc,'999999999999999999'),'') || ' din ' || coalesce(to_char(c.datadoc,'dd.mm.yyyy'),'') || ', Partener ' || coalesce(np.den_part,'')
       || ', Factura nr. ' || coalesce(to_char(i.nrdoc,'999999999999999999'),'') || '/' || coalesce(to_char(i.datadoc,'dd.mm.yyyy'),'') || ', scadenta la ' || coalesce(to_char(op.termen,'dd.mm.yyyy'),'') Detalii
FROM
       Contract c
       INNER JOIN Contract2 c2 ON c2.codcontract = c.codcontract
       INNER JOIN NPart     np ON np.codpart = c.partener
       INNER JOIN Intrari   i  ON i.codintr = c2.coddoc AND i.tipdoc = c2.tipdoc
       INNER JOIN General   g  ON g.coddoc = i.codintr AND g.tipdoc = i.tipdoc
       INNER JOIN Obligpif  op ON op.codgeneral = g.codgeneral
       WHERE
            TRUNC( op.termen - current_date ) &lt; 10 and TRUNC( op.termen - current_date ) &gt;0
) OBJ</sql>
</row>

<row>
  <codalertadef>112</codalertadef>
  <denumire>Scadente depasite clienti - email doar la parteneri</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       eMail MailTo,
       '?'  MessTo,
       '?'  TaskTo,
       replace(DEN_PART,'''','"')||' doc:'||document||' = '||valoare||' '||Den_Moneda||' Scadent la '||coalesce(to_char(DataScadenta,'dd.mm.yyyy'),'')||' Rest = '|| coalesce(to_char(rest,'999999999999999999.999999999'),'')||' '||Den_Moneda REZULTAT
FROM ( select na.eMail email,
              case when coalesce(pe.LOGONNAME, coalesce(pe1.LOGONNAME,0)) = 0 then '?' else (SELECT u.username FROM WINMADMIN.utilizator u WHERE u.codutilizator = coalesce(pe.LOGONNAME, pe1.LOGONNAME)) end Agent,
              np.DEN_PART,nm.Den_Moneda,
              coalesce(ies.prefixdoc,'')||'.'||coalesce(ies.Seriadoc,'')||coalesce(to_char(ies.nrdoc,'999999999999999999'),'')||'/'||coalesce(to_char(ies.datadoc,'dd.mm.yyyy'),'') as document,
              trunc(current_date - o.termen) ZileDepasire,
       o.termen DataScadenta, o.valoare,o.rest
       from iesiri ies
            inner join general  g    on g.coddoc = ies.codies and g.tipdoc = ies.tipdoc
            inner join NMonede  nm   on nm.CodMoneda = ies.MONEDA
            inner join obligpif o    on o.codgeneral = g.codgeneral
            inner join npart    np   on np.codpart = ies.partener
            left  join NAgenda  na   on na.CODAGENDA = ies.CODLOCATIE
            left  join npers    pe   on pe.CodPers = ies.agent
            left  join nagenda  nag  on nag.codagenda = pe.agenda
            left  join npers    pe1  on pe1.CodPers = na.CODAGENT
            left  join nagenda  nag1 on nag1.codagenda = pe1.agenda
       where coalesce(o.rest,0) &gt; 0 and o.SEMNIFICATIE=200) OBJ where coalesce(email,'?')&lt;&gt;'?'</sql>
</row>

<row>
  <codalertadef>113</codalertadef>
  <denumire>Scadente depasite clienti - email doar la agenti</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       eMail MailTo,
       '?'  MessTo,
       '?'  TaskTo,
       replace(DEN_PART,'''','"')||' doc:'||document||' = '||valoare||' '||Den_Moneda||' Scadent la '||coalesce(to_char(DataScadenta,'dd.mm.yyyy'),'')||' Rest = '|| coalesce(to_char(rest,'999999999999999999.999999999'),'')||' '||Den_Moneda REZULTAT
FROM ( select coalesce(nag.email, nag1.email) email,
              case when coalesce(pe.LOGONNAME, coalesce(pe1.LOGONNAME,0)) = 0 then '?' else (SELECT u.username FROM WINMADMIN.utilizator u WHERE u.codutilizator = coalesce(pe.LOGONNAME, pe1.LOGONNAME)) end Agent,
              np.DEN_PART,nm.Den_Moneda,
              coalesce(ies.prefixdoc,'')||'.'||coalesce(ies.Seriadoc,'')||coalesce(to_char(ies.nrdoc,'999999999999999999'),'')||'/'||coalesce(to_char(ies.datadoc,'dd.mm.yyyy'),'') as document,
              trunc(current_date - o.termen) ZileDepasire,
       o.termen DataScadenta, o.valoare,o.rest
       from iesiri ies
            inner join general  g    on g.coddoc = ies.codies and g.tipdoc = ies.tipdoc
            inner join NMonede  nm   on nm.CodMoneda = ies.MONEDA
            inner join obligpif o    on o.codgeneral = g.codgeneral
            inner join npart    np   on np.codpart = ies.partener
            left  join NAgenda  na   on na.CODAGENDA = ies.CODLOCATIE
            left  join npers    pe   on pe.CodPers = ies.agent
            left  join nagenda  nag  on nag.codagenda = pe.agenda
            left  join npers    pe1  on pe1.CodPers = na.CODAGENT
            left  join nagenda  nag1 on nag1.codagenda = pe1.agenda
       where coalesce(o.rest,0) &gt; 0 and o.SEMNIFICATIE=200) OBJ where coalesce(obj.email,'?')&lt;&gt;'?' </sql>
</row>

<row>
  <codalertadef>116</codalertadef>
  <denumire>Facturi iesire neoperate, introduse in luna curenta</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA
</codintern>
  <sql>SELECT
       email MailTo,
       '?' MessTo,
       '?'  TaskTo,
       '?'  SMSTo,
       Detalii REZULTAT,
       partener, document, tipdocument
FROM
    (
    select
          np.den_part ||', '||'doc '||i.prefixdoc||'.'||i.Seriadoc||i.nrdoc||'/'||i.datadoc Detalii,
          np.den_part partener, i.prefixdoc||'.'||i.Seriadoc||i.nrdoc||'/'||i.datadoc as document,
          case when i.tipdoc=19 then 'Factura iesire'
               when i.tipdoc=20 then 'Aviz iesire'
               when i.tipdoc=21 then 'Chitanta / bon'
               when i.tipdoc=26 then 'Factura la aviz clienti' end TipDocument, nag.email email
    from iesiri i
         INNER JOIN npart    np  ON np.codpart = i.partener
         INNER JOIN npers    npe on npe.logonname=i.userid
         LEFT  JOIN  nagenda nag on nag.codagenda = coalesce(Npe.agenda,0)
         WHERE
           coalesce(i.operat,0)=0 and coalesce(i.anulat,0)=0
           and
           to_date(to_char(i.datacreare,'dd.mm.yyyy'),'dd.mm.yyyy') between  to_date(to_char(add_months(current_date,-1),'dd.mm.yyyy'),'dd.mm.yyyy') and to_date(to_char(current_date,'dd.mm.yyyy'),'dd.mm.yyyy')
     ) OBJ</sql>
</row>

<row>
  <codalertadef>114</codalertadef>
  <denumire>Stocuri la moment</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>  SELECT
     '?' SMSTo,
     '?' MailTo,
     '?' MessTo,
     '?' TaskTo,
     '?' Rezultat,
     Codextern,Denumire as Articol,Den_UM as UM, Stoc as Stoc_disponibil
  FROM
    (
      Select S.CodOBIECT, O.Denumire, O.CodExtern, UM.Den_UM, S.Stoc
         from
         (
           SELECT CodOBIECT, sum(REST) STOC
           FROM NIRSTOC
         WHERE rest &lt;&gt; 0 AND NOT codioart IN (SELECT DISTINCT codioart from ioart1 where mg = 5)
   group by CodOBIECT) S
  INNER JOIN NARTOBJ O ON O.CODOBIECT = S.CODOBIECT AND Coalesce(O.CodExtern, '?') &lt;&gt; '?'
  INNER JOIN NART NA ON NA.CODARTICOL=O.CODARTICOL
  INNER JOIN NUM UM ON UM.CODUM=NA.UM
  )OBJ</sql>
</row>

<row>
  <codalertadef>115</codalertadef>
  <denumire>Mesaj inregistrare comanda in baza de date</denumire>
  <codintern>PKG_ALERTE.DO_ALERTA</codintern>
  <sql>SELECT
       '?' SMSTo,
       coalesce(email,'?') MailTo,
       '?' MessTo,
       '?' TaskTo,
       'Comanda dvs a fost inregistrata' REZULTAT
 FROM (
       SELECT  na.email
       FROM COMANDA c
            left join nagenda na on na.codagenda=c.codlocatie
       WHERE c.datacreare between current_timestamp - interval '1' hour and current_timestamp)
   OBJ</sql>
</row>

</alerte_def>
