View Issue Details

IDProjectCategoryView StatusLast Update
0001662Database Comparer utilityGeneralpublic2021-04-13 15:22
Reportershirokov Assigned Tobarry  
PrioritynormalSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
Product Version8.0.0.2359 
Target Version8.0.0.2359Fixed in Version8.0.0.2400 
Summary0001662: DBComparer fails on parsing "with recursive", "merge into query", and "extract (week from...)"
Descriptionextract (week from current_date) - example select:

select count (distinct iif (P.KOOPERACE_TECHNOLOGICKA = '*',V.PKID, null)) TK,
        count (distinct iif (P.KOOPERACE_TECHNOLOGICKA ='', V.PKID, null)) KK
    from ZAK_VYKRESY V
    left outer join ZAK_POSTUPY P on P.ZAKAZKA = V.ZAKAZKAand P.VYROBNI_CISLO = V.VYROBNI_CISLO
    left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA =P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO andVP.CISLO_OPERACE = P.CISLO_OPERACE
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD =V.KOD_SKUPINY
    where KS.SYSTEMOVY_KOD = '' and KS.SYSTEM_VYRABENE_DILY= '' and
      KS.SYSTEM_UPRAVA_DILU = '' andKS.MONTOVATELNA_SKUPINA = '' and
      KS.MATERIAL = '' and P.ZRUSENO = '' and P.NEVYROBNI ='' and P.KONTROLOVANO = '' and P.STORNOVANO = ''
      and extract(week from VP.TERMIN_POTVRZENY) betweenextract(week from current_date) and extract(week fromcurrent_date)+3
      and P.ODVEDENO_VSE = '' and P.KOOPERACE = '*'
      group by extract(week from VP.TERMIN_POTVRZENY)
      order by extract(week from VP.TERMIN_POTVRZENY)

There is still the problem with "with recursive clause"

example select:

   select (select ITEMID fromI2_GET_ID_V(O.ID_VYRABENY_DIL, O.SERIE)),
           O.CISLO_OPERACE, 'Soma',
           coalesce((with recursive PROFESE as (
             select P.PROFESE, 99999 PRIORITA, 0 as KOREN
               from ZAR_POSTUPY P
               where P.VYKRES = O.VYKRES and P.SERIE =O.SERIE and P.CISLO_OPERACE = O.CISLO_OPERACE
             union all
             select A.ALTERNATIVA PROFESE, A.PRIORITA, 1 asKOREN
               from PROFESE PROFESE
               join ZAK_PROFESE_ALTERNATIVY A on A.PROFESE= PROFESE.PROFESE
               where PROFESE.KOREN = 0
               order by A.PRIORITA desc)
           select first 1 P.PROFESE
             from PROFESE P
             left outer join KAP_PROFESE_DENNI_FOND(coalesce(P.PROFESE, ''), :xdatum) DF on 1=1
             left outer join (select KAP.PROFESE,min(KAP.DATUM) NASLEDUJICI_KAPACITA
                                from ZAK_PROFESE_KAPACITYKAP
                                where KAP.DATUM >:xdatum and KAP.VYRAZENI = '' and KAP.DENNI_FOND > 0 group byKAP.PROFESE) KAP on KAP.PROFESE = P.PROFESE
             where not (DF.POCET_PRACOVNIKU >= 0 andDF.DENNI_FOND = 0 and (KAP.NASLEDUJICI_KAPACITA is null orCURRENT_DATE + 14 < KAP.NASLEDUJICI_KAPACITA))
             order by P.PRIORITA desc), O.PROFESE)
      from (select distinct V.ID_VYRABENY_DIL, V.VYKRES,coalesce(P.SERIE, 0) SERIE, coalesce(P.CISLO_OPERACE, 0)CISLO_OPERACE, coalesce(P.PROFESE, '0001') PROFESE
              from ZAR_VYKRESY V
              left outer join ZAR_POSTUPY P on P.VYKRES =V.VYKRES) O

Problems with merge into query

merge into ZAK_MATERIALY M
  using
  (with RUCNI_OZ_TERMIN as (select R.ZAKAZKA,R.MONTAZNI_CISLO, min (R.TERMIN_POZADOVANY) asRUCNI_TERMIN_POZADOVANY
       from ZAK_ZAKAZKY_AKTIVNI Z
       join ZAK_ROZPISKY R on R.ZAKAZKA = Z.ZAKAZKA
       join ZAR_KODY_SKUPIN KS on KS.KOD = R.KOD_SKUPINY
       where R.TERMIN_POZADOVANY is not null andKS.MATERIAL = '*'
       group by 1,2
  ),
  TERM_V_ROZPISKY as (
     select R.ZAKAZKA, R.MONTAZNI_CISLO,
            min (minvalue(coalesce(V.TERMIN_ZAHAJENI,V.TERMIN_LPST), (V.TERMIN_LPST - :xpredstih_nakup_vd_lpst))) asTERMIN_MATERIALU,
            min (V.TERMIN_LPST) as TERMIN_MAT_LPST
       from ZAK_ZAKAZKY_AKTIVNI Z
       join ZAK_ROZPISKY R on R.ZAKAZKA = Z.ZAKAZKA
       left outer join ZAK_STRUKTURA_ROZPISKY_UP(R.ZAKAZKA, R.MONTAZNI_CISLO) SR on 1=1
       left outer join ZAR_KODY_SKUPIN KS on KS.KOD =SR.O_KOD_SKUPINY
       left outer join ZAK_VYKRESY V on V.ZAKAZKA =SR.O_ZAKAZKA and V.VYROBNI_CISLO = SR.O_VYROBNI_CISLO
       where KS.POSTUP = '*' and KS.ZARIZENI <> '*'
       group by 1,2
  ),
  INDIVIDUALNI_DIL as (
    select R.ZAKAZKA, R.MONTAZNI_CISLO,
           min (ZDO.TERMIN_DOKONCENI) asTERMIN_INDIVIDUALNI
      from ZAK_ZAKAZKY_AKTIVNI A
      join ZAK_ROZPISKY R on R.ZAKAZKA = A.ZAKAZKA
      join ZAK_DODAVKY_OBSAH ZDO on ZDO.DODAVKA = R.DODAVKAand ZDO.POLOZKA = R.POLOZKA_DODAVKY
      where ZDO.TYP_POLOZKY in ('I', 'M')
      group by 1,2
  ),
  ET_VS_MAT as (
    select M.PKID, min (T.TERMIN) as VS_TERMIN_ETAPY
      from ZAK_MATERIALY M
      join ZAK_ZAKAZKY Z on Z.ZAKAZKA = M.ZAKAZKA
      left outer join ZAK_MONT_PRIK_POLOZKY MPP onM.ZAKAZKA = MPP.ZAKAZKA and M.VM_CISLO = MPP.MONTAZNI_CISLO andM.CISLO_OPERACE = 0
      left outer join ZAK_MONTAZNI_PRIKAZY MP on MP.ZAKAZKA= MPP.ZAKAZKA and MP.MONTAZNI_PRIKAZ = MPP.MONTAZNI_PRIKAZ
      left outer join ZAK_ROZPISKY R on R.ZAKAZKA =MPP.ZAKAZKA and R.MONTAZNI_CISLO = MPP.MONTAZNI_CISLO
      left outer join ZAK_ROZPISKY RR on R.ZAKAZKA =RR.ZAKAZKA and R.MONTAZNI_CISLO_RODIC = RR.MONTAZNI_CISLO
      left outer join ZAR_KODY_SKUPIN RKS on RKS.KOD =RR.KOD_SKUPINY
      left outer join ZAK_TERMIN_VS (M.ZAKAZKA,coalesce(MP.PODSKUPINA,0),'N') T on 1=1
      where M.CISLO_OPERACE = 0 and
            M.ODEVZDANO = '*' and
            M.ZRUSENO = '' and
            M.MNOZSTVI_VYDANO < M.MNOZSTVI_MATERIALU
            --VO na J zakazkach musim pocitat jinde
            and not (Z.DRUH_ZAKAZKY = 'J' and RKS.POSTUP ='*')
      group by M.PKID

  ),
  TERMIN_O_ROZPISKY as (
    select R.ZAKAZKA, R.MONTAZNI_CISLO,
            min (RR.TERMIN_ZAHAJENI) as TERMIN_NADRAZENE
      from ZAK_ZAKAZKY_AKTIVNI Z
      join ZAK_ROZPISKY R on R.ZAKAZKA = Z.ZAKAZKA
      join ZAK_ROZPISKY RR on R.ZAKAZKA = RR.ZAKAZKA andR.MONTAZNI_CISLO_RODIC = RR.MONTAZNI_CISLO
      group by 1,2
  )
  select M.PKID,M.NAZEV_MATERIALU, T.O_TERMIN_SKUTECNYTERMIN, VR.TERMIN_MAT_LPST,
    cast(case
           when RT.RUCNI_TERMIN_POZADOVANY is not null then'R'
           when VR.TERMIN_MATERIALU is not null then 'VR'
           when ID.TERMIN_INDIVIDUALNI is not null then 'I'
           when EVM.VS_TERMIN_ETAPY is not null then 'E'
           when ZR.TERMIN_NADRAZENE is not null then 'TN'
           else 'XX'
         end as TEXT2) as TERMIN_MATERIALU_DRUH
          from ZAK_ZAKAZKY_AKTIVNI Z
          join ZAK_MATERIALY M on M.ZAKAZKA = Z.ZAKAZKA
          left outer join RUCNI_OZ_TERMIN RT on RT.ZAKAZKA= M.ZAKAZKA and RT.MONTAZNI_CISLO = M.VM_CISLO andRT.RUCNI_TERMIN_POZADOVANY is not null
          left outer join TERM_V_ROZPISKY VR onRT.RUCNI_TERMIN_POZADOVANY is null and (Z.VYROBNI_ZAKAZKA = '*' orZ.JEDNOUCELOVA_ZAKAZKA = '*') and VR.ZAKAZKA = M.ZAKAZKA andVR.MONTAZNI_CISLO = M.VM_CISLO
          left outer join INDIVIDUALNI_DIL ID onRT.RUCNI_TERMIN_POZADOVANY is null and VR.TERMIN_MATERIALU is nulland ID.ZAKAZKA = M.ZAKAZKA and ID.MONTAZNI_CISLO = M.VM_CISLO
          left outer join ET_VS_MAT EVM onRT.RUCNI_TERMIN_POZADOVANY is null and VR.TERMIN_MATERIALU is nulland ID.TERMIN_INDIVIDUALNI is null and EVM.PKID = M.PKID
          left outer join TERMIN_O_ROZPISKY ZR onRT.RUCNI_TERMIN_POZADOVANY is null and VR.TERMIN_MATERIALU is nulland ID.TERMIN_INDIVIDUALNI is null and
                          EVM.VS_TERMIN_ETAPY is null andM.ZAKAZKA = ZR.ZAKAZKA and M.VM_CISLO = ZR.MONTAZNI_CISLO
          --JH20190118 get_termin_skutecny volam dvakrat,-+1den abych mel jistotu, ze termin objednani je na pracovni densoma
          left outer join GET_TERMIN_SKUTECNY ((selectO_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY (coalesce(RT.RUCNI_TERMIN_POZADOVANY, VR.TERMIN_MATERIALU,ID.TERMIN_INDIVIDUALNI, EVM.VS_TERMIN_ETAPY, ZR.TERMIN_NADRAZENE),-1)),1) T on 1=1
          where M.CISLO_OPERACE = 0 and
                M.ODEVZDANO = '*' and
                M.ZRUSENO = '' and
                M.MNOZSTVI_VYDANO < M.MNOZSTVI_MATERIALUand (0 = :i_zakazka or M.ZAKAZKA = :i_zakazka)
    ) RM
  on M.PKID = RM.PKID and ((cast (M.TERMIN_MATERIALU asdate) is distinct from cast (RM.TERMIN as date)) or
                           (cast (M.TERMIN_LPST as date) isdistinct from cast (RM.TERMIN_MAT_LPST as date)) or
                            (M.TERMIN_MATERIALU_DRUH isdistinct from RM.TERMIN_MATERIALU_DRUH)
                          )
  when matched then update set M.TERMIN_MATERIALU = cast(RM.TERMIN as date), M.TERMIN_LPST = RM.TERMIN_MAT_LPST,M.TERMIN_MATERIALU_DRUH = RM.TERMIN_MATERIALU_DRUH;

And so on...
Steps To ReproduceAn example SQL script is attached.
Additional InformationSee also the bugreport no 1648.
TagsNo tags attached.

Relationships

related to 0001648 resolvedbarry Database Comparer Utility fail on syntax analysis of CTE in firebird namely CTE where "WITH recursive" is USED 

Activities

shirokov

2020-12-04 17:15

administrator  

cleverCOmponentsExport.sql (200,358 bytes)   
set term ^ ;
create procedure APS_UKAZATELE_VYPOCET as begin end^

alter procedure APS_UKAZATELE_VYPOCET
as
  declare xpocet_zak                      POCET;
  declare xpocet_z_zak                    POCET;
  declare xpocet_j_zak                    POCET;
  declare xpocet_s_zak                    POCET;
  declare xpocet_o_zak                    POCET;
  
  declare xpocet_auto_ato_vc              POCET;
  declare xpocet_forecast_vc              POCET;
  declare xpocet_zpozdenych_zak           POCET;
  declare xpocet_nesplnitelnych_zak       POCET;
  declare xnormativ_nesplnitelnych_zak    MNOZSTVI;
  declare xcelkove_zpozdeni_zak           MNOZSTVI;
  declare xmaximalni_zpozdeni_zak         MNOZSTVI;
  declare xprumerne_zpozdeni_zak          MNOZSTVI;
  declare xmedian_zpozdeni_zak            MNOZSTVI; 
  
  declare xpocet_predcasnych_zak          UCETNI_DOKLAD; 
  declare xcelkova_predcasnost_zak        MNOZSTVI;
  declare xmaximalni_predcasnost_zak      MNOZSTVI; 
  declare xprumerna_predcasnost_zak       MNOZSTVI;
  declare xmedian_predcasnost_zak         MNOZSTVI;

  declare xpocet_vcasnych_zak             POCET;
  declare xpocet_profesi_s4               POCET;
  --kapacita NH 
  declare xkapacita_neuvolnena_s4          MNOZSTVI;
  declare xkapacita_uvolnena_s4            MNOZSTVI;
  declare xkapacita_zadana_s4              MNOZSTVI;
  --kapacity NH KOOP
  declare xkooperace_neuvolnena_s4         MNOZSTVI;
  declare xkooperace_uvolnena_s4           MNOZSTVI;
  declare xkooperace_zadana_s4             MNOZSTVI;
  declare xkooperace_objednana_s4          MNOZSTVI;
  --K|T kooperace neodvedene s terminem dodani polotovaru
  declare xkoop_t_term_dod_pol             POCET;
  declare xkoop_k_term_dod_pol             POCET;
  declare xkoop_k_term_dod_pol_nh          MNOZSTVI;
  declare xkoop_t_tdp_vcas                 POCET;
  declare xkoop_k_tdp_vcas                 POCET;
  declare xkoop_k_tdp_vcas_nh              MNOZSTVI;
  declare xkoop_t_tdp_pozde                POCET;
  declare xkoop_k_tdp_pozde                POCET;
  declare xkoop_k_tdp_pozde_nh             MNOZSTVI;
  declare xkoop_t_tdp_neex_po_t            POCET;
  declare xkoop_k_tdp_neex_po_t            POCET;
  declare xkoop_k_tdp_neex_po_t_nh         MNOZSTVI;
  
  declare xpocet_pretizenych_profesi_s4 POCET;
  
  declare xsuma_previs_normohodin         MNOZSTVI;
  declare xsuma_nedostatku_normohodin     MNOZSTVI;
  declare xvcerejsi_kapacita_dle_kz       MNOZSTVI;
  declare xvcerejsi_skutecna_dochazka     MNOZSTVI;
  declare xsuma_hodin_odvedenych_vcera    MNOZSTVI;
  declare xpocet_unikatnich_id            UCETNI_DOKLAD;
  declare xpocet_unikatnich_z_vc          UCETNI_DOKLAD;
  declare xpocet_unikatnich_z_vc_co       UCETNI_DOKLAD;
  declare xpocet_dupl_id                  UCETNI_DOKLAD;
  declare xpocet_dupl_z_vc                UCETNI_DOKLAD;
  declare xpocet_dupl_z_vc_co             UCETNI_DOKLAD;
  declare xpocet_prohozenych_id           UCETNI_DOKLAD;
  declare xpocet_zpozdenych_id            UCETNI_DOKLAD;
  declare xpocet_prohozeni_zak_vc         UCETNI_DOKLAD;
  declare xpocet_zpozdenych_zak_vc        UCETNI_DOKLAD;

  declare xpocet_unik_z_vc_pred_kont      UCETNI_DOKLAD;
  declare xpocet_unik_z_vc_za_kont        UCETNI_DOKLAD;
  declare xpocet_unik_z_vc_nedod_koop     UCETNI_DOKLAD;
  declare xpocet_unik_z_vc_nedod_koop_k   UCETNI_DOKLAD;
  declare xnorm_unik_z_vc_nedod_koop_k    MNOZSTVI;
  declare xpocet_unik_z_vc_nedod_koop_t   UCETNI_DOKLAD;
  declare xpocet_zpozdenych_v_dilu        UCETNI_DOKLAD;
  declare xpocet_zpozdenych_mc            UCETNI_DOKLAD;
  declare xpocet_zadano_ctecka            UCETNI_DOKLAD;
  declare xpocet_zadano_ctecka_zahajeno   UCETNI_DOKLAD;
  declare xpocet_zadano_ctecka_nezahajeno UCETNI_DOKLAD;
  declare xpocet_operaci_kk_nepoptane     UCETNI_DOKLAD;
  declare xpocet_operaci_kk_bez_terminu   UCETNI_DOKLAD;
  declare xpocet_operaci_kk_neodeslane    UCETNI_DOKLAD;
  declare xnorm_operaci_kk_nepoptane      MNOZSTVI;
  declare xnorm_operaci_kk_bez_terminu    MNOZSTVI;
  declare xnorm_operaci_kk_neodeslane     MNOZSTVI;
  declare xpocet_operaci_tk_nepoptane     UCETNI_DOKLAD;
  declare xpocet_operaci_tk_bez_terminu   UCETNI_DOKLAD;
  declare xpocet_operaci_tk_neodeslane    UCETNI_DOKLAD;
  --pocty NH nehotovych dilu po terminu MP
  declare xpocet_t_koop_po_terminu_mp     UCETNI_DOKLAD;
  declare xpocet_k_koop_po_terminu_mp     UCETNI_DOKLAD;
  declare xnorm_k_koop_po_terminu_mp      MNOZSTVI;
  --pocet zpozdenych dilu k dnesku dle APS
  declare xpocet_aps_zpozdenych_id        UCETNI_DOKLAD;
  declare xpocet_aps_zpozdenych_zak_vc    UCETNI_DOKLAD;
  declare xpocet_aps_zpozdenych_operaci   UCETNI_DOKLAD;
  --pocet zadanych pracovnich listku
  declare xpocet_zadanych_listku          UCETNI_DOKLAD;
  declare xpocet_planovanych_nezadanych   UCETNI_DOKLAD;
  declare xpocet_pripravenych_operaci     UCETNI_DOKLAD;

  declare xdatum_kontrola                 DATUM;
  declare xdatum_dnes                     DATUM;
  declare xpocet                          UCETNI_DOKLAD;
  --pocet generovanych dilu
  declare xpocet_generovanych_dilu        UCETNI_DOKLAD;
  declare xnh_gen_dilu_4                  MNOZSTVI;
  declare xnh_gen_dilu_7                  MNOZSTVI;
  declare xpocet_generovanych_do_zpoz     UCETNI_DOKLAD;
  declare xnh_gen_dilu_4_do_zpoz          MNOZSTVI;
  declare xnh_gen_dilu_7_do_zpoz          MNOZSTVI;
   --pocty typu operaci
  declare xpocet_prof_hotovo                POCET;
  declare xpocet_prof_tech_koop             POCET;
  declare xpocet_prof_obrobna               POCET;
  declare xpocet_prof_cerneni               POCET;
  declare xpocet_prof_lakovani              POCET;
  declare xpocet_prof_kap_koop              POCET;
  declare xpocet_prof_zmetek                POCET;
  declare xpocet_prof_lak_kk                POCET;
  --pocty typu operace pro zpozdena vyr. cisla
  declare xpocet_prof_hotovo_zp_vc                POCET;
  declare xpocet_prof_tech_koop_zp_vc             POCET;
  declare xpocet_prof_obrobna_zp_vc               POCET;
  declare xpocet_prof_cerneni_zp_vc               POCET;
  declare xpocet_prof_lakovani_zp_vc              POCET;
  declare xpocet_prof_kap_koop_zp_vc              POCET;
  declare xpocet_prof_zmetek_zp_vc                POCET;
  declare xpocet_prof_lak_kk_zp_vc                POCET;
  declare xprumer_zpoz_prof_zp_vc                 MNOZSTVI;
  declare xmedian_zpoz_prof_zp_vc                 MNOZSTVI;
  --ukazatele objednavani hutare
  declare xrez_pocet_op_neobj_pred_lpst     POCET;
  declare xrez_pocet_op_neobj_pred_t_obj    POCET;
  declare xrez_pocet_op_neobj_pred_t_zah    POCET;
  --prijem kooperace
  declare xpocet_zak_vc_prijem_koo_kk        POCET;
  declare xpocet_zak_vc_prijem_koo_tk        POCET;
  declare xpocet_zak_vc_prijem_koo_kk1       POCET;
  declare xpocet_zak_vc_prijem_koo_tk1       POCET;
  declare xpocet_zak_vc_prijem_koo_kk2       POCET;
  declare xpocet_zak_vc_prijem_koo_tk2       POCET;
  declare xpocet_zak_vc_prijem_koo_kk3       POCET;
  declare xpocet_zak_vc_prijem_koo_tk3       POCET;
  declare xpocet_zak_vc_prijem_koo_kk4       POCET;
  declare xpocet_zak_vc_prijem_koo_tk4       POCET;
  --nehotove profese zpozdene k M polozkam
  declare xpocet_m_pol_nehot_profese_kk      POCET;
  declare xpocet_m_pol_nehot_profese_tk      POCET;
  --nehotove profese zpozdene ZAK_VC
  declare xpocet_zak_vc_nehot_profese_kk     POCET;
  declare xpocet_zak_vc_nehot_profese_tk     POCET;
  --pocty typu profesi pro zpozdeni APS k ZAK/VC (vc. bud)
  declare xpocet_prof_hotovo_zak_vc          POCET;
  declare xpocet_prof_tech_koop_zak_vc       POCET;
  declare xpocet_prof_obrobna_zak_vc         POCET;
  declare xpocet_prof_cerneni_zak_vc         POCET;
  declare xpocet_prof_lakovani_zak_vc        POCET;
  declare xpocet_prof_kap_koop_zak_vc        POCET;
  declare xpocet_prof_zmetek_zak_vc          POCET;
  declare xpocet_prof_lak_kk_zak_vc          POCET;
  declare xprumer_zpoz_prof_zak_vc           MNOZSTVI;
  declare xmedian_zpoz_prof_zak_vc           MNOZSTVI;
   -- porovnani planu pocet kde zmena terminu a vykres se zadanou a neodvedenou KK
  declare xpocet_planu_zmena_terminu         POCET;
  --zpozdene dily po term. zahajeni/pripravy
  declare xpocet_dilu_po_term_pripravy       POCET;
  declare xpocet_dilu_po_term_zahajeni       POCET;
   --pocet ZAK/VC kde LPST je vetsi nez OZ_TERMIN_POZADOVANY
  declare xpocet_dilu_lpst_vetsi_oz_term     POCET;

  declare xpocet_zmen_tol_vcasnosti_rozp     POCET;
  declare xpocet_zmen_tol_zpozdeni_rozp       POCET;

begin
   select * from ZAK_APS_UKAZATELE_ZAKAZKY
   into :xpocet_z_zak, :xpocet_j_zak, :xpocet_s_zak, :xpocet_o_zak, :xpocet_zak, 
  :xpocet_forecast_vc,:xpocet_nesplnitelnych_zak, :xpocet_auto_ato_vc, :xpocet_zpozdenych_zak, :xcelkove_zpozdeni_zak,
  :xmaximalni_zpozdeni_zak, :xprumerne_zpozdeni_zak,
  :xmedian_zpozdeni_zak,:xpocet_vcasnych_zak,:xpocet_predcasnych_zak, :xcelkova_predcasnost_zak, :xmaximalni_predcasnost_zak, 
  :xprumerna_predcasnost_zak,:xmedian_predcasnost_zak;

  select coalesce (sum(KS.HODINY_OBROBNA),0 ) as NORMATIV_NESPLNITELNYCH_ZAK
    from ZAK_APS_NESPLNITELNE_ZAK NZ
    left outer join ZAR_VYKRESY ZARV on ZARV.ID_VYRABENY_DIL = NZ.PROBLEMSRC_ID and NZ.TYP_PROBLEMSRC_ID = 'V' and ZARV.ZRUSENO = ''
    left outer join DATUM2UCETNI_OBDOBI (current_date) UO on 1=1
    left outer join ZAR_KALKULACE_SOUHRNNA2 (coalesce(ZARV.VYKRES,''),NZ.MNOZSTVI,UO.O_RESULT) KS on 1=1
  into :xnormativ_nesplnitelnych_zak;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 1');

  select count (distinct R.PROFESE)
    from ZAK_ZAKAZKY_AKTIVNI A
    join ZAK_POSTUPY P on P.ZAKAZKA = A.ZAKAZKA
    join ZAK_PROFESE R on R.PROFESE = P.PROFESE and R.STREDISKO = '4'
    where P.ODVEDENO_VSE = '' and P.ZRUSENO = '' and P.STORNOVANO = ''  and P.KOOPERACE = ''
  into :xpocet_profesi_s4;
  --TODO
  xpocet_pretizenych_profesi_s4 = 0;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 2');

  select 
    sum (KAPACITA_NEUVOLNENA) as KAPACITA_NEUVOLNENA,
    sum (KAPACITA_UVOLNENA) as KAPACITA_UVOLNENA,
    sum (KAPACITA_ZADANA) as KAPACITA_ZADANA,
    sum (KOOPERACE_NEUVOLNENA) as KOOPERACE_NEUVOLNENA,
    sum (KOOPERACE_UVOLNENA) as KOOPERACE_UVOLNENA,
    sum (KOOPERACE_ZADANA) as KOOPERACE_ZADANA,
    sum (KOOPERACE_OBJEDNANA) as KOOPERACE_OBJEDNANA
  from KAP_VYROBA_POSTUPY KVP
    left outer join ZAK_PROFESE R on R.PROFESE = KVP.PROFESE 
  where R.STREDISKO = '4'
  into :xkapacita_neuvolnena_s4,  :xkapacita_uvolnena_s4, :xkapacita_zadana_s4,
       :xkooperace_neuvolnena_s4, :xkooperace_uvolnena_s4, :xkooperace_zadana_s4, :xkooperace_objednana_s4;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 3');
  --TODO
  xsuma_previs_normohodin = 0;
  xsuma_nedostatku_normohodin = 0;
  xvcerejsi_kapacita_dle_kz = 0;
  xvcerejsi_skutecna_dochazka = 0;
  xsuma_hodin_odvedenych_vcera = 0;
  
  select count(*) as POCET_ID, sum (POCET_ZAK_VC) as POCET_ZAK_VC, sum (POCET_ZAK_VC_CO) as POCET_ZAK_VC_CO from
  (select H.ITEM, count(distinct H.PRODUCTIONORDERID) as POCET_ZAK_VC, count (*) as POCET_ZAK_VC_CO
    from I2_SFCHEADER H     
    left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
    left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    left outer join ZAK_POSTUPY P on P.ZAKAZKA = D.ZAKAZKA and P.VYROBNI_CISLO = D.VYROBNI_CISLO and P.VETEV = D.VETEV
    left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
    where 1=1 and 
      KS.SYSTEMOVY_KOD = '' and 
      KS.SYSTEM_VYRABENE_DILY = '' and 
      KS.SYSTEM_UPRAVA_DILU = '' and 
      KS.MONTOVATELNA_SKUPINA = '' and 
      KS.MATERIAL = '' and P.ODVEDENO_VSE = '' and P.ZRUSENO = '' and P.NEVYROBNI = '' and R.STREDISKO in ('4','7')
    and QTYCOMPLETED < QTYORDERED
    group by H.ITEM)
  into :xpocet_unikatnich_id, :xpocet_unikatnich_z_vc, :xpocet_unikatnich_z_vc_co;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 4');

select * From GET_TERMIN_SKUTECNY (current_date, 1)
    into :xdatum_kontrola;
select 
  count(*)
    from I2_SFCHEADER H     
    left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
    left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    left outer join ZAK_POSTUPY P on P.ZAKAZKA = D.ZAKAZKA and P.VYROBNI_CISLO = D.VYROBNI_CISLO and P.VETEV = D.VETEV
    left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
    where 1=1 and 
      KS.SYSTEMOVY_KOD = '' and 
      KS.SYSTEM_VYRABENE_DILY = '' and 
      KS.SYSTEM_UPRAVA_DILU = '' and 
      KS.MONTOVATELNA_SKUPINA = '' and 
      KS.MATERIAL = '' and P.ZRUSENO = '' and P.NEVYROBNI = '' and R.STREDISKO in ('4','7') and 
      P.MNOZSTVI_ODVEDENO > 0 and P.MNOZSTVI_KONTROLOVANO + P.MNOZSTVI_ZMETEK < P.MNOZSTVI_ODVEDENO and 
      cast((select max(VP.DATUM_ODVEDENI) from ZAK_VYROBNI_PRIKAZY VP 
        where VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE) as date) <= :xdatum_kontrola
  into :xpocet_unik_z_vc_pred_kont;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 5');

select count(*) from 
  (select distinct PS.ZAKAZKA_VYROBNI, PS.VYROBNI_CISLO_VYROBNI 
    from ZAK_DILY_PRED_SKLADEM('0', 0) PS)
  into :xpocet_unik_z_vc_za_kont;

select count (distinct P.ZAKAZKA || '%' || P.VYROBNI_CISLO || '%' ||P.VETEV) POCET,
  count(distinct iif (VP.KOOPERACE = '*', P.ZAKAZKA || '%' || P.VYROBNI_CISLO || '%' ||P.VETEV, null)) POCET_T,
  count(distinct iif (VP.KOOPERACE = '+', P.ZAKAZKA || '%' || P.VYROBNI_CISLO || '%' ||P.VETEV, null)) POCET_K,
  sum(iif (VP.KOOPERACE = '+', P.NORMATIV_HODIN_KS * VP.MNOZSTVI, null)) NORMATIV
    from I2_SFCHEADER H     
    left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
    left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    left outer join ZAK_POSTUPY P on P.ZAKAZKA = D.ZAKAZKA and P.VYROBNI_CISLO = D.VYROBNI_CISLO and P.VETEV = D.VETEV
    left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
    left outer join MTZ_OBJEDNAVKY O on O.OBJEDNAVKA = VP.OBJEDNAVKA
    left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
    where 1=1 and 
      KS.SYSTEMOVY_KOD = '' and 
      KS.SYSTEM_VYRABENE_DILY = '' and 
      KS.SYSTEM_UPRAVA_DILU = '' and 
      KS.MONTOVATELNA_SKUPINA = '' and 
      KS.MATERIAL = '' and P.ZRUSENO = '' and P.NEVYROBNI = '' and (R.STREDISKO in ('4','7') or (R.LAKOVNA = '*')) and 
      VP.OBJEDNAVKA > 0 and VP.ODVEDENO = '' and VP.ZMETEK = '' and VP.STORNOVANO = '' and 
      VP.TERMIN_POTVRZENY <= (:xdatum_kontrola) and (VP.DATUM_ODVEDENI is null )
  into :xpocet_unik_z_vc_nedod_koop, :xpocet_unik_z_vc_nedod_koop_t, :xpocet_unik_z_vc_nedod_koop_k, :xnorm_unik_z_vc_nedod_koop_k;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 6');

  select count(*) as POCET_ID, sum (POCET_ZAK_VC) as POCET_ZAK_VC, sum (POCET_ZAK_VC_CO) as POCET_ZAK_VC_CO from
  (select H.ITEM, count(distinct H.PRODUCTIONORDERID) as POCET_ZAK_VC, count (*) as POCET_ZAK_VC_CO
    from I2_SFCHEADER H     
    left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
    left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    left outer join ZAK_POSTUPY P on P.ZAKAZKA = D.ZAKAZKA and P.VYROBNI_CISLO = D.VYROBNI_CISLO and P.VETEV = D.VETEV
    left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
    where 1=1 and 
      KS.SYSTEMOVY_KOD = '' and 
      KS.SYSTEM_VYRABENE_DILY = '' and 
      KS.SYSTEM_UPRAVA_DILU = '' and 
      KS.MONTOVATELNA_SKUPINA = '' and 
      KS.MATERIAL = '' and P.ODVEDENO_VSE = '' and P.ZRUSENO = '' and P.NEVYROBNI = '' and R.STREDISKO in ('4','7')
    and QTYCOMPLETED < QTYORDERED
    group by H.ITEM
    having count(distinct H.PRODUCTIONORDERID) > 1)
  into :xpocet_dupl_id, :xpocet_dupl_z_vc, :xpocet_dupl_z_vc_co;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 7');

  select count (distinct ITEM) as POCET_PROHOZENYCH_ID,
       sum (iif (POCET_ZPOZDENYCH > 0,1,0)) as POCET_ZPOZDENYCH_ID,
       sum (POCET_PROHOZENI) as POCET_PROHOZENI_ZAK_VC, 
       sum (POCET_ZPOZDENYCH) as POCET_ZPOZDENYCH_ZAK_VC From I2FP_GET_ID_SPATNE_TERMINY
  into :xpocet_prohozenych_id, :xpocet_zpozdenych_id, :xpocet_prohozeni_zak_vc, :xpocet_zpozdenych_zak_vc;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 8');


  select count(*) as POCET_V, sum(POCET) as POCET_M, sum(PO_PRIPRAVE) PO_PRIPRAVE, sum(PO_ZAHAJENI) PO_ZAHAJENI
    from (select  ZP.ZAKAZKA_VYROBNI, ZP.VYROBNI_CISLO_VYROBNI, count(*) as POCET,
    coalesce(max(iif(cast (ZP.DATUM_DOKONCENI as date) > cast (OZ.OZ_TERMIN_POZADOVANY as date),1,0)),0) PO_PRIPRAVE,    
    coalesce(max(iif(cast (ZP.DATUM_DOKONCENI as date) > cast (OZ.OZ_MIN_TERMIN_ZAHAJENI_MP as date),1,0)),0) PO_ZAHAJENI
    from ZAK_APS_ZPOZDENI ZP
    left outer join ZAR_VYKRESY V on ZP.TYP_DILU = 'V' and V.ID_VYRABENY_DIL = ZP.ID_DILU and V.ZRUSENO = ''
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    left outer join ZAK_VYRABENY_DIL_ODB_ZAK_SELECT(ZP.ZAKAZKA_VYROBNI, ZP.VYROBNI_CISLO_VYROBNI) OZ on 1=1
    where (ZP.TYP_DILU = 'V' and KS.MONTOVATELNA_SKUPINA = '') and (ZP.ZPOZDENI > 0) 
    group by ZP.ZAKAZKA_VYROBNI, ZP.VYROBNI_CISLO_VYROBNI)
  into :xpocet_zpozdenych_v_dilu, :xpocet_zpozdenych_mc, :xpocet_dilu_po_term_pripravy, :xpocet_dilu_po_term_zahajeni;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 9');




  xdatum_dnes = current_date;

  select coalesce(sum (1),0) CELKEM, coalesce(sum (iif (ZAHAJENE = '*', 1, 0)),0) ZAHAJENE, coalesce(sum (iif (ZAHAJENE = '', 1, 0)),0) NEZAHAJENE
    from (
      select distinct P.ZAKAZKA, P.VYROBNI_CISLO, P.VETEV,
          iif (exists (select * from ZAK_VYROBNI_PRIKAZY VP2
              join ZAK_VYROBNI_PRIKAZY_CASY PC on PC.ZAKAZKA = VP2.ZAKAZKA and PC.VYROBNI_CISLO = VP2.VYROBNI_CISLO and PC.CISLO_OPERACE = VP2.CISLO_OPERACE
              where VP2.ZAKAZKA = VP.ZAKAZKA and VP2.VYROBNI_CISLO = VP.VYROBNI_CISLO
                  and ((VP.CISLO_SLOUCENEHO_VP > 0 and VP.CISLO_SLOUCENEHO_VP = VP2.CISLO_SLOUCENEHO_VP)
                      or (VP.CISLO_OPERACE = VP2.CISLO_OPERACE))), '*', '') ZAHAJENE
        from I2_SFCHEADER H    
        left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
        left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
        left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
        left outer join ZAK_POSTUPY P on P.ZAKAZKA = D.ZAKAZKA and P.VYROBNI_CISLO = D.VYROBNI_CISLO and P.VETEV = D.VETEV
        left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
        left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
        where 1=1 and
          KS.SYSTEMOVY_KOD = '' and
          KS.SYSTEM_VYRABENE_DILY = '' and
          KS.SYSTEM_UPRAVA_DILU = '' and
          KS.MONTOVATELNA_SKUPINA = '' and
          KS.MATERIAL = '' and P.ZRUSENO = '' and P.NEVYROBNI = ''
          and VP.KOOPERACE = '' and P.STREDISKO = '4'
          --zadane kde termin zahajeni je mensi/rovno datum
          and P.ZADANO = '*' and P.TERMIN_ZAHAJENI <= :xdatum_dnes
          and P.NORMATIV_HODIN > 7.5)
    into :xpocet_zadano_ctecka, :xpocet_zadano_ctecka_zahajeno, :xpocet_zadano_ctecka_nezahajeno;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 10');


    xdatum_dnes = current_date - 1;
    select coalesce(sum(iif (NEZPRACOVANO = '*', 1, 0)),0) NEJSOU_POPTANE, coalesce(sum(iif (POTVRZENY_TERMIN is null, 1, 0)),0) NEMAJI_POTVRZENY_TERMIN, coalesce(sum (iif(DATUM_ODESLANI is null, 1, 0)),0) NEJSOU_ODESLANE,
       coalesce(sum(iif (NEZPRACOVANO = '*', NORMATIV_HODIN, 0)),0) NH_NEJSOU_POPTANE, coalesce(sum(iif (POTVRZENY_TERMIN is null, NORMATIV_HODIN, 0)),0) NH_NEMAJI_POTVRZENY_TERMIN, coalesce(sum (iif(DATUM_ODESLANI is null, NORMATIV_HODIN, 0)),0) NH_NEJSOU_ODESLANE
      from (
        select distinct P.ZAKAZKA, P.VYROBNI_CISLO, P.CISLO_OPERACE, P.VETEV, P.KOOPERACE_ZMENA,
          iif (P.MNOZSTVI_NEZPRACOVANO > 0, '*', '') NEZPRACOVANO,
          (select max(VP.TERMIN_POTVRZENY) from ZAK_VYROBNI_PRIKAZY VP where VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE) POTVRZENY_TERMIN,
          (select max(O.DATUM_ODESLANI) from ZAK_VYROBNI_PRIKAZY VP
            join MTZ_OBJEDNAVKY O on O.OBJEDNAVKA = VP.OBJEDNAVKA
              where VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE) DATUM_ODESLANI, P.NORMATIV_HODIN
          from I2_SFCHEADER H    
          left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
          left outer join ZAK_ZAKAZKY Z on Z.ZAKAZKA = D.ZAKAZKA 
          left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
          left outer join ZAK_VYROBNI_DAVKY VD on VD.DAVKA = V.DAVKA
          left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
          left outer join ZAK_POSTUPY P on P.ZAKAZKA = D.ZAKAZKA and P.VYROBNI_CISLO = D.VYROBNI_CISLO and P.VETEV = D.VETEV
          where 1=1 and
            KS.SYSTEMOVY_KOD = '' and
            KS.SYSTEM_VYRABENE_DILY = '' and
            KS.SYSTEM_UPRAVA_DILU = '' and
            KS.MONTOVATELNA_SKUPINA = '' and
            KS.MATERIAL = '' and P.ZRUSENO = '' and P.NEVYROBNI = ''
            and P.KOOPERACE <> '' and P.KOOPERACE_TECHNOLOGICKA = ''
            and P.ODVEDENO = '' and P.KONTROLOVANO = ''
            and (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(cast (maxvalue(P.KOOPERACE_ZMENA,Z.DATUM_BLOKACE) as date), -7)) < :xdatum_dnes 
            and Z.ZAKAZKA_BLOKOVANA = '' and VD.DAVKA_BLOKOVANA = '')
    into :xpocet_operaci_kk_nepoptane, :xpocet_operaci_kk_bez_terminu, :xpocet_operaci_kk_neodeslane,
          :xnorm_operaci_kk_nepoptane, :xnorm_operaci_kk_bez_terminu, :xnorm_operaci_kk_neodeslane;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 11');





    select coalesce(sum(iif (NEZPRACOVANO = '*', 1, 0)),0) NEJSOU_POPTANE, coalesce(sum(iif (POTVRZENY_TERMIN is null, 1, 0)),0) NEMAJI_POTVRZENY_TERMIN, coalesce(sum (iif(DATUM_ODESLANI is null, 1, 0)),0) NEJSOU_ODESLANE
      from (
        select distinct P.ZAKAZKA, P.VYROBNI_CISLO, P.CISLO_OPERACE, P.VETEV, P.KOOPERACE_ZMENA,
          iif (P.MNOZSTVI_NEZPRACOVANO > 0, '*', '') NEZPRACOVANO,
          (select max(VP.TERMIN_POTVRZENY) from ZAK_VYROBNI_PRIKAZY VP where VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE) POTVRZENY_TERMIN,
          (select max(O.DATUM_ODESLANI) from ZAK_VYROBNI_PRIKAZY VP
            join MTZ_OBJEDNAVKY O on O.OBJEDNAVKA = VP.OBJEDNAVKA
              where VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE) DATUM_ODESLANI
               
          from I2_SFCHEADER H    
          left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
          left outer join ZAK_ZAKAZKY Z on Z.ZAKAZKA = D.ZAKAZKA 
          left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
          left outer join ZAK_VYROBNI_DAVKY VD on VD.DAVKA = V.DAVKA
          left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
          left outer join ZAK_POSTUPY P on P.ZAKAZKA = D.ZAKAZKA and P.VYROBNI_CISLO = D.VYROBNI_CISLO and P.VETEV = D.VETEV
          where 1=1 and
            KS.SYSTEMOVY_KOD = '' and
            KS.SYSTEM_VYRABENE_DILY = '' and
            KS.SYSTEM_UPRAVA_DILU = '' and
            KS.MONTOVATELNA_SKUPINA = '' and
            KS.MATERIAL = '' and P.ZRUSENO = '' and P.NEVYROBNI = ''
            and P.KOOPERACE <> '' and P.KOOPERACE_TECHNOLOGICKA <> ''
            --test na dokoncenou predchozi - tzn. ze je operace pripravena
            and P.PRIPRAVENO = '*'
            --neexistuje predchozi mene jak 3 dny kontrolovana
            and not exists (select * from ZAK_POSTUPY PRED where PRED.ZAKAZKA = P.ZAKAZKA and PRED.VYROBNI_CISLO = P.VYROBNI_CISLO and PRED.NASLEDUJICI_OPERACE = P.CISLO_OPERACE
                and (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(cast (PRED.DATUM_DOKONCENI as date), -3)) >= :xdatum_dnes)
            --neexistuje kontrola deti v poslednich 3 pracovnich dnech
            and not exists (select *
                          from ZAK_ROZPISKY R
                          left outer join ZAK_ROZPISKY DETI on DETI.ZAKAZKA = R.ZAKAZKA and DETI.MONTAZNI_CISLO_RODIC = R.MONTAZNI_CISLO
                          left outer join ZAK_POSTUPY PD on PD.ZAKAZKA = DETI.ZAKAZKA and PD.VYROBNI_CISLO = DETI.VYROBNI_CISLO
                          where R.ZAKAZKA = P.ZAKAZKA and R.VYROBNI_CISLO = P.VYROBNI_CISLO and
                            (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(PD.DATUM_DOKONCENI, -3)) >= :xdatum_dnes)
            --generovano bylo pred vice jak 3 pracovnimi dny
            and not exists (select * from ZAK_ROZPISKY R join ZAK_VYROBNI_DAVKY VD on VD.DAVKA = R.DAVKA
                    where R.ZAKAZKA = P.ZAKAZKA and R.VYROBNI_CISLO = P.VYROBNI_CISLO and
                   (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(maxvalue(VD.DATUM_PREDANI, Z.DATUM_BLOKACE), -3)) >= :xdatum_dnes
            and Z.ZAKAZKA_BLOKOVANA = '' and VD.DAVKA_BLOKOVANA = '')
      )
    into   :xpocet_operaci_tk_nepoptane, :xpocet_operaci_tk_bez_terminu, :xpocet_operaci_tk_neodeslane;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 12');

  
  --pocet K|T kooperace s vyplněnym terminem dodani polotovaru
  select 
    count(distinct iif (VP.KOOPERACE = '*', VP.OBJEDNAVKA || '%' || VP.POLOZKA_OBJEDNAVKY, null)) POCET_T,
    count(distinct iif (VP.KOOPERACE = '+', VP.OBJEDNAVKA || '%' || VP.POLOZKA_OBJEDNAVKY, null)) POCET_K,
    cast (coalesce (sum(iif (VP.KOOPERACE = '+', VP.MNOZSTVI * P.NORMATIV_HODIN_KS, null)), 0) as MNOZSTVI) NORMATIV_K,
    count(distinct iif (VP.KOOPERACE = '*' and VP.DATUM_EXPEDICE_MATERIALU > VP.TERMIN_DODANI_POLOTOVARU, VP.OBJEDNAVKA || '%' || VP.POLOZKA_OBJEDNAVKY, null)) POCET_T_POZDE,
    count(distinct iif (VP.KOOPERACE = '+' and VP.DATUM_EXPEDICE_MATERIALU > VP.TERMIN_DODANI_POLOTOVARU, VP.OBJEDNAVKA || '%' || VP.POLOZKA_OBJEDNAVKY, null)) POCET_K_POZDE,
    cast (coalesce (sum(iif (VP.KOOPERACE = '+' and VP.DATUM_EXPEDICE_MATERIALU > VP.TERMIN_DODANI_POLOTOVARU, VP.MNOZSTVI * P.NORMATIV_HODIN_KS, null)), 0) as MNOZSTVI) NORMATIV_K_POZDE,
    count(distinct iif (VP.KOOPERACE = '*' and VP.DATUM_EXPEDICE_MATERIALU <= VP.TERMIN_DODANI_POLOTOVARU, VP.OBJEDNAVKA || '%' || VP.POLOZKA_OBJEDNAVKY, null)) POCET_T_VCAS,
    count(distinct iif (VP.KOOPERACE = '+' and VP.DATUM_EXPEDICE_MATERIALU <= VP.TERMIN_DODANI_POLOTOVARU, VP.OBJEDNAVKA || '%' || VP.POLOZKA_OBJEDNAVKY, null)) POCET_K_VCAS,
    cast (coalesce (sum(iif (VP.KOOPERACE = '+' and VP.DATUM_EXPEDICE_MATERIALU <= VP.TERMIN_DODANI_POLOTOVARU, VP.MNOZSTVI * P.NORMATIV_HODIN_KS, null)), 0) as MNOZSTVI) NORMATIV_K_VCAS,
    count(distinct iif (VP.KOOPERACE = '*' and VP.DATUM_EXPEDICE_MATERIALU is null and VP.TERMIN_DODANI_POLOTOVARU < :xdatum_dnes, VP.OBJEDNAVKA || '%' || VP.POLOZKA_OBJEDNAVKY, null)) POCET_T_NEEX_PO_TERM,
    count(distinct iif (VP.KOOPERACE = '+' and VP.DATUM_EXPEDICE_MATERIALU is null and VP.TERMIN_DODANI_POLOTOVARU < :xdatum_dnes, VP.OBJEDNAVKA || '%' || VP.POLOZKA_OBJEDNAVKY, null)) POCET_K_NEEX_PO_TERM,
    cast (coalesce (sum(iif (VP.KOOPERACE = '+' and VP.DATUM_EXPEDICE_MATERIALU is null and VP.TERMIN_DODANI_POLOTOVARU < :xdatum_dnes, VP.MNOZSTVI * P.NORMATIV_HODIN_KS, null)), 0) as MNOZSTVI) NORMATIV_K_NEEX_PO_TERM
  from I2_SFCHEADER H 
  left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
  left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
  left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
  left outer join ZAK_POSTUPY P on P.ZAKAZKA = V.ZAKAZKA and P.VYROBNI_CISLO = V.VYROBNI_CISLO
  left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
  left outer join MTZ_OBJEDNAVKY O on O.OBJEDNAVKA = VP.OBJEDNAVKA
  left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
  where VP.KOOPERACE <> '' and VP.OBJEDNAVKA > 0 and VP.DATUM_ODVEDENI is null and VP.TERMIN_DODANI_POLOTOVARU is not null
      and KS.SYSTEMOVY_KOD = '' and KS.SYSTEM_VYRABENE_DILY = '' and
          KS.SYSTEM_UPRAVA_DILU = '' and KS.MONTOVATELNA_SKUPINA = '' and
          KS.MATERIAL = '' and P.ZRUSENO = '' and P.NEVYROBNI = ''
          and VP.KONTROLOVANO = '' and VP.ZMETEK = '' and VP.STORNOVANO = '' and (R.STREDISKO in ('4','7') or (R.LAKOVNA = '*'))
          and O.DATUM_ODESLANI is not null
          --kontrola ze neobsahuje rezarnu
          and not exists (select * from ZAK_VYROBNI_PRIKAZY VPR join ZAK_POSTUPY PR on PR.ZAKAZKA = VPR.ZAKAZKA and PR.VYROBNI_CISLO = VPR.VYROBNI_CISLO and PR.CISLO_OPERACE = VPR.CISLO_OPERACE
                            join ZAK_PROFESE PROFR on PROFR.PROFESE = PR.PROFESE and PROFR.REZARNA = '*'
                            where VPR.OBJEDNAVKA = VP.OBJEDNAVKA and VPR.POLOZKA_OBJEDNAVKY = VP.POLOZKA_OBJEDNAVKY)
      and O.DATUM_POTVRZENI is not null
  into :xkoop_t_term_dod_pol, :xkoop_k_term_dod_pol, :xkoop_k_term_dod_pol_nh, :xkoop_t_tdp_pozde, :xkoop_k_tdp_pozde, :xkoop_k_tdp_pozde_nh, :xkoop_t_tdp_vcas, :xkoop_k_tdp_vcas, :xkoop_k_tdp_vcas_nh, 
    :xkoop_t_tdp_neex_po_t, :xkoop_k_tdp_neex_po_t, :xkoop_k_tdp_neex_po_t_nh;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 14');

  --pocet dilu zpozdenych vuci terminu montazniho prikazu
  select count (distinct iif (VP.KOOPERACE = '*' and OZ.OZ_TERMIN_POZADOVANY < maxvalue (VP.TERMIN_POTVRZENY, current_timestamp), VP.ZAKAZKA || '%' || VP.VYROBNI_CISLO, null)) ZPOZDENYCH_T,
      count (distinct iif (VP.KOOPERACE = '+' and OZ.OZ_TERMIN_POZADOVANY < maxvalue (VP.TERMIN_POTVRZENY, current_timestamp), VP.ZAKAZKA || '%' || VP.VYROBNI_CISLO, null)) ZPOZDENYCH_K,
      coalesce(sum (iif (VP.KOOPERACE = '+' and OZ.OZ_TERMIN_POZADOVANY < maxvalue (VP.TERMIN_POTVRZENY, current_timestamp), P.NORMATIV_HODIN_KS * VP.MNOZSTVI, null)),0) NH_ZPOZDENYCH_K
    from I2_SFCHEADER H 
    left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
    left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
    left outer join ZAK_VYRABENY_DIL_ODB_ZAK_SELECT(V.ZAKAZKA, V.VYROBNI_CISLO) OZ on 1=1
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    left outer join ZAK_POSTUPY P on P.ZAKAZKA = V.ZAKAZKA and P.VYROBNI_CISLO = V.VYROBNI_CISLO
    left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
    left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
    where VP.KOOPERACE <> '' and VP.OBJEDNAVKA > 0 and VP.DATUM_ODVEDENI is null 
        and KS.SYSTEMOVY_KOD = '' and KS.SYSTEM_VYRABENE_DILY = '' and
            KS.SYSTEM_UPRAVA_DILU = '' and KS.MONTOVATELNA_SKUPINA = '' and
            KS.MATERIAL = '' and P.ZRUSENO = '' and P.NEVYROBNI = ''
            and VP.KONTROLOVANO = '' and VP.ZMETEK = '' and VP.STORNOVANO = '' and (R.STREDISKO in ('4','7') or (R.LAKOVNA='*'))
            and OZ.OZ_TERMIN_POZADOVANY is not null and VP.TERMIN_POTVRZENY is not null
            and current_timestamp - coalesce (VP.TERMIN_POTVRZENY, VP.TERMIN_POZADAVKU) > 0
    into :xpocet_t_koop_po_terminu_mp, :xpocet_k_koop_po_terminu_mp, :xnorm_k_koop_po_terminu_mp;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 15');


  xdatum_dnes = current_date;
  --Pocet dilu zpozdenych dle APS
  select count (distinct V.VYKRES) POCET_ID, count(distinct V.PKID) POCET_ZAK_VC, count(distinct P.PKID) POCET_ZAK_VC_CO
    from ZAK_APS_ZPOZDENI AZ
    left outer join ZAR_VYKRESY ZV  on AZ.TYP_DILU = 'V' and ZV.ID_VYRABENY_DIL = AZ.ID_DILU and ZV.ZRUSENO = ''
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD = ZV.KOD_SKUPINY
    left outer join ZAK_VYKRESY V on V.ZAKAZKA = AZ.ZAKAZKA_VYROBNI and V.VYROBNI_CISLO = AZ.VYROBNI_CISLO_VYROBNI
    left outer join ZAK_POSTUPY P on P.ZAKAZKA = V.ZAKAZKA and P.VYROBNI_CISLO = V.VYROBNI_CISLO
    left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
    where (AZ.TYP_DILU = 'V' and KS.MONTOVATELNA_SKUPINA = '') and (AZ.ZPOZDENI > 0) 
          and P.ZRUSENO = ''
          and P.NEVYROBNI = ''
          and P.KONTROLOVANO = ''
          and P.STORNOVANO = ''
          and (R.STREDISKO in ('4','7') or (R.LAKOVNA = '*'))
          and cast (AZ.DATUM_POTREBY as DATE) <= :xdatum_dnes
      into :xpocet_aps_zpozdenych_id, :xpocet_aps_zpozdenych_zak_vc, :xpocet_aps_zpozdenych_operaci;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 16');


  select count(distinct iif(VP.DATUM_ZADANI = (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(current_date, 1)), VP.ZAKAZKA || '%' || VP.VYROBNI_CISLO || '%' || VP.CISLO_OPERACE, null)) ZADANYCH,
        count(distinct (iif (PLP.TERMIN_ZAHAJENI = (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(current_date, 1)) and VP.PKID is null, PLP.ZAKAZKA || '%' || PLP.VYROBNI_CISLO || '%' || PLP.CISLO_OPERACE, null))) PLANOVANO_NEZADANO,
        count(distinct (iif (P.PRIPRAVENO = '*' and VP.PKID is null, PLP.ZAKAZKA || '%' || PLP.VYROBNI_CISLO || '%' || PLP.CISLO_OPERACE, null))) MOZNO_PRIPRAVENO
    from I2_SFCHEADER H     
    left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
    left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    left outer join ZAK_POSTUPY P on P.ZAKAZKA = D.ZAKAZKA and P.VYROBNI_CISLO = D.VYROBNI_CISLO and P.VETEV = D.VETEV
    left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
    left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
    --plan z predchoziho pracovniho dne
    left outer join ZAK_APS_PLAN PL on cast (PL.DATUM_ULOZENI as date) = (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(current_date, 1))
    left outer join ZAK_APS_PLAN_POSTUPY PLP on PLP.PKID_PLANU = PL.PKID and PLP.ZAKAZKA = P.ZAKAZKA and PLP.VYROBNI_CISLO = P.VYROBNI_CISLO and PLP.CISLO_OPERACE = P.CISLO_OPERACE
    where 1=1 and P.PROFESE not in ('0629', '0955') and R.REZARNA = '' and
      KS.SYSTEMOVY_KOD = '' and 
      KS.SYSTEM_VYRABENE_DILY = '' and 
      KS.SYSTEM_UPRAVA_DILU = '' and 
      KS.MONTOVATELNA_SKUPINA = '' and 
      KS.MATERIAL = '' and P.ODVEDENO_VSE = '' and P.ZRUSENO = '' and P.NEVYROBNI = '' and R.STREDISKO = '4' and P.KOOPERACE = ''
    into :xpocet_zadanych_listku, :xpocet_planovanych_nezadanych, :xpocet_pripravenych_operaci;
  xdatum_dnes = current_date - 1;
  --TB20161129 Pocet generovanych dilu s operaci strediska 4 nebo 7 + normativ
  select count(distinct V.ZAKAZKA || '%' || V.VYROBNI_CISLO), 
      coalesce (sum(iif (P.KOOPERACE <> '', 0, P.NORMATIV_HODIN)), 0) NORMATIV_HODIN_OBR, 
      coalesce (sum(iif(P.KOOPERACE = '', 0, P.NORMATIV_HODIN)), 0) NORMATIV_HODIN_KOOP,
      --TB20170208 Hodiny hned do zpozdeni
      count (distinct iif (P.TERMIN_LPST <= DAV.DATUM_PREDANI, V.ZAKAZKA || '%' || V.VYROBNI_CISLO, null)) GENEROVANO_DO_ZPOZDENI,
      coalesce (sum(iif (P.KOOPERACE = '' and P.TERMIN_LPST <= DAV.DATUM_PREDANI, P.NORMATIV_HODIN, 0)), 0) NORMATIV_HODIN_OBR_ZPOZENI, 
      coalesce (sum(iif(P.KOOPERACE <> '' and P.TERMIN_LPST <= DAV.DATUM_PREDANI, P.NORMATIV_HODIN, 0)), 0) NORMATIV_HODIN_KOOP_ZPOZDENI
    from I2_SFCHEADER H 
    left outer join I2FP_DECODE_VD_BOMID (H.PRODUCTIONORDERID) D on 1=1
    left outer join ZAK_VYKRESY V on V.ZAKAZKA = D.ZAKAZKA and V.VYROBNI_CISLO = D.VYROBNI_CISLO
    left outer join ZAK_VYROBNI_DAVKY DAV on DAV.DAVKA = V.DAVKA
    join ZAK_POSTUPY P on P.ZAKAZKA = V.ZAKAZKA and P.VYROBNI_CISLO = V.VYROBNI_CISLO and P.STREDISKO in ('4', '7')
    where cast (DAV.DATUM_PREDANI as date) = :xdatum_dnes
    into :xpocet_generovanych_dilu, :xnh_gen_dilu_4, :xnh_gen_dilu_7, :xpocet_generovanych_do_zpoz, :xnh_gen_dilu_4_do_zpoz, :xnh_gen_dilu_7_do_zpoz;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 17');


    --Pocty typu operaci
  select PP.POCET_PROF_HOTOVO,PP.POCET_PROF_KAP_KOOP,PP.POCET_PROF_TECH_KOOP,
        PP.POCET_PROF_CERNENI, PP.POCET_PROF_LAKOVANI, PP.POCET_PROF_LAKOVANI_KK,
        PP.POCET_PROF_OBROBNA, PP.POCET_PROF_ZMETEK
    from ZAK_APS_POCTY_PROFESI (0) PP
    into :xpocet_prof_hotovo, :xpocet_prof_kap_koop, :xpocet_prof_tech_koop,
        :xpocet_prof_cerneni, :xpocet_prof_lakovani, :xpocet_prof_lak_kk, 
        :xpocet_prof_obrobna, :xpocet_prof_zmetek;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 18');


  xdatum_dnes = current_date;
  --Pocty typu operaci pro zpozdena vyrobni cisla
  select PPV.POCET_PROF_HOTOVO,PPV.POCET_PROF_KAP_KOOP,PPV.POCET_PROF_TECH_KOOP,
        PPV.POCET_PROF_CERNENI, PPV.POCET_PROF_LAKOVANI, PPV.POCET_PROF_LAKOVANI_KK,
        PPV.POCET_PROF_OBROBNA, PPV.POCET_PROF_ZMETEK, PPV.MEDIAN_ZPOZDENI,
        PPV.PRUMER_ZPOZDENI
    from ZAK_APS_POCTY_PROFESI (2, 0, '',:xdatum_dnes) PPV
    into :xpocet_prof_hotovo_zp_vc, :xpocet_prof_kap_koop_zp_vc, :xpocet_prof_tech_koop_zp_vc,
        :xpocet_prof_cerneni_zp_vc, :xpocet_prof_lakovani_zp_vc, :xpocet_prof_lak_kk_zp_vc, 
        :xpocet_prof_obrobna_zp_vc, :xpocet_prof_zmetek_zp_vc,
        :xmedian_zpoz_prof_zp_vc, :xprumer_zpoz_prof_zp_vc;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 19');


  --prepocitani ukazatelu rezarny
  execute procedure ZAK_MATERIALY_ROZDELENI_HUTNI;
  
  select count(*) as POCET
  from ZAK_ZAKAZKY_AKTIVNI Z
  join ZAK_POSTUPY MAIN on MAIN.ZAKAZKA = Z.ZAKAZKA 
  join ZAK_PROFESE ZP on ZP.PROFESE = MAIN.PROFESE
  left outer join ZAK_MATERIALY M on M.ZAKAZKA = Z.ZAKAZKA  and MAIN.VYROBNI_CISLO = M.VM_CISLO and MAIN.CISLO_OPERACE = M.CISLO_OPERACE and M.ZRUSENO = '' and M.HLAVNI_MATERIAL = '*'
  left outer join MTZ_MATERIAL MTZ_MAT on MTZ_MAT.NAZEV_MATERIALU = M.NAZEV_MATERIALU
  left outer join ZAK_MATERIALY_ROZDELENI ZAK_MP on ZAK_MP.ZAKAZKA = MAIN.ZAKAZKA and ZAK_MP.VM_CISLO = MAIN.VYROBNI_CISLO and ZAK_MP.CISLO_OPERACE = MAIN.CISLO_OPERACE and 
                                                  ZAK_MP.NAZEV_MATERIALU = M.NAZEV_MATERIALU
  where MAIN.ODEVZDANO = '*' and MAIN.ZRUSENO = '' and MAIN.DOKONCENO = '' and ZP.REZARNA = '*'  and MAIN.MNOZSTVI_ZADANO = 0 and (MAIN.KOOPERACE = '') 
    and ((current_date >= minvalue (coalesce ((select min (VP.TERMIN_DODANI_POLOTOVARU) TERMIN_DODANI_POLOTOVARU From ZAK_ROZPISKY R 
      left outer join ZAK_STRUKTURA_ROZPISKY_UP (R.ZAKAZKA, R.MONTAZNI_CISLO) SRU on 1=1
      left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = SRU.O_ZAKAZKA and VP.VYROBNI_CISLO = SRU.O_VYROBNI_CISLO 
      where R.ZAKAZKA = MAIN.ZAKAZKA and R.VYROBNI_CISLO = MAIN.VYROBNI_CISLO) - ZP.MEZIOPERACE,MAIN.TERMIN_LPST),MAIN.TERMIN_LPST ) - 40 - coalesce (MTZ_MAT.DODACI_LHUTA, 0))) and (ZAK_MP.ZAJISTENI = '')
  into :xrez_pocet_op_neobj_pred_lpst;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 20');


  select count(*) as POCET
  from ZAK_ZAKAZKY_AKTIVNI Z
  join ZAK_POSTUPY MAIN on MAIN.ZAKAZKA = Z.ZAKAZKA 
  join ZAK_PROFESE ZP on ZP.PROFESE = MAIN.PROFESE
  left outer join ZAK_MATERIALY M on M.ZAKAZKA = Z.ZAKAZKA  and MAIN.VYROBNI_CISLO = M.VM_CISLO and MAIN.CISLO_OPERACE = M.CISLO_OPERACE and M.ZRUSENO = '' and M.HLAVNI_MATERIAL = '*'
  left outer join MTZ_MATERIAL MTZ_MAT on MTZ_MAT.NAZEV_MATERIALU = M.NAZEV_MATERIALU
  left outer join ZAK_MATERIALY_ROZDELENI ZAK_MP on ZAK_MP.ZAKAZKA = MAIN.ZAKAZKA and ZAK_MP.VM_CISLO = MAIN.VYROBNI_CISLO and ZAK_MP.CISLO_OPERACE = MAIN.CISLO_OPERACE and 
                                                  ZAK_MP.NAZEV_MATERIALU = M.NAZEV_MATERIALU
  where MAIN.ODEVZDANO = '*' and MAIN.ZRUSENO = '' and MAIN.DOKONCENO = '' and ZP.REZARNA = '*'  and MAIN.MNOZSTVI_ZADANO = 0 and (MAIN.KOOPERACE = '') and ((minvalue (coalesce ((select min (VP.TERMIN_DODANI_POLOTOVARU) TERMIN_DODANI_POLOTOVARU From ZAK_ROZPISKY R 
  left outer join ZAK_STRUKTURA_ROZPISKY_UP (R.ZAKAZKA, R.MONTAZNI_CISLO) SRU on 1=1
  left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = SRU.O_ZAKAZKA and VP.VYROBNI_CISLO = SRU.O_VYROBNI_CISLO 
  where R.ZAKAZKA = MAIN.ZAKAZKA and R.VYROBNI_CISLO = MAIN.VYROBNI_CISLO) - ZP.MEZIOPERACE,MAIN.TERMIN_ZAHAJENI),MAIN.TERMIN_ZAHAJENI) - MTZ_MAT.DODACI_LHUTA) <= (current_date + 14) or (MAIN.TERMIN_LPST < MAIN.TERMIN_ZAHAJENI or MAIN.TERMIN_LPST < CURRENT_DATE)) and (ZAK_MP.ZAJISTENI = '')
  into :xrez_pocet_op_neobj_pred_t_obj;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 21');


  select count(*) as POCET
  from ZAK_ZAKAZKY_AKTIVNI Z
  join ZAK_POSTUPY MAIN on MAIN.ZAKAZKA = Z.ZAKAZKA 
  join ZAK_PROFESE ZP on ZP.PROFESE = MAIN.PROFESE
  left outer join ZAK_MATERIALY M on M.ZAKAZKA = Z.ZAKAZKA  and MAIN.VYROBNI_CISLO = M.VM_CISLO and MAIN.CISLO_OPERACE = M.CISLO_OPERACE and M.ZRUSENO = '' and M.HLAVNI_MATERIAL = '*'
  left outer join MTZ_MATERIAL MTZ_MAT on MTZ_MAT.NAZEV_MATERIALU = M.NAZEV_MATERIALU
  left outer join ZAK_MATERIALY_ROZDELENI ZAK_MP on ZAK_MP.ZAKAZKA = MAIN.ZAKAZKA and ZAK_MP.VM_CISLO = MAIN.VYROBNI_CISLO and ZAK_MP.CISLO_OPERACE = MAIN.CISLO_OPERACE and 
                                                  ZAK_MP.NAZEV_MATERIALU = M.NAZEV_MATERIALU
  where MAIN.ODEVZDANO = '*' and MAIN.ZRUSENO = '' and MAIN.DOKONCENO = '' and ZP.REZARNA = '*'  and MAIN.MNOZSTVI_ZADANO = 0 and (MAIN.KOOPERACE = '') 
 and (ZAK_MP.ZAJISTENI = '')
and (minvalue (coalesce ((select min (VP.TERMIN_DODANI_POLOTOVARU) TERMIN_DODANI_POLOTOVARU From ZAK_ROZPISKY R 
  left outer join ZAK_STRUKTURA_ROZPISKY_UP (R.ZAKAZKA, R.MONTAZNI_CISLO) SRU on 1=1
  left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = SRU.O_ZAKAZKA and VP.VYROBNI_CISLO = SRU.O_VYROBNI_CISLO 
  where R.ZAKAZKA = MAIN.ZAKAZKA and R.VYROBNI_CISLO = MAIN.VYROBNI_CISLO) - ZP.MEZIOPERACE,MAIN.TERMIN_ZAHAJENI),MAIN.TERMIN_ZAHAJENI) <= (current_date + 14) or (MAIN.TERMIN_LPST < MAIN.TERMIN_ZAHAJENI or MAIN.TERMIN_LPST < CURRENT_DATE))
  into :xrez_pocet_op_neobj_pred_t_zah;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 22');

  --Prijem kooperace
  xpocet = 1;
  for select count (distinct iif (P.KOOPERACE_TECHNOLOGICKA = '*', V.PKID, null)) TK,
        count (distinct iif (P.KOOPERACE_TECHNOLOGICKA = '', V.PKID, null)) KK
    from ZAK_VYKRESY V
    left outer join ZAK_POSTUPY P on P.ZAKAZKA = V.ZAKAZKA and P.VYROBNI_CISLO = V.VYROBNI_CISLO
    left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
    left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    where KS.SYSTEMOVY_KOD = '' and KS.SYSTEM_VYRABENE_DILY = '' and
      KS.SYSTEM_UPRAVA_DILU = '' and KS.MONTOVATELNA_SKUPINA = '' and
      KS.MATERIAL = '' and P.ZRUSENO = '' and P.NEVYROBNI = '' and P.KONTROLOVANO = '' and P.STORNOVANO = ''
      and extract(week from VP.TERMIN_POTVRZENY) between extract(week from current_date) and extract(week from current_date)+3
      and P.ODVEDENO_VSE = '' and P.KOOPERACE = '*'
      group by extract(week from VP.TERMIN_POTVRZENY)
      order by extract(week from VP.TERMIN_POTVRZENY)
  into :xpocet_zak_vc_prijem_koo_tk, :xpocet_zak_vc_prijem_koo_kk
  
  do begin
    --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 23');
    if(xpocet = 1)then
    begin
    xpocet_zak_vc_prijem_koo_tk1 = xpocet_zak_vc_prijem_koo_tk;
    xpocet_zak_vc_prijem_koo_kk1 = xpocet_zak_vc_prijem_koo_kk;
    end
    if(xpocet = 2)then
    begin
    xpocet_zak_vc_prijem_koo_tk2 = xpocet_zak_vc_prijem_koo_tk;
    xpocet_zak_vc_prijem_koo_kk2 = xpocet_zak_vc_prijem_koo_kk;
    end
    if(xpocet = 3)then
    begin
    xpocet_zak_vc_prijem_koo_tk3 = xpocet_zak_vc_prijem_koo_tk;
    xpocet_zak_vc_prijem_koo_kk3 = xpocet_zak_vc_prijem_koo_kk;
    end
    if(xpocet = 4)then
    begin
    xpocet_zak_vc_prijem_koo_tk4 = xpocet_zak_vc_prijem_koo_tk;
    xpocet_zak_vc_prijem_koo_kk4 = xpocet_zak_vc_prijem_koo_kk;
    end
    xpocet = xpocet + 1;
  end
  
  --Nehotove profese v KK a TK k M polozkam a ZAK/VC
  select sum(iif(substring(NP.PROFESE_EXT from 5 for 5)='K',1,0)) POCET_KK, 
            sum(iif(substring(NP.PROFESE_EXT from 5 for 5)='T',1,0)) POCET_TK
      from ZAK_APS_ZPOZDENI ZP
      left outer join ZAR_VYKRESY V on ZP.TYP_DILU = 'V' and V.ID_VYRABENY_DIL = ZP.ID_DILU and V.ZRUSENO = ''
      left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
  left outer join ZAK_POSTUPY P on P.ZAKAZKA = ZP.ZAKAZKA_VYROBNI and P.VYROBNI_CISLO = ZP.VYROBNI_CISLO_VYROBNI
  left outer join ZAK_VC_NEHOTOVE_PROFESE(zp.ZAKAZKA, zp.VYROBNI_CISLO_VYROBNI) NP on NP.CISLO_OPERACE = P.CISLO_OPERACE
      where (ZP.TYP_DILU = 'V' and KS.MONTOVATELNA_SKUPINA = '') and (ZP.ZPOZDENI > 0) and 
    (P.KOOPERACE > '' ) and NP.CISLO_OPERACE is not null
  into :xpocet_m_pol_nehot_profese_kk, :xpocet_m_pol_nehot_profese_tk;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 24');

  select sum(iif(P.KOOPERACE_TECHNOLOGICKA = '',1,0)) POCET_KK, 
        sum(iif(P.KOOPERACE_TECHNOLOGICKA = '*',1,0)) POCET_TK
        from (select distinct P.PKID, P.KOOPERACE, P.KOOPERACE_TECHNOLOGICKA
      from ZAK_APS_ZPOZDENI ZP
      left outer join ZAR_VYKRESY ZV  on ZP.TYP_DILU = 'V' and ZV.ID_VYRABENY_DIL = ZP.ID_DILU and ZV.ZRUSENO = ''
      left outer join ZAR_KODY_SKUPIN KS on KS.KOD = ZV.KOD_SKUPINY
      left outer join ZAK_VYKRESY V on V.ZAKAZKA = ZP.ZAKAZKA_VYROBNI and V.VYROBNI_CISLO = ZP.VYROBNI_CISLO_VYROBNI
      left outer join ZAK_POSTUPY P on P.ZAKAZKA = V.ZAKAZKA and P.VYROBNI_CISLO = V.VYROBNI_CISLO
      left outer join ZAK_PROFESE R on R.PROFESE = P.PROFESE
              left outer join ZAK_VC_NEHOTOVE_PROFESE(zp.ZAKAZKA, zp.VYROBNI_CISLO_VYROBNI) NP on NP.CISLO_OPERACE = P.CISLO_OPERACE
              where (P.KOOPERACE > '' ) and NP.CISLO_OPERACE is not null
                    and KS.SYSTEMOVY_KOD = '' 
              and KS.SYSTEM_VYRABENE_DILY = ''
              and KS.SYSTEM_UPRAVA_DILU = ''
              and KS.MONTOVATELNA_SKUPINA = ''
              and KS.MATERIAL = ''
              and P.ZRUSENO = ''
              and P.NEVYROBNI = ''
              and P.KONTROLOVANO = ''
              and P.STORNOVANO = ''
              and (R.STREDISKO in ('4','7') or (R.LAKOVNA = '*'))
              and cast (ZP.DATUM_POTREBY as DATE) <= current_date
              and ZP.ZPOZDENI > 0
        ) P
  into :xpocet_zak_vc_nehot_profese_kk, :xpocet_zak_vc_nehot_profese_tk;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 25');
  --Pocty profesi pro zpozdeni dle APS k ZAK/VC (vc. bud.)
  select PPV.POCET_PROF_HOTOVO,PPV.POCET_PROF_KAP_KOOP,PPV.POCET_PROF_TECH_KOOP,
        PPV.POCET_PROF_CERNENI, PPV.POCET_PROF_LAKOVANI, PPV.POCET_PROF_LAKOVANI_KK,
        PPV.POCET_PROF_OBROBNA, PPV.POCET_PROF_ZMETEK, PPV.PRUMER_ZPOZDENI, PPV.MEDIAN_ZPOZDENI
    from ZAK_APS_POCTY_PROFESI (1) PPV
    into :xpocet_prof_hotovo_zak_vc, :xpocet_prof_kap_koop_zak_vc, :xpocet_prof_tech_koop_zak_vc,
        :xpocet_prof_cerneni_zak_vc, :xpocet_prof_lakovani_zak_vc, :xpocet_prof_lak_kk_zak_vc, 
        :xpocet_prof_obrobna_zak_vc, :xpocet_prof_zmetek_zak_vc, :xprumer_zpoz_prof_zak_vc,
        :xmedian_zpoz_prof_zak_vc;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 26');
        
   -- porovnani planu pocet kde je zmena terminu a vykres se zadanou a neodvedenou KK
   -- pocita se pouze ve vsedni dny
  if(extract (weekday from current_date) in (6,0)) then
  begin
   xpocet_planu_zmena_terminu = 0;
  end
  else begin
  select count(*)
    from ZAK_APS_PLAN_VYKRESY PV
    left outer join ZAK_APS_PLAN PL on PV.PKID_PLANU = PL.PKID
    left outer join  ZAK_APS_PLAN_VYKRESY PVO on PVO.PKID_PLANU =  
        (select max(PL.PKID) from ZAK_APS_PLAN PL where cast (PL.DATUM_ULOZENI as date) = (select cast(O_TERMIN_SKUTECNY as date) from GET_TERMIN_SKUTECNY(current_date, 1)))
    and PVO.ZAKAZKA = PV.ZAKAZKA and PVO.VYROBNI_CISLO = PV.VYROBNI_CISLO
    where PV.PKID_PLANU = 
        (select min(PL.PKID) as PKID from ZAK_APS_PLAN PL where cast (PL.DATUM_ULOZENI as date) = (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(current_date, 0)))
    and PVO.OZ_TYP_ZARIZENI is not null and ((PVO.OZ_TERMIN_POZADOVANY <> PV.OZ_TERMIN_POZADOVANY) 
      and exists (select * from ZAK_POSTUPY P 
          join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE and VP.OBJEDNAVKA > 0 
          where P.ZAKAZKA = PV.ZAKAZKA and P.VYROBNI_CISLO = PV.VYROBNI_CISLO and P.KOOPERACE = '*' 
          and P.KOOPERACE_TECHNOLOGICKA = '' 
          and cast (VP.DATUM_ZADANI as date)  < cast(PL.DATUM_ULOZENI as date) and VP.DATUM_ODVEDENI is null))
    into :xpocet_planu_zmena_terminu;
  --execute procedure LOG_DEBUG_MSG ('APS_UKAZATELE_VYPOCET select 27');

  end

  --pocet ZAK/VC kde LPST > OZ_TERMIN_POZADOVANY, select je narocny, trva asi minutu.
  select count(*) from 
    (select V.ZAKAZKA, V.VYROBNI_CISLO, V.TERMIN_LPST from ZAK_VYKRESY V 
    join ZAK_ZAKAZKY_AKTIVNI A on A.ZAKAZKA = V.ZAKAZKA 
    join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    where V.DOKONCENO = '' and (A.VYROBNI_ZAKAZKA = '*' or A.JEDNOUCELOVA_ZAKAZKA = '*') and 
          V.KOD_SKUPINY not in ('GVD','GIH','GIP','RD') and KS.POSTUP > '' and KS.SYSTEMOVY_KOD = '' and KS.MONTOVATELNA_SKUPINA = '') A
    left outer join ZAK_VYRABENY_DIL_ODB_ZAK_SELECT (A.ZAKAZKA, A.VYROBNI_CISLO) S on 1=1        
    where S.OZ_TERMIN_POZADOVANY < A.TERMIN_LPST
  into :xpocet_dilu_lpst_vetsi_oz_term;

  xdatum_dnes = current_date - 1;
  select count(*) from ZAK_ROZPISKY R where R.DATUM_ZMENY_TOL_VCASNOSTI is not null
    into :xpocet_zmen_tol_vcasnosti_rozp;
  select count(*) from ZAK_ROZPISKY R where R.DATUM_ZMENY_TOL_ZPOZDENI is not null
    into :xpocet_zmen_tol_zpozdeni_rozp;


  --smazat mozny radek, ktery by mohl prekazet
  delete from ZAK_APS_UKAZATELE U where U.DATUM = current_date;
  --zaverecny insert 
  insert into ZAK_APS_UKAZATELE (POCET_Z_ZAK, POCET_J_ZAK, POCET_S_ZAK, POCET_O_ZAK, POCET_ZAK, 
    POCET_AUTO_ATO_VC, POCET_FORECAST_VC, 
    POCET_ZPOZDENYCH_ZAK, CELKOVE_ZPOZDENI_ZAK,MAXIMALNI_ZPOZDENI_ZAK, PRUMERNE_ZPOZDENI_ZAK, MEDIAN_ZPOZDENI_ZAK,
    POCET_PREDCASNYCH_ZAK,CELKOVA_PREDCASNOST_ZAK,MAXIMALNI_PREDCASNOST_ZAK,PRUMERNA_PREDCASNOST_ZAK,MEDIAN_PREDCASNOST_ZAK,
    POCET_VCASNYCH_ZAK,
    POCET_NESPLNITELNYCH_ZAK,
    NORMATIV_NESPLNITELNYCH_ZAK,
    POCET_PROFESI_S4,
    POCET_PRETIZENYCH_PROFESI_S4,
    KAPACITA_NEUVOLNENA_S4,
    KAPACITA_UVOLNENA_S4,
    KAPACITA_ZADANA_S4,
    KOOPERACE_NEUVOLNENA_S4,
    KOOPERACE_UVOLNENA_S4,
    KOOPERACE_ZADANA_S4,
    KOOPERACE_OBJEDNANA_S4,
    SUMA_PREVIS_NORMOHODIN,
    SUMA_NEDOSTATKU_NORMOHODIN,
    VCEREJSI_KAPACITA_DLE_KZ,
    VCEREJSI_SKUTECNA_DOCHAZKA,
    SUMA_HODIN_ODVEDENYCH_VCERA,
    POCET_UNIKATNICH_ID,
    POCET_UNIKATNICH_Z_VC,
    POCET_UNIKATNICH_Z_VC_CO,
    POCET_DUPL_ID,
    POCET_DUPL_Z_VC,
    POCET_DUPL_Z_VC_CO,
    POCET_PROHOZENYCH_ID,
    POCET_ZPOZDENYCH_ID,
    POCET_PROHOZENI_ZAK_VC,
    POCET_ZPOZDENYCH_ZAK_VC,
    POCET_UNIK_Z_VC_PRED_KONT,
    POCET_UNIK_Z_VC_ZA_KONT,
    POCET_UNIK_Z_VC_NEDOD_KOOP,
    POCET_UNIK_Z_VC_NEDOD_KOOP_T,
    POCET_UNIK_Z_VC_NEDOD_KOOP_K,
    NORM_UNIK_Z_VC_NEDOD_KOOP_K,
    POCET_ZPOZDENYCH_V_DILU,
    POCET_ZPOZDENYCH_MC,
    POCET_ZADANO_CTECKA,
    POCET_ZADANO_CTECKA_ZAHAJENO,
    POCET_ZADANO_CTECKA_NEZAHAJENO,
    POCET_OPERACI_KK_NEPOPTANE,
    POCET_OPERACI_KK_BEZ_TERMINU,
    POCET_OPERACI_KK_NEODESLANE,
    NORM_OPERACI_KK_NEPOPTANE,
    NORM_OPERACI_KK_BEZ_TERMINU,
    NORM_OPERACI_KK_NEODESLANE,
    POCET_OPERACI_TK_NEPOPTANE,
    POCET_OPERACI_TK_BEZ_TERMINU,
    POCET_OPERACI_TK_NEODESLANE,
    KOOP_T_TERM_DOD_POL,
    KOOP_K_TERM_DOD_POL,
    KOOP_K_TERM_DOD_POL_NH,
    KOOP_T_TDP_POZDE, 
    KOOP_K_TDP_POZDE, 
    KOOP_K_TDP_POZDE_NH, 
    KOOP_T_TDP_VCAS, 
    KOOP_K_TDP_VCAS, 
    KOOP_K_TDP_VCAS_NH, 
    KOOP_T_TDP_NEEX_PO_T, 
    KOOP_K_TDP_NEEX_PO_T, 
    KOOP_K_TDP_NEEX_PO_T_NH,
    POCET_T_KOOP_PO_TERMINU_MP,
    POCET_K_KOOP_PO_TERMINU_MP,
    NORM_K_KOOP_PO_TERMINU_MP,
    POCET_APS_ZPOZDENYCH_ID, 
    POCET_APS_ZPOZDENYCH_ZAK_VC, 
    POCET_APS_ZPOZDENYCH_OPERACI,
    POCET_ZADANYCH_LISTKU, 
    POCET_PLANOVANYCH_NEZADANYCH, 
    POCET_PRIPRAVENYCH_OPERACI,
    POCET_GENEROVANO,
    NORMATIV_GENEROVANO_4,
    NORMATIV_GENEROVANO_7, 
    POCET_GENEROVANO_DO_ZPOZDENI,
    NORMATIV_GENEROVANO_4_DO_ZPOZ,
    NORMATIV_GENEROVANO_7_DO_ZPOZ, 
    POCET_PROF_HOTOVO,            
    POCET_PROF_TECH_KOOP,             
    POCET_PROF_OBROBNA,               
    POCET_PROF_CERNENI,               
    POCET_PROF_LAKOVANI,              
    POCET_PROF_KAP_KOOP,              
    POCET_PROF_ZMETEK,
    POCET_PROF_LAK_KK,
    POCET_PROF_HOTOVO_ZP_VC,            
    POCET_PROF_TECH_KOOP_ZP_VC,             
    POCET_PROF_OBROBNA_ZP_VC,               
    POCET_PROF_CERNENI_ZP_VC,               
    POCET_PROF_LAKOVANI_ZP_VC,              
    POCET_PROF_KAP_KOOP_ZP_VC,              
    POCET_PROF_ZMETEK_ZP_VC,
    POCET_PROF_LAK_KK_ZP_VC,
    REZ_POCET_OP_NEOBJ_PRED_LPST,  
    REZ_POCET_OP_NEOBJ_PRED_T_OBJ, 
    REZ_POCET_OP_NEOBJ_PRED_T_ZAH,
    POCET_ZAK_VC_PRIJEM_KOO_KK1,
    POCET_ZAK_VC_PRIJEM_KOO_TK1,
    POCET_ZAK_VC_PRIJEM_KOO_KK2,
    POCET_ZAK_VC_PRIJEM_KOO_TK2,  
    POCET_ZAK_VC_PRIJEM_KOO_KK3,
    POCET_ZAK_VC_PRIJEM_KOO_TK3,
    POCET_ZAK_VC_PRIJEM_KOO_KK4,
    POCET_ZAK_VC_PRIJEM_KOO_TK4,
    POCET_M_POL_NEHOT_PROFESE_KK,
    POCET_M_POL_NEHOT_PROFESE_TK,
    POCET_ZAK_VC_NEHOT_PROFESE_KK,
    POCET_ZAK_VC_NEHOT_PROFESE_TK,
    POCET_PROF_HOTOVO_ZAK_VC,            
    POCET_PROF_TECH_KOOP_ZAK_VC,             
    POCET_PROF_OBROBNA_ZAK_VC,               
    POCET_PROF_CERNENI_ZAK_VC,               
    POCET_PROF_LAKOVANI_ZAK_VC,              
    POCET_PROF_KAP_KOOP_ZAK_VC,              
    POCET_PROF_ZMETEK_ZAK_VC,
    POCET_PROF_LAK_KK_ZAK_VC,
    POCET_PLANU_ZMENA_TERMINU,
    POCET_DILU_PO_TERM_PRIPRAVY,
    POCET_DILU_PO_TERM_ZAHAJENI,
    PRUMER_ZPOZ_PROF_ZP_VC,
    MEDIAN_ZPOZ_PROF_ZP_VC,
    PRUMER_ZPOZ_PROF_ZAK_VC,
    MEDIAN_ZPOZ_PROF_ZAK_VC,
    POCET_DILU_LPST_VETSI_OZ_TERM,
    POCET_ZMEN_TOL_VCASNOSTI_ROZP,
    POCET_ZMEN_TOL_ZPOZDENI_ROZP
  ) 
    values 
  (:xpocet_z_zak, :xpocet_j_zak, :xpocet_s_zak, :xpocet_o_zak, :xpocet_zak, 
   :xpocet_auto_ato_vc, :xpocet_forecast_vc, 
   :xpocet_zpozdenych_zak, :xcelkove_zpozdeni_zak, :xmaximalni_zpozdeni_zak, :xprumerne_zpozdeni_zak, :xmedian_zpozdeni_zak, 
   :xpocet_predcasnych_zak, :xcelkova_predcasnost_zak,:xmaximalni_predcasnost_zak,:xprumerna_predcasnost_zak,:xmedian_predcasnost_zak,
   :xpocet_vcasnych_zak,
   :xpocet_nesplnitelnych_zak,
   :xnormativ_nesplnitelnych_zak,
   :xpocet_profesi_s4,
   :xpocet_pretizenych_profesi_s4,
   :xkapacita_neuvolnena_s4,
   :xkapacita_uvolnena_s4,
   :xkapacita_zadana_s4,
   :xkooperace_neuvolnena_s4,
   :xkooperace_uvolnena_s4,
   :xkooperace_zadana_s4,
   :xkooperace_objednana_s4,
   :xsuma_previs_normohodin,
   :xsuma_nedostatku_normohodin,
   :xvcerejsi_kapacita_dle_kz,
   :xvcerejsi_skutecna_dochazka,
   :xsuma_hodin_odvedenych_vcera,
   :xpocet_unikatnich_id,
   :xpocet_unikatnich_z_vc,
   :xpocet_unikatnich_z_vc_co,
   :xpocet_dupl_id,
   :xpocet_dupl_z_vc,
   :xpocet_dupl_z_vc_co,
   :xpocet_prohozenych_id,
   :xpocet_zpozdenych_id,
   :xpocet_prohozeni_zak_vc,
   :xpocet_zpozdenych_zak_vc,
   :xpocet_unik_z_vc_pred_kont,
   :xpocet_unik_z_vc_za_kont,
   :xpocet_unik_z_vc_nedod_koop,
   :xpocet_unik_z_vc_nedod_koop_t,
   :xpocet_unik_z_vc_nedod_koop_k,
   :xnorm_unik_z_vc_nedod_koop_k,
   :xpocet_zpozdenych_v_dilu,
   :xpocet_zpozdenych_mc,
   :xpocet_zadano_ctecka,
   :xpocet_zadano_ctecka_zahajeno,
   :xpocet_zadano_ctecka_nezahajeno,
   :xpocet_operaci_kk_nepoptane,
   :xpocet_operaci_kk_bez_terminu,
   :xpocet_operaci_kk_neodeslane,
   :xnorm_operaci_kk_nepoptane, 
   :xnorm_operaci_kk_bez_terminu, 
   :xnorm_operaci_kk_neodeslane,
   :xpocet_operaci_tk_nepoptane,
   :xpocet_operaci_tk_bez_terminu,
   :xpocet_operaci_tk_neodeslane,
   :xkoop_t_term_dod_pol, 
   :xkoop_k_term_dod_pol, 
   :xkoop_k_term_dod_pol_nh,  
   :xkoop_t_tdp_pozde, 
   :xkoop_k_tdp_pozde, 
   :xkoop_k_tdp_pozde_nh, 
   :xkoop_t_tdp_vcas, 
   :xkoop_k_tdp_vcas, 
   :xkoop_k_tdp_vcas_nh,
   :xkoop_t_tdp_neex_po_t, 
   :xkoop_k_tdp_neex_po_t, 
   :xkoop_k_tdp_neex_po_t_nh,
   :xpocet_t_koop_po_terminu_mp, 
   :xpocet_k_koop_po_terminu_mp, 
   :xnorm_k_koop_po_terminu_mp,
   :xpocet_aps_zpozdenych_id, 
   :xpocet_aps_zpozdenych_zak_vc, 
   :xpocet_aps_zpozdenych_operaci,
   :xpocet_zadanych_listku, 
   :xpocet_planovanych_nezadanych, 
   :xpocet_pripravenych_operaci,
   :xpocet_generovanych_dilu, 
   :xnh_gen_dilu_4, 
   :xnh_gen_dilu_7,   
   :xpocet_generovanych_do_zpoz, 
   :xnh_gen_dilu_4_do_zpoz, 
   :xnh_gen_dilu_7_do_zpoz,
   :xpocet_prof_hotovo,            
   :xpocet_prof_tech_koop,             
   :xpocet_prof_obrobna,               
   :xpocet_prof_cerneni,               
   :xpocet_prof_lakovani,              
   :xpocet_prof_kap_koop,              
   :xpocet_prof_zmetek,
   :xpocet_prof_lak_kk,
   :xpocet_prof_hotovo_zp_vc,            
   :xpocet_prof_tech_koop_zp_vc,             
   :xpocet_prof_obrobna_zp_vc,               
   :xpocet_prof_cerneni_zp_vc,               
   :xpocet_prof_lakovani_zp_vc,              
   :xpocet_prof_kap_koop_zp_vc,              
   :xpocet_prof_zmetek_zp_vc,
   :xpocet_prof_lak_kk_zp_vc,
   :xrez_pocet_op_neobj_pred_lpst,
   :xrez_pocet_op_neobj_pred_t_obj,
   :xrez_pocet_op_neobj_pred_t_zah,
   :xpocet_zak_vc_prijem_koo_kk1,
   :xpocet_zak_vc_prijem_koo_tk1,
   :xpocet_zak_vc_prijem_koo_kk2,
   :xpocet_zak_vc_prijem_koo_tk2,
   :xpocet_zak_vc_prijem_koo_kk3,
   :xpocet_zak_vc_prijem_koo_tk3,
   :xpocet_zak_vc_prijem_koo_kk4,
   :xpocet_zak_vc_prijem_koo_tk4,
   :xpocet_m_pol_nehot_profese_kk,
   :xpocet_m_pol_nehot_profese_tk,
   :xpocet_zak_vc_nehot_profese_kk,
   :xpocet_zak_vc_nehot_profese_tk,
   :xpocet_prof_hotovo_zak_vc,
   :xpocet_prof_tech_koop_zak_vc,   
   :xpocet_prof_obrobna_zak_vc,
   :xpocet_prof_cerneni_zak_vc,
   :xpocet_prof_lakovani_zak_vc,
   :xpocet_prof_kap_koop_zak_vc,
   :xpocet_prof_zmetek_zak_vc,
   :xpocet_prof_lak_kk_zak_vc,
   :xpocet_planu_zmena_terminu,
   :xpocet_dilu_po_term_pripravy, 
   :xpocet_dilu_po_term_zahajeni,
   :xprumer_zpoz_prof_zp_vc,
   :xmedian_zpoz_prof_zp_vc, 
   :xprumer_zpoz_prof_zak_vc,
   :xmedian_zpoz_prof_zak_vc,
   :xpocet_dilu_lpst_vetsi_oz_term,
   :xpocet_zmen_tol_vcasnosti_rozp,
   :xpocet_zmen_tol_zpozdeni_rozp
  );     
end
^


create procedure DATUM_GET_Y_M_D_W as begin exit; end^
alter procedure DATUM_GET_Y_M_D_W
  (i_od timestamp, i_do timestamp)
returns
  (o_datum timestamp,
   o_rok integer, o_mesic integer,
   o_den integer, o_tyden integer)
as
begin
  while (i_od <= i_do) do
  begin
    o_datum = i_od;
    o_rok = extract(YEAR from i_od);
    o_mesic = extract(MONTH from i_od);
    o_den = extract(DAY from i_od);
    o_tyden = extract (WEEK from i_od);
    suspend;
    i_od = i_od + 1;
  end
end
^


create procedure DATUM2TYDENNI_OBDOBI as begin exit; end^

alter procedure DATUM2TYDENNI_OBDOBI (i_datum DATUM)
returns (tydenni_obdobi varchar(8), rok integer, tyden integer)
as
  declare xmesic POCET;
begin
  if (:i_datum is null) then
  begin
    tyden = null;
    suspend;
    exit;
  end
  
  tyden = extract (WEEK from :i_datum);
  rok = extract (YEAR from :i_datum);
  xmesic = extract (MONTH from :i_datum);
  
  --vyjimka je pokud je posledni tyden v roce nebo prvni tyden v roce
  if ((:xmesic = 12) and (:tyden = 1)) then
  begin
    rok = :rok + 1;
  end else if ((:xmesic = 1) and (:tyden > 50)) then
  begin
    rok = :rok - 1;
  end
  
  tydenni_obdobi = :rok || '/' || iif (:tyden < 10, ('0' || :tyden), :tyden);
  suspend;
end
^


create or alter procedure I2FP_EXPORT_DAT_TPV
as
  declare xsupplierid                      varchar(40);
  declare xitem                            varchar(40);
  declare xnazev_materialu                 NAZEV_MATERIALU;
  declare xpocet                           integer;
  declare xdatum                           timestamp;
  declare xnormativ_rezarna                numeric (15,3); --normativ operace rezarny v hodinach
  declare xsimulace                        varchar(1);
begin
  /************************************************************************************************************/
  /***
    Tyto tabulky slouzi k naplneni prostredi TEST, ktere obsahuje (narozdil od PROD) i TPV data.
    Toto je z duvodu moznosti simulaci.

    I2_BOMHEADER2
    I2_BOMCOMPONENTS2
    I2_ROUTINGHEADER2
    I2_ROUTINGOPERATION2
    I2_ITEMBOMROUTING2
    I2_OPRESOURCE2
  ***/
  /************************************************************************************************************/

  /************************************************************************************************************/
  /**   BOMHEADER   *******************************************************************************************/
  /************************************************************************************************************/
  execute procedure LOG_DEBUG_MSG ('I2FP TPV BOMHEADER start ?',cast ('NOW' as time));
  delete from I2_BOMHEADER;
  delete from I2_BOMHEADER2;
  insert into I2_BOMHEADER2 (BOMID, QTYPRODUCED)
    select (select ITEMID from I2_GET_ID_V(ID_VYRABENY_DIL, SERIE)), 1
      from (select distinct V.ID_VYRABENY_DIL, coalesce(P.SERIE, 0) SERIE
              from ZAR_VYKRESY V
              left outer join ZAR_POSTUPY P on P.VYKRES = V.VYKRES
              where exists(select * from ZAR_ROZPISKY R where R.VYKRES_PODSKUPINY = V.VYKRES or R.VYKRES = V.VYKRES));


  /************************************************************************************************************/
  /**   BOMCOMPONENTS   ***************************************************************************************/
  /************************************************************************************************************/
  execute procedure LOG_DEBUG_MSG ('I2FP TPV BOMCOMPONENTS start ?',cast ('NOW' as time));
  delete from I2_BOMCOMPONENTS;
  delete from I2_BOMCOMPONENTS2;
  execute procedure LOG_DEBUG_MSG ('I2FP TPV BOMCOMPONENTS insert 1 ?',cast ('NOW' as time));
  -- 1. Musim osetrit jednicove dily, ktere nemaji postup nebo do kterych nevstupuje material (stare rozpisky, kompletni vyroba v kooperaci, ...).
  insert into I2_BOMCOMPONENTS2 (BOMID, ITEM, QTYPER, ROUTINGID, OPERATIONSEQ)
    select (select ITEMID from I2_GET_ID_V(ID_VYRABENY_DIL, SERIE)),
           (select ITEMID from I2_GET_ID_N(0)),
           1, '', 0
      from (select (select first 1 VD.ID_VYRABENY_DIL from ZAR_VYKRESY VD where VD.VYKRES = V.VYKRES) ID_VYRABENY_DIL, V.SERIE
              from (select distinct R.VYKRES, coalesce(P.SERIE, 0) SERIE
                      from (select distinct R.VYKRES
                              from ZAR_ROZPISKY R
                              where R.VYKRES > '' and 
                                    not exists(select * from ZAR_ROZPISKY R2 where R2.VYKRES_PODSKUPINY = R.VYKRES and R2.ZRUSENO = '')) R
                      left outer join ZAR_POSTUPY P on P.VYKRES = R.VYKRES) V
              left outer join ZAR_MATERIALY M on M.VYKRES = V.VYKRES and M.SERIE = V.SERIE
              where M.VYKRES is null);
  execute procedure LOG_DEBUG_MSG ('I2FP TPV BOMCOMPONENTS insert 2 ?',cast ('NOW' as time));
  -- 2. Do kusovniku vstupuje material, ze ktereho je dil vyroben.
  insert into I2_BOMCOMPONENTS2 (BOMID, ITEM, QTYPER, ROUTINGID, OPERATIONSEQ)
    select A.BOMID, A.ITEM, sum (A.QTYPER) QTYPER, min (ROUTINGID) as ROUTINGID, min (OPERATIONSEQ) as OPERATIONSEQ from 
    (select (select ITEMID from I2_GET_ID_V(ID_VYRABENY_DIL, SERIE)) as BOMID,
               (select ITEMID from I2_GET_ID_N(iif(MNOZSTVI_MATERIALU = 0, 0, ID_NAKUPOVANY_DIL))) as ITEM,
               iif(MNOZSTVI_MATERIALU = 0, 1, MNOZSTVI_MATERIALU) QTYPER, ''  ROUTINGID, 0 as OPERATIONSEQ
          from (select V.ID_VYRABENY_DIL, V.SERIE, MAT.ID_NAKUPOVANY_DIL, sum(M.MNOZSTVI_MATERIALU) MNOZSTVI_MATERIALU
                  from (select distinct V.VYKRES, V.ID_VYRABENY_DIL, P.SERIE
                          from ZAR_VYKRESY V
                          left outer join ZAR_POSTUPY P on P.VYKRES = V.VYKRES
                          where exists(select * from ZAR_ROZPISKY R where R.VYKRES = V.VYKRES)) V, ZAR_MATERIALY M, MTZ_MATERIAL MAT
                  where M.VYKRES = V.VYKRES and M.SERIE = V.SERIE and MAT.NAZEV_MATERIALU = M.NAZEV_MATERIALU and
                        --M.MNOZSTVI_MATERIALU > 0 and
                        -- Podminka pro cast exportu nize, kde se pripocitava k rozpiskovemu materialu mnozstvi stejneho materialu z operace (zcela vyjimecny pripad) - jinak by tam byl dany material dvakrat.
                        not exists (select * from ZAR_ROZPISKY R where R.VYKRES_PODSKUPINY = V.VYKRES and R.NAZEV_MATERIALU = M.NAZEV_MATERIALU)
                  group by V.ID_VYRABENY_DIL, V.SERIE, MAT.ID_NAKUPOVANY_DIL)) A
    group by A.BOMID, A.ITEM;  
  execute procedure LOG_DEBUG_MSG ('I2FP TPV BOMCOMPONENTS insert 3 ?',cast ('NOW' as time));
  -- 3. exportuji polozky kusovniku (vyrabene a nakupovane dily) ...
  insert into I2_BOMCOMPONENTS2 (BOMID, ITEM, QTYPER, ROUTINGID, OPERATIONSEQ)
    select (select ITEMID from I2_GET_ID_V(R.ID_BOM, R.SERIE_BOM)),
           iif(R.VYKRES = '',
               (select ITEMID from I2_GET_ID_N(R.ID_NAKUPOVANY_DIL)),
               (select ITEMID from I2_GET_ID_V(R.ID_VYRABENY_DIL, 0))
           ),
           -- Cast "+ iif..." viz. vyse v bodu 2.
           R.MNOZSTVI + iif(R.VYKRES > '', 0, coalesce((select sum(M.MNOZSTVI_MATERIALU) from ZAR_MATERIALY M where M.VYKRES = R.VYKRES_PODSKUPINY and M.SERIE = R.SERIE_BOM and M.NAZEV_MATERIALU = R.NAZEV_MATERIALU), 0)),
           '', 0
    from (select distinct R.VYKRES_PODSKUPINY, 
                 (select first 1 V.ID_VYRABENY_DIL from ZAR_VYKRESY V where V.VYKRES = R.VYKRES_PODSKUPINY) ID_BOM, coalesce(P.SERIE, 0) SERIE_BOM,
                 R.VYKRES, R.SERIE, R.NAZEV_MATERIALU,
                 R.ID_VYRABENY_DIL, R.ID_NAKUPOVANY_DIL, R.MNOZSTVI
            from (select R.VYKRES_PODSKUPINY, R.VYKRES, R.NAZEV_MATERIALU,
                        (select first 1 V.ID_VYRABENY_DIL from ZAR_VYKRESY V where V.VYKRES = R.VYKRES) ID_VYRABENY_DIL,
                        (select M.ID_NAKUPOVANY_DIL from MTZ_MATERIAL M where M.NAZEV_MATERIALU = R.NAZEV_MATERIALU) ID_NAKUPOVANY_DIL,
                        sum(R.MNOZSTVI) MNOZSTVI,
                        coalesce((select max(P.SERIE) from ZAR_POSTUPY P where P.VYKRES = R.VYKRES and P.SERIE <= sum(R.MNOZSTVI)), 0) SERIE
                    from ZAR_ROZPISKY R
                    where R.VYKRES_PODSKUPINY > '' and R.ZRUSENO = '' and R.MNOZSTVI > 0
                    group by R.VYKRES_PODSKUPINY, R.VYKRES, R.NAZEV_MATERIALU) R
            left outer join ZAR_POSTUPY P on P.VYKRES = R.VYKRES_PODSKUPINY
            where exists(select * from ZAR_VYKRESY V where V.VYKRES = R.VYKRES_PODSKUPINY)) R;


  /************************************************************************************************************/
  /**   ROUTINGHEADER   ***************************************************************************************/
  /************************************************************************************************************/
  execute procedure LOG_DEBUG_MSG ('I2FP TPV ROUTINGHEADER start ?',cast ('NOW' as time));
  delete from I2_ROUTINGHEADER;
  delete from I2_ROUTINGHEADER2;
  insert into I2_ROUTINGHEADER2 (ROUTINGID)
    select (select ITEMID from I2_GET_ID_V(ID_VYRABENY_DIL, SERIE))
      from (select distinct V.ID_VYRABENY_DIL, coalesce(P.SERIE, 0) SERIE
              from ZAR_VYKRESY V
              left outer join ZAR_POSTUPY P on P.VYKRES = V.VYKRES);


  /************************************************************************************************************/
  /**   ROUTINGOPERATION   ************************************************************************************/
  /************************************************************************************************************/
  execute procedure LOG_DEBUG_MSG ('I2FP TPV ROUTINGOPERATION start ?',cast ('NOW' as time));
  --nastavim parametr normativ rezarny
  xnormativ_rezarna = 0.083;--0.083 = 5 minut
  select cast (KEY_VALUE as numeric(15,3)) as NORMATIV_REZARNA 
    from SYS_SETTINGS 
    where KEY_NAME = 'APS_PARAMS_NORMATIV_REZARNA'
  into :xnormativ_rezarna;

  delete from I2_ROUTINGOPERATION;
  delete from I2_ROUTINGOPERATION2;
  --20101202 Kvuli teminovani dle etapy 70 ma montaz nulove casy.
  insert into I2_ROUTINGOPERATION2 (ROUTINGID, OPERATIONSEQ, OPERATION, RUNTIMEPER, INTSETUPTIME, TEARDOWNTIME, TIMEUOM)
     select (select ITEMID from I2_GET_ID_V(ID_VYRABENY_DIL, SERIE)), 
            CISLO_OPERACE, POPIS, NORMATIV_PRACE, NORMATIV_PRIPRAVA, MEZIOPERACE, 'HOURS'
       from (select distinct V.ID_VYRABENY_DIL, coalesce(P.SERIE, 0) SERIE, coalesce(P.CISLO_OPERACE, 0) CISLO_OPERACE, PROF.PROFESE,
                   substring(coalesce(P.POPIS, 'Dil nema operace !!!') from 1 for 40) POPIS,
                   iif(coalesce(KS.MONTOVATELNA_SKUPINA, '') = '' and PROF.APS_NEPLANOVAT = '', coalesce(P.NORMATIV_PRIPRAVA, 0), 0) NORMATIV_PRIPRAVA,
                   (iif(coalesce(KS.MONTOVATELNA_SKUPINA, '') = '' and PROF.APS_NEPLANOVAT = '', iif (PROF.REZARNA = '',coalesce(P.NORMATIV_PRACE, 0), :xnormativ_rezarna), 0) 
                   / iif (P.DAVKA_ZPRACOVANI <= 1,1, P.DAVKA_ZPRACOVANI)) NORMATIV_PRACE,
                   iif(coalesce(KS.MONTOVATELNA_SKUPINA, '') = '' and PROF.APS_NEPLANOVAT = '', iif (P.PROFESE <> coalesce(NP.PROFESE,''),coalesce(PROF.MEZIOPERACE, 0),0), 0) * 24 MEZIOPERACE
              from ZAR_VYKRESY V
              left outer join ZAR_POSTUPY P on P.VYKRES = V.VYKRES
              left outer join ZAR_POSTUPY NP on NP.VYKRES = P.VYKRES and NP.SERIE = P.SERIE and NP.CISLO_OPERACE > P.CISLO_OPERACE 
                                                and NP.CISLO_OPERACE = (select min(PP.CISLO_OPERACE) from ZAR_POSTUPY PP 
                                                                      where PP.VYKRES = P.VYKRES and PP.SERIE = P.SERIE and PP.CISLO_OPERACE > P.CISLO_OPERACE)
                                                
              left outer join ZAK_PROFESE PROF on PROF.PROFESE = P.PROFESE
              left outer join ZAR_KODY_SKUPIN KS on KS.KOD = (select first 1 V2.KOD_SKUPINY from ZAR_VYKRESY V2 where V2.VYKRES = V.VYKRES order by V2.ZRUSENO, V2.VERZE desc));


  /************************************************************************************************************/
  /**   ITEMBOMROUTING   **************************************************************************************/
  /************************************************************************************************************/
  delete from I2_ITEMBOMROUTING;
  delete from I2_ITEMBOMROUTING2;
  insert into I2_ITEMBOMROUTING2 (BOMID, ROUTINGID, ITEM, USABLEBYNEWMFGORD)
    select (select ITEMID from I2_GET_ID_V(ID_VYRABENY_DIL, SERIE)),
           (select ITEMID from I2_GET_ID_V(ID_VYRABENY_DIL, SERIE)),
           (select ITEMID from I2_GET_ID_V(ID_VYRABENY_DIL, 0)),
           1
      from (select distinct V.ID_VYRABENY_DIL, coalesce(P.SERIE, 0) SERIE
              from ZAR_VYKRESY V
              left outer join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
              left outer join ZAR_POSTUPY P on P.VYKRES = V.VYKRES
              where (coalesce(KS.SYSTEMOVY_KOD, '') = '' or coalesce(KS.ZARIZENI, '*') = '*') and 
                    exists(select * from ZAR_ROZPISKY R where R.VYKRES_PODSKUPINY = V.VYKRES or R.VYKRES = V.VYKRES));


  /************************************************************************************************************/
  /**   OPRESOURCE   ******************************************************************************************/
  /************************************************************************************************************/
  delete from I2_OPRESOURCE;
  delete from I2_OPRESOURCE2;
  --KN20151026 Nemuzu pouzit CURRENT_DATE, protoze to ve svatcich a o vikendu priradi spatne profese (protoze je v techto dnech denni fond a pocet pracovniku roven nule).
  -- Naleznu nasledujici pracovni den.
  xdatum = CURRENT_DATE;
  while (exists(select * from ZAK_SVATKY where DATUM = :xdatum)) do
  begin
    xdatum = xdatum + 1;
  end
  insert into I2_OPRESOURCE2 (ROUTINGID, OPERATIONSEQ, WORKCENTERNAME, RESOURCENAME)
    select (select ITEMID from I2_GET_ID_V(O.ID_VYRABENY_DIL, O.SERIE)),
           O.CISLO_OPERACE, 'Soma',
           coalesce((with recursive PROFESE as (
             select P.PROFESE, 99999 PRIORITA, 0 as KOREN
               from ZAR_POSTUPY P
               where P.VYKRES = O.VYKRES and P.SERIE = O.SERIE and P.CISLO_OPERACE = O.CISLO_OPERACE
             union all
             select A.ALTERNATIVA PROFESE, A.PRIORITA, 1 as KOREN
               from PROFESE PROFESE
               join ZAK_PROFESE_ALTERNATIVY A on A.PROFESE = PROFESE.PROFESE
               where PROFESE.KOREN = 0
               order by A.PRIORITA desc)
           select first 1 P.PROFESE
             from PROFESE P
             left outer join KAP_PROFESE_DENNI_FOND (coalesce(P.PROFESE, ''), :xdatum) DF on 1=1
             left outer join (select KAP.PROFESE, min(KAP.DATUM) NASLEDUJICI_KAPACITA 
                                from ZAK_PROFESE_KAPACITY KAP 
                                where KAP.DATUM > :xdatum and KAP.VYRAZENI = '' and KAP.DENNI_FOND > 0 group by KAP.PROFESE) KAP on KAP.PROFESE = P.PROFESE
             where not (DF.POCET_PRACOVNIKU >= 0 and DF.DENNI_FOND = 0 and (KAP.NASLEDUJICI_KAPACITA is null or CURRENT_DATE + 14 < KAP.NASLEDUJICI_KAPACITA))
             order by P.PRIORITA desc), O.PROFESE)
      from (select distinct V.ID_VYRABENY_DIL, V.VYKRES, coalesce(P.SERIE, 0) SERIE, coalesce(P.CISLO_OPERACE, 0) CISLO_OPERACE, coalesce(P.PROFESE, '0001') PROFESE
              from ZAR_VYKRESY V
              left outer join ZAR_POSTUPY P on P.VYKRES = V.VYKRES) O;

  --Korekce dat pro simulace MP!!!!
  select CONTEXT from SYS_GET_CONTEXT ('APS_SIMULACE')  
    into :xsimulace;
  execute procedure LOG_DEBUG_MSG ('TPV I2FP priznak simulace je roven ''?'' ?',:xsimulace, cast ('NOW' as time));
  if (:xsimulace = '*') then
  begin
    execute procedure I2FP_UPRAV_TPV_DATA_PRO_STROJ ('O10/33');
    execute procedure I2FP_UPRAV_TPV_DATA_PRO_STROJ ('O20/05');
  end  
end
^
/*************************************************************************************************************/


/* PROC: I2FP_EXPORT_DAT_VYROBA_EXP_DIL */
create or alter procedure I2FP_EXPORT_DAT_VYROBA_EXP_DIL
  (i_zakazka integer, i_vyrobni_cislo integer, i_vetev smallint,
   i_vykres varchar(25), i_id_vyrabeny_dil integer,
   i_prubezna_doba smallint, i_charakter_skupiny varchar(1),
   i_zalozit_vz varchar(1), i_vyrobni_zakazka varchar(1),
   i_mnozstvi numeric(15, 3), i_mnozstvi_dokonceno numeric(15, 3),
   i_pomer double precision, i_normativ_rezarna numeric(15,3),
   i_mnozstvi_pred_kompl_na_sklad numeric(15,3)
  )
as
  declare xpocet                          integer;
  declare xbomid                          varchar(40);
  declare xitemid                         varchar(40);
  declare xmontazni_cislo                 integer;
  declare xpredstih                       smallint;
  declare xpocet_operaci                  smallint;
  declare xvhodnost_kooperace             varchar(1);
  declare xpozadavek_mtz_zrusen           varchar(1);
  declare xcislo_operace                  smallint;
  declare xpopis                          varchar(40);
  declare xnormativ_priprava              numeric(15, 3);
  declare xnormativ_prace                 numeric(15, 3);
  declare xnormativ_priprava_nezkraceny   numeric(15, 3);
  declare xnormativ_prace_nezkraceny      numeric(15, 3);
  declare xodvedeny_cas                   numeric(15, 3);
  declare xnormativ_predchozich_op        numeric(15, 3);
  declare xn                              numeric(15, 3);
  declare xvychozi_operace                varchar(1);
  declare xpripraveno                     varchar(1);
  declare xzadano                         varchar(1); 
  declare xdokonceno                      varchar(1);
  declare xodvedeno_vse                   varchar(1);
  declare xdatum_dokonceni                timestamp;
  declare xnasledujici_operace            smallint;
  declare xposledni_operace               smallint;
  declare xkooperace                      varchar(1);
  declare xmezioperace                    smallint;
  declare xmezioperace_skutecna           integer;
  declare xspojit_operace                 smallint;
  --declare xpooperacni_doba        smallint;
  declare xpredstih_skutecny              smallint;
  declare xobjednavka                     integer;
  declare xobjednavka_predchozi           integer;
  declare xtermin_pozadavku               timestamp;
  declare xdil_dokoncen                   varchar(1);
  declare xid_dilu                        varchar(40);
  declare xprofese_puvodni                varchar(5);
  declare xprofese                        varchar(5);
  declare xrezarna                        varchar(1);
  declare xdavkovy_zdroj                  varchar(1);
  declare xnepouzivat_linkdownstream      varchar(1);
  declare xbatchtype                      varchar(40);
  declare xmontovatelna_skupina           varchar(1);
  declare xodbytova_zakazka               varchar(1);
  declare xjednoucelova_zakazka           varchar(1);
  declare xaps_montaz_dokonceni           varchar(1);
  declare xitem_koop                      varchar(40);
  declare xmnozstvi_koop                  numeric(15, 3);
  declare xdatum                          timestamp;
  declare xzruseno                        varchar(1);
  declare xmnozstvi_operace               numeric(15, 3);
  declare xmira_rozpracovanosti           numeric(15,4);
 
  declare xtermin_pozadovany              date; 
  declare xtermin_dily_pro_montaz         date;
  declare xitem                           varchar(40);
  declare xitems                          textmemo;
  declare xitems_termin_pozadovany        date;
  declare xlast_termin_pozadovany         date;
  declare xcas                            time;

  declare xprodordholdenddate             timestamp;
  declare xmnozstvi_vc_predano_na_sklad   numeric(15,3);
  declare xpozadavky_bez_vazeb            varchar(1);   
begin

  select CONTEXT from SYS_GET_CONTEXT ('APS_POZADAVKY_BEZ_VAZEB')  
    into :xpozadavky_bez_vazeb;
  xcas = cast ('NOW' as time);
  select coalesce(max(D.POZADAVEK_MTZ_MS_ZRUSEN), '')
    from ODB_DODAVKY D
    where D.ZAKAZKA = :i_zakazka and D.DOKONCENO = '' and D.TERMIN_DOKONCENI is not null
    into :xpozadavek_mtz_zrusen;
--  execute procedure LOG_DEBUG_MSG ('I2FP_EXPORT_DAT_VYROBA_EXP_DIL 1/2 i_zakazka ?, i_vyrobni_cislo ?, i_vetev ?, i_vykres ?, i_id_vyrabeny_dil ?, i_prubezna_doba ?, i_charakter_skupiny ?, i_zalozit_vz ?, i_vyrobni_zakazka ?', :i_zakazka, :i_vyrobni_cislo, :i_vetev, :i_vykres, :i_id_vyrabeny_dil,:i_prubezna_doba , :i_charakter_skupiny,:i_zalozit_vz, :i_vyrobni_zakazka);
--  execute procedure LOG_DEBUG_MSG ('I2FP_EXPORT_DAT_VYROBA_EXP_DIL 2/2 i_mnozstvi ?, i_mnozstvi_dokonceno ?,i_pomer ?, i_normativ_rezarna ?, pbv @?@',:i_mnozstvi, :i_mnozstvi_dokonceno, :i_pomer, :i_normativ_rezarna, :xpozadavky_bez_vazeb);
  if (abs(cast (:i_pomer  as CENA) - cast (:i_pomer as int)) > 0) then
  begin
--    execute procedure LOG_DEBUG_MSG ('I2FP_EXPORT_DAT_VYROBA_EXP_DIL i_zakazka ?, i_vyrobni_cislo ?, i_vetev ?, i_pomer ?', :i_zakazka, :i_vyrobni_cislo, :i_vetev, :i_pomer);
  end

  if (i_pomer <= 0) then
  begin
    exception BP 'pomer = ' || i_pomer || ' (' || i_zakazka || '/' || i_vyrobni_cislo || '  Mn: ' || i_mnozstvi || '; MD:' || i_mnozstvi_dokonceno;
  end
  
  select first 1 R.MONTAZNI_CISLO, R.PREDSTIH, coalesce(KS.MONTOVATELNA_SKUPINA, '') as MONTOVATELNA_SKUPINA
    from ZAK_ROZPISKY R, ZAR_KODY_SKUPIN KS
    where R.ZAKAZKA = :i_zakazka and R.VYROBNI_CISLO = :i_vyrobni_cislo and
          KS.KOD = R.KOD_SKUPINY
    order by R.ODEVZDANO desc, R.STORNOVANO
    into :xmontazni_cislo, :xpredstih, :xmontovatelna_skupina;
  
  xbomid = i_zakazka || '/' || i_vyrobni_cislo || '%' || iif(i_vetev > 0, '-' || i_vetev, '');
  select ITEMID from I2_GET_ID_V(:i_id_vyrabeny_dil, 0)
    into :xitemid;
--  execute procedure LOG_DEBUG_MSG ('EXP_DIL START ? at ?',:xbomid, cast ('NOW' as time));

  select coalesce (ZD.ZALOZIT_VYROBNI_ZAKAZKU, '') as ODBYTOVA_ZAKAZKA, 
         coalesce (iif ((ZD.ZALOZIT_VYROBNI_ZAKAZKU = '' and ZD.HODINY_ADRESNE = '*'),'*',''),'') as JEDNOUCELOVA_ZAKAZKA
    from ZAK_ZAKAZKY Z
    join  ZAK_ZAKAZKY_DRUHY ZD on ZD.DRUH_ZAKAZKY = Z.DRUH_ZAKAZKY
    where Z.ZAKAZKA = :i_zakazka 
    into :xodbytova_zakazka, xjednoucelova_zakazka;
  
  if (:xodbytova_zakazka = '*' or :xjednoucelova_zakazka = '*') then
  begin
    xaps_montaz_dokonceni = '*';
  end

  if (xjednoucelova_zakazka = '*') then
  begin
    xodbytova_zakazka = xmontovatelna_skupina;
  end
  
  --v jednourovnovem kusovniku OZ, nepotrebuji dopocitavat umele operace
  if (xpozadavky_bez_vazeb = '*') then
  begin
    xaps_montaz_dokonceni = '';
  end

  --pokud je zakazka oznacena jako planovat dle MP, tak zjistim, jestli uz jsem na podskupine, kde to musim pocitat presne
  if (xaps_montaz_dokonceni = '*') then
  begin
    select  first 1 coalesce(min('*'),'') as PLANOVAT_POZADAVKY from ZAK_ROZPISKY R 
      join ZAR_KODY_SKUPIN RKS on RKS.KOD = R.KOD_SKUPINY
      join ZAK_ROZPISKY RD on RD.ZAKAZKA = R.ZAKAZKA and RD.MONTAZNI_CISLO_RODIC = R.MONTAZNI_CISLO
      join ZAR_KODY_SKUPIN RDKS on RDKS.KOD = RD.KOD_SKUPINY
      where R.ZAKAZKA = :i_zakazka and R.VYROBNI_CISLO = :i_vyrobni_cislo and 
            R.ODEVZDANO = '*' and RD.ODEVZDANO = '*' and 
            (RD.VYROBNI_CISLO = 0 or 
             (:xjednoucelova_zakazka = '' and RD.POZADAVEK_VD = '*') or 
             (:xjednoucelova_zakazka = '*' and RKS.MONTOVATELNA_SKUPINA = '*' and RD.VYKRES > '' and 
              RDKS.MONTOVATELNA_SKUPINA = '' and RDKS.ZARIZENI = '')
            ) 
    into :xaps_montaz_dokonceni;
    --pokud uz musim pocitat presne, tak jeste zkontroluju jestli nevisim v podstromu I dilu
    if (xaps_montaz_dokonceni = '*') then
    begin
      --I dily neplanuju podle montaze
      if (exists (select SR.*
        from ZAK_STRUKTURA_ROZPISKY_UP (:i_zakazka,:xmontazni_cislo) SR
        where SR.O_KOD_SKUPINY in ('GIH', 'GIP'))) then 
      begin
        xaps_montaz_dokonceni = '';
      end
    end
  end

  if (i_mnozstvi = 0) then
  begin
    i_prubezna_doba = 0;
  end else begin
    i_prubezna_doba = i_prubezna_doba / i_mnozstvi;
  end
  
  --KN20100826 Vyrabene dily nemaji prubeznou dobu ani predstih.
  if (i_vyrobni_zakazka = '*') then
  begin
    i_prubezna_doba = 0;
    xpredstih = 0;
  end
    
  -- SFCHeader
  select RATIO from I2FP_MIRA_ROZPRACOVANOSTI_VZ (:i_zakazka, :i_vyrobni_cislo, :i_vetev)
    into :xmira_rozpracovanosti;

  --pozdrzeni vyroby od
  xprodordholdenddate = null;
  if (xjednoucelova_zakazka = '*') then
  begin
    if (xodbytova_zakazka = '') then
    begin
      select Z.APS_ZAHAJIT_NEJDRIVE_OD from ZAK_ZAKAZKY Z where Z.ZAKAZKA = :i_zakazka 
      into :xprodordholdenddate;
    end
  end else 
  begin
    select min (Z.APS_ZAHAJIT_NEJDRIVE_OD) from ZAK_ROZPISKY R 
      left outer join ZAK_STRUKTURA_ROZPISKY_UP (R.ZAKAZKA, R.MONTAZNI_CISLO) SU on 1=1
      left outer join ZAK_ROZPISKY RD on RD.ZAKAZKA = SU.O_ZAKAZKA and RD.MONTAZNI_CISLO = SU.O_MONTAZNI_CISLO
      left outer join ZAK_ROZPISKY RR on RR.ZAKAZKA = SU.O_ZAKAZKA and RR.MONTAZNI_CISLO = SU.O_MONTAZNI_CISLO_RODIC
      left outer join ZAR_KODY_SKUPIN RRKS on RRKS.KOD = RR.KOD_SKUPINY
      left outer join ZAK_ZAKAZKY Z  on Z.ZAKAZKA = RD.ZAKAZKA_RODIC_VD
    where 
      R.ZAKAZKA = :i_zakazka and R.VYROBNI_CISLO  = :i_vyrobni_cislo  and 
      (RRKS.SYSTEM_VYRABENE_DILY = '*' or RRKS.SYSTEM_UPRAVA_DILU = '*' or RRKS.KOD in ('GIH','GIP')) and  
      RD.ZAKAZKA_RODIC_VD > 0 and RD.MONTAZNI_CISLO_RODIC_VD > 0
    into :xprodordholdenddate;
  end

  -- Pokud uzel obsahuje nedokoncene pouze neodevzdane podskupiny, tak je dokonceny.
  -- Vyrabene dily se berou jako nedokoncene i kdyz jsou neodevzdane. Muze to zvednout AUTO_ATO zakazky (pozadavek dokoncen, ale dil se vyrabi).
  xdil_dokoncen = '';
  insert into I2_SFCHEADER (PRODUCTIONORDERID, ITEM, QTYORDERED, QTYCOMPLETED, ROUTINGID, BOMID, ISFEEDSLOCKED, PRIORITY, PRODORDHOLDENDDATE, UD_MONTAZNI_PRIKAZY)
    values (:xbomid, :xitemid, :i_mnozstvi, iif(:xdil_dokoncen = '*', :i_mnozstvi, :i_mnozstvi_dokonceno),
            :xbomid, :xbomid, iif(:i_zalozit_vz = '' and :i_vyrobni_zakazka = '', 1, 0), :xmira_rozpracovanosti, :xprodordholdenddate,
            left((select list (distinct MPP.MONTAZNI_PRIKAZ, ',')
               from ZAK_ROZPISKY R, ZAK_MONT_PRIK_POLOZKY MPP
               where R.ZAKAZKA = :i_zakazka and R.VYROBNI_CISLO = :i_vyrobni_cislo and 
                     R.ZAKAZKA = MPP.ZAKAZKA and R.MONTAZNI_CISLO = MPP.MONTAZNI_CISLO), 40));
  
  -- Kusovnik.
  -- Hlavicka kusovniku.
  insert into I2_BOMHEADER (BOMID, QTYPRODUCED, SOMA_VYROBNI_DATA)
    values (:xbomid, 1, '*');

--  execute procedure LOG_DEBUG_MSG ('pred exportem BOM');
  -- vlozim polozky kusovniku do BOMCOMPONENTS a SFCCOMPONENTS (vykresy a materialy)
  execute procedure I2FP_EXPORT_DAT_VYR_EXP_DIL_BOM (i_zakazka, i_vyrobni_cislo, i_vetev, i_pomer, xmontazni_cislo, xbomid, xpozadavek_mtz_zrusen, :xmontovatelna_skupina);
--  execute procedure LOG_DEBUG_MSG ('po exportu BOM');

  -- Vlozim operace.
  insert into I2_ROUTINGHEADER (ROUTINGID, SOMA_VYROBNI_DATA)
    values (:xbomid, '*');
  
  if (xodbytova_zakazka = '*' and xmontovatelna_skupina = '*') then
  begin
    if (xaps_montaz_dokonceni = '*') then
    begin
--      execute procedure LOG_DEBUG_MSG ('EXP_DIL M - MP ? at ?',:xbomid, cast ('NOW' as time));
      select min(H.TERMIN_DILY_PRO_MONTAZ) as TERMIN_DILY_PRO_MONTAZ, min(SO.PROMISEDAVAILDATE) TERMIN_POZADOVANY from ZAK_STRUKTURA_ROZPISKY_UP (:i_zakazka,:xmontazni_cislo) SR
        left outer join I2_SALESORDERLINEHLP H on SR.O_ZAKAZKA = H.ZAKAZKA and SR.O_MONTAZNI_CISLO = H.MONTAZNI_CISLO
        left outer join I2_SALESORDERLINE SO on SO.SALESORDERID = H.SALESORDERID and SO.SOLINENUM = H.SOLINENUM
      into :xtermin_dily_pro_montaz, :xtermin_pozadovany;
      xlast_termin_pozadovany = xtermin_pozadovany;

      insert into I2_BOMCOMPONENTSTERMINY (BOMID, ITEM, TERMIN, VSTUPUJICI_DIL) 
        select :xbomid, ID_DILU, 
               cast (min(TERMIN_POZADOVANY) as date) as TERMIN_POZADOVANY, 
               min (VSTUPUJICI_DIL) as VSTUPUJICI_DIL 
          from 
          (select iif (RD.VYROBNI_CISLO = 0,
                        (select ITEMID from I2_GET_ID_N(iif(:xpozadavek_mtz_zrusen = '', iif(MAT.TYP_VYSKLADNENI in ('A'), 0, MAT.ID_NAKUPOVANY_DIL), 0))),
                        (select ITEMID from I2_GET_ID_V((ZV.ID_VYRABENY_DIL), 0))
                      ) as ID_DILU,
                      UP.OZ_TERMIN_POZADOVANY as TERMIN_POZADOVANY,
                      --JH20190719 sjednoceni pocitani terminu jednou procedurou
                      --(coalesce (cast (MPZO.TERMIN_PRIPRAVENI as date), cast (MP.TERMIN_PRIPRAVENI as date))) as TERMIN_POZADOVANY,
                      --JH20170131 zmena z planovani individualnich terminu MP na terminy vyrobnich skupin 
                      --cast (min (MP.TERMIN_PRIPRAVENI) as date) as TERMIN_POZADOVANY, 
                      (MPP.VSTUPUJICI_DIL) as VSTUPUJICI_DIL 
            From ZAK_ROZPISKY R 
              join ZAK_ROZPISKY RD on RD.ZAKAZKA = R.ZAKAZKA and RD.MONTAZNI_CISLO_RODIC = R.MONTAZNI_CISLO
              join ZAK_ZAKAZKY Z on RD.ZAKAZKA = Z.ZAKAZKA 
              left outer join ZAK_MATERIALY M on M.ZAKAZKA = RD.ZAKAZKA and M.VM_CISLO = RD.MONTAZNI_CISLO and M.CISLO_OPERACE = 0  
              left outer join MTZ_MATERIAL MAT on MAT.NAZEV_MATERIALU = M.NAZEV_MATERIALU
              left outer join ZAK_VYKRESY V on V.ZAKAZKA = RD.ZAKAZKA and V.VYROBNI_CISLO = RD.VYROBNI_CISLO
              left outer join ZAR_VYKRESY ZV on ZV.VYKRES = V.VYKRES and ZV.ZRUSENO = ''
              left outer join ZAK_MONT_PRIK_POLOZKY MPP on MPP.ZAKAZKA = RD.ZAKAZKA and MPP.MONTAZNI_CISLO = RD.MONTAZNI_CISLO
              left outer join ZAK_OZ_UDAJE_POZADAVKU (RD.ZAKAZKA, RD.MONTAZNI_CISLO) UP on 1=1
            where R.ZAKAZKA =:i_zakazka and R.VYROBNI_CISLO = :i_vyrobni_cislo  and R.STORNOVANO = '' and R.ODEVZDANO = '*' 
                  and RD.STORNOVANO = '' and RD.ODEVZDANO = '*' and RD.VYROBNI_CISLO <> 0 --beru pouze V dily
          ) BCT 
          join I2_BOMCOMPONENTS BC on BC.BOMID = :xbomid and BC.ITEM = BCT.ID_DILU
          group by 1,2;
      for select  A.TERMIN_POZADOVANY, A.ITEM_LIST, RN.ROWNUM*10 as CISLO_OPERACE from ( 
          select coalesce(iif (R.VSTUPUJICI_DIL = '*', R.TERMIN,:xtermin_pozadovany), :xtermin_dily_pro_montaz) as TERMIN_POZADOVANY,
               list(C.ITEM) as ITEM_LIST
            from (select * from I2_BOMCOMPONENTS C where C.BOMID = :xbomid) C
              left outer join I2_BOMCOMPONENTSTERMINY R on R.BOMID = C.BOMID and R.ITEM = C.ITEM            
          group by 1
          ) A
          left outer join SYS_ROW_NUM ('BOMCOMPONENTS_BOMID',:xbomid) RN on 1=1
          order by 1 desc
      into :xitems_termin_pozadovany, :xitems, :xcislo_operace
      do
      begin
          xmezioperace = maxvalue(0, xlast_termin_pozadovany - xitems_termin_pozadovany);
          execute procedure I2FP_INSERT_M_SKUP_POSTUP (:xbomid, :i_zakazka, :xmontazni_cislo, :xcislo_operace, xmezioperace * 24);
          for select SS.PART as ID_DILU 
                from SPLIT_STRING (:xitems, ',') SS          
          into :xitem
          do begin
            update I2_BOMCOMPONENTS C 
              set C.ROUTINGID = :xbomid, C.OPERATIONSEQ = :xcislo_operace
              where C.BOMID = :xbomid and C.ITEM = :xitem;
            update I2_SFCCOMPONENTS C 
              set C.OPERATIONSEQ = :xcislo_operace
              where C.PRODUCTIONORDERID = :xbomid and C.ITEM = :xitem; 
          end
          if (xlast_termin_pozadovany > xitems_termin_pozadovany) then
          begin
            xlast_termin_pozadovany = xitems_termin_pozadovany;
          end
      end
      delete from I2_BOMCOMPONENTSTERMINY C where C.BOMID = :xbomid;
    end else 
    begin
      --klasicka M skupina bez planovani podle montaze nebo bez vstupujicich dilu
--      execute procedure LOG_DEBUG_MSG ('EXP_DIL M - KLASIK1 ? at ?',:xbomid, cast ('NOW' as time));
      execute procedure I2FP_INSERT_M_SKUP_POSTUP (:xbomid, :i_zakazka, :xmontazni_cislo, 10, 0);
    end
  end else begin
    select count(*)
      from ZAK_POSTUPY
      where ZAKAZKA = :i_zakazka and VYROBNI_CISLO = :i_vyrobni_cislo and VETEV = :i_vetev
      into :xpocet_operaci;
    -- ID vyrabeneho dilu pro pripadne slucovani.
    select first 1 ZARV.ID_VYRABENY_DIL
      from ZAK_VYKRESY V, ZAR_VYKRESY ZARV
      where V.ZAKAZKA = :i_zakazka and V.VYROBNI_CISLO = :i_vyrobni_cislo and
            ZARV.VYKRES = V.VYKRES
      order by ZARV.ZRUSENO
      into :xid_dilu;

    -- Prepocitam prubeznou dobu na pocet operaci.
    i_prubezna_doba = i_prubezna_doba / maxvalue(1, xpocet_operaci) * 7.5;
    --JH20160209 -- testovaci tisk, zdali je prubezna doba vubec nekdy > 0
    --vetsi jak nula je pouze u R a T zakazek -- je to nutne vubec?
    /*if (i_prubezna_doba > 0) then
    begin
        execute procedure LOG_DEBUG_MSG ('I2FP export dil ? prubezna_doba > 0', :xbomid);
    end*/

    --zjistim si cislo posledni operace (pokud jsou operaci ke konci postupu zrusene tak cislo prvni zrusene)
    select UO.CISLO_OPERACE from ZAK_GET_CISLO_UKONCUJICI_OP (:i_zakazka, :i_vyrobni_cislo, :i_vetev) UO
    into :xposledni_operace;

--    execute procedure LOG_DEBUG_MSG ('EXP_DIL V ? at ?',:xbomid, cast ('NOW' as time));
    -- Zaporny predstih se zatim nebere v uvahu.
    for select distinct coalesce(P.CISLO_OPERACE, 0) CISLO_OPERACE, 
               substring(coalesce(P.POPIS, 'Dil nema operace !!!') from 1 for 40) POPIS,
               coalesce(iif(PROF.APS_NEPLANOVAT = '', P.NORMATIV_PRIPRAVA, 0), 0) NORMATIV_PRIPRAVA, 
               coalesce(iif(PROF.APS_NEPLANOVAT = '', 
                          iif(:i_charakter_skupiny = 'O' or (:i_charakter_skupiny = 'V' and :xpocet_operaci = 0),
                            iif(coalesce(P.MNOZSTVI, 0) = 0, :i_prubezna_doba, :i_prubezna_doba / P.MNOZSTVI),
                            --JH20160118 Pro rezarnu nastavim konstantu 5 minut.
                            iif(P.MNOZSTVI = 0, 0, iif(PROF.REZARNA = '' or P.NORMATIV_PRACE > 0, P.NORMATIV_PRACE / P.MNOZSTVI, :i_normativ_rezarna))), 0), 0) NORMATIV_PRACE,
               coalesce(iif(PROF.APS_NEPLANOVAT = '', P.NORMATIV_PRIPRAVA, 0), 0) NORMATIV_PRIPRAVA_NEZKRACENY, 
               coalesce(iif(PROF.APS_NEPLANOVAT = '', 
                          iif(:i_charakter_skupiny = 'O' or (:i_charakter_skupiny = 'V' and :xpocet_operaci = 0),
                            iif(coalesce(P.MNOZSTVI, 0) = 0, :i_prubezna_doba, :i_prubezna_doba / P.MNOZSTVI),
                            --JH20160118 Pro rezarnu nastavim konstantu 5 minut.
                            iif(P.MNOZSTVI = 0, 0, iif(PROF.REZARNA = '' or P.NORMATIV_PRACE > 0, P.NORMATIV_PRACE / P.MNOZSTVI, :i_normativ_rezarna))), 0), 0) NORMATIV_PRACE_NEZKRACENY,
               coalesce(P.VYCHOZI_OPERACE, '') VYCHOZI_OPERACE, coalesce(P.PRIPRAVENO, '') PRIPRAVENO, coalesce(P.ZADANO, '') ZADANO,
               coalesce(P.DOKONCENO, '') DOKONCENO, coalesce(P.ODVEDENO_VSE, '') ODVEDENO_VSE, P.DATUM_DOKONCENI,
               coalesce(P.NASLEDUJICI_OPERACE, 0) NASLEDUJICI_OPERACE, 
               --KN20151016 Vraceno zpet na priznak kooperace. Nepoptane kooperace se terminuji pomoci LPST.
               coalesce(P.KOOPERACE, '') KOOPERACE, 
               --KN20151021 coalesce(iif(PROF.APS_NEPLANOVAT = '', PROF.MEZIOPERACE, 0), 0) MEZIOPERACE,
               --KN20151021 U kooperaci, ktere nemaji termin z objednavky pouziju PROFESE.APS_KOOPERACE_PRUBEZNA_DOBA pro stanoveni delky zpracovani (nebude se vkladat virtualni polozka obj. s terminem).
               coalesce(iif(P.KOOPERACE = '*', PROF.APS_KOOPERACE_PRUBEZNA_DOBA, iif(PROF.APS_NEPLANOVAT = '', PROF.MEZIOPERACE, 0)), 0) MEZIOPERACE,
               coalesce(P.PROFESE_PUVODNI, '') PROFESE_PUVODNI, coalesce(P.PROFESE, '') PROFESE, coalesce(PROF.REZARNA, '') REZARNA, 
               coalesce(PROF.APS_DAVKOVY_ZDROJ, '') APS_DAVKOVY_ZDROJ, coalesce(PROF.APS_NEPOUZIVAT_LINKDOWNSTREAM, '') APS_NEPOUZIVAT_LINKDOWNSTREAM, coalesce(P.ZRUSENO, '') ZRUSENO,
               coalesce(P.MNOZSTVI, 0) MNOZSTVI
          from ZAK_VYKRESY V
          left outer join ZAK_POSTUPY P on P.ZAKAZKA = V.ZAKAZKA and P.VYROBNI_CISLO = V.VYROBNI_CISLO and P.VETEV = :i_vetev
          left outer join ZAK_PROFESE PROF on PROF.PROFESE = P.PROFESE
          where V.ZAKAZKA = :i_zakazka and V.VYROBNI_CISLO = :i_vyrobni_cislo
          into :xcislo_operace, :xpopis, :xnormativ_priprava, :xnormativ_prace, :xnormativ_priprava_nezkraceny, :xnormativ_prace_nezkraceny,
               :xvychozi_operace, :xpripraveno, :xzadano, 
               :xdokonceno, :xodvedeno_vse, :xdatum_dokonceni, :xnasledujici_operace, :xkooperace,
               :xmezioperace,
               :xprofese_puvodni, :xprofese,  :xrezarna, :xdavkovy_zdroj, :xnepouzivat_linkdownstream, :xzruseno, :xmnozstvi_operace
    do begin
--      execute procedure LOG_DEBUG_MSG ('Operace ?',:xcislo_operace);
      -- Pooperacni doba (pro objednavky).
      --20160530JH -- kontrola POOPERACNI_DOBA z ZAK_VYROBNI_PRIKAZY se nikde nepouziva -- pravdepodobne zbytecny udaj.
      --select max(coalesce(POOPERACNI_DOBA, 0))
      --  from ZAK_VYROBNI_PRIKAZY
      --  where ZAKAZKA = :i_zakazka and VYROBNI_CISLO = :i_vyrobni_cislo and CISLO_OPERACE = :xcislo_operace and
      --        ODVEDENO = '' and ZMETEK = '' and STORNOVANO = ''
      --  into :xpooperacni_doba;
      
      xobjednavka = 0;
      xtermin_pozadavku = null;
      -- Vypocitam mezioperacni dobu.
      xmezioperace_skutecna = 0;
      xspojit_operace = 0;
      if (xdokonceno = '') then
      begin
        if (xodvedeno_vse = '') then
        begin
          if (xkooperace > '') then
          begin
--            execute procedure LOG_DEBUG_MSG ('Kooperace ?',:xkooperace);
            xnormativ_priprava = 0;
            xnormativ_prace = 0;
            xnormativ_priprava_nezkraceny = 0;
            xnormativ_prace_nezkraceny = 0;

            --select coalesce(max(VP.OBJEDNAVKA), 0), max(coalesce(VP.TERMIN_POTVRZENY, O.TERMIN, VP.TERMIN_POZADAVKU, O.TERMIN_POZADOVANY, CURRENT_TIMESTAMP))
            select coalesce(max(VP.OBJEDNAVKA), 0) OBJEDNAVKA, 
                   max(coalesce(VP.TERMIN_POTVRZENY, O.TERMIN, VP.TERMIN_POZADAVKU, O.TERMIN_POZADOVANY)) TERMIN_POZADAVKU, 
                   sum(coalesce(VP.MNOZSTVI, P.MNOZSTVI)) MNOZSTVI_KOOP
              from ZAK_POSTUPY P
              left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE and VP.OBJEDNAVKA > 0
              left outer join MTZ_OBJEDNAVKY O on O.OBJEDNAVKA = VP.OBJEDNAVKA
              where P.ZAKAZKA = :i_zakazka and P.VYROBNI_CISLO = :i_vyrobni_cislo and P.CISLO_OPERACE = :xcislo_operace
              into :xobjednavka, :xtermin_pozadavku, :xmnozstvi_koop;
--            execute procedure LOG_DEBUG_MSG ('Obj ?',:xobjednavka);

            -- Mezioperaci vypoctu dle terminu pozadavku (plus jeden den na kontrolu).
            -- !!!!!  MEZIOPERACNI DOBA BUDE POUZE U POSLEDNI OPERACE (POSTUPU I ROZPISKY) I V PRIPADE, ZE JE RADA PRERUSENA  !!!!!

            --KN20150608 Termin dodani kooperacni operace provedeme virtualnim pozadavkem "materialu", ktery ma termin dodani = xtermin_pozadavku.
            if (xtermin_pozadavku is not null) then
            begin
--              execute procedure LOG_DEBUG_MSG ('Termin poz ?',:xtermin_pozadavku);
              -- Termin pozadavku nastavim na posledni operaci.
              --KN20151027 Prerusene rady pry nejsou, tak budu doplnovat konecny termin do vsech operaci dane polozky objednavky.
              xpocet = 0;
              select count(*)
                from ZAK_POSTUPY P
                join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE and VP.OBJEDNAVKA > 0
                where P.ZAKAZKA = :i_zakazka and P.VYROBNI_CISLO = :i_vyrobni_cislo and P.VETEV = :i_vetev and P.CISLO_OPERACE > :xcislo_operace and P.KOOPERACE > ''
                into :xpocet;
              if (xpocet = 0) then
              begin
--                execute procedure LOG_DEBUG_MSG ('xpocet = 0 1');
                -- Existuje v nadrizenem postupu stejna objednavka?
                select count(*)
                  from ZAK_VYROBNI_PRIKAZY VP
                  left outer join ZAK_POSTUPY P on P.ZAKAZKA = VP.ZAKAZKA and P.VYROBNI_CISLO = VP.VYROBNI_CISLO and P.CISLO_OPERACE = VP.CISLO_OPERACE and P.VETEV = :i_vetev
                  left outer join ZAK_ROZPISKY R on R.ZAKAZKA = P.ZAKAZKA and R.VYROBNI_CISLO = P.VYROBNI_CISLO
                  left outer join ZAK_ROZPISKY ROTEC on ROTEC.ZAKAZKA = R.ZAKAZKA and ROTEC.MONTAZNI_CISLO = R.MONTAZNI_CISLO_RODIC
                  left outer join ZAK_VYROBNI_PRIKAZY VPOTEC on VPOTEC.ZAKAZKA = ROTEC.ZAKAZKA and VPOTEC.VYROBNI_CISLO = ROTEC.VYROBNI_CISLO
                  where VP.ZAKAZKA = :i_zakazka and VP.VYROBNI_CISLO = :i_vyrobni_cislo and
                        --KN20160616((VP.CISLO_OPERACE > :xcislo_operace and VP.OBJEDNAVKA = :xobjednavka) or (VPOTEC.OBJEDNAVKA = :xobjednavka))
                        ((VP.CISLO_OPERACE > :xcislo_operace and VP.OBJEDNAVKA > 0) or (VPOTEC.OBJEDNAVKA > 0))
                  into :xpocet;
--                execute procedure LOG_DEBUG_MSG ('novy xpocet = ?', :xpocet);
              end
              
              -- Zalozime fiktivni "material" pro stanoveni terminu dodani kooperacni operace a tim padem dokonceni dane operace.
              select ITEM from I2_GET_ITEM_VIRT_KOOP_MAT(:i_zakazka, :i_vyrobni_cislo, :xcislo_operace, :i_vetev) 
                into :xitem_koop;
              insert into I2_ITEMMASTER (ITEM, ITEMDESC, CATEGORY) 
                values (:xitem_koop, :xitem_koop, 'O');
              insert into I2_SFCCOMPONENTS (PRODUCTIONORDERID, ITEM, QTYREQUIRED, UD_QTYPER, OPERATIONSEQ)
                values (:xbomid, :xitem_koop, :xmnozstvi_koop, :xmnozstvi_koop, :xcislo_operace);
              insert into I2_BOMCOMPONENTS (BOMID, ITEM, QTYPER, SOMA_VYROBNI_DATA, ROUTINGID, OPERATIONSEQ)
                values (:xbomid, :xitem_koop, :xmnozstvi_koop, '*', :xbomid, :xcislo_operace);
              insert into I2_PURCHORDLINE (PURCHASEORDERID, POLINENUM, ITEM, QTYOPEN, SCHEDULEDDELRYDATE)
                values ('OBJ-KOOPERACE-VYROBA', :xitem_koop, :xitem_koop, :xmnozstvi_koop,  iif(:xtermin_pozadavku < CURRENT_DATE, CURRENT_DATE + 1, cast(:xtermin_pozadavku as DATE)));
              /*KN20151123 Sjednoceno kvuli simulacim
              insert into I2_PURCHORDLINE2 (PURCHASEORDERID, POLINENUM, ITEM, QTYOPEN, SCHEDULEDDELRYDATE)
                values ('OBJ-KOOPERACE-VYROBA', :xitem_koop, :xitem_koop, :xmnozstvi_koop, iif(:xtermin_pozadavku < CURRENT_DATE, CURRENT_DATE + 1, cast(:xtermin_pozadavku as DATE)));*/
              -- Nastavim vysokou prumernou dobu dodani. Kazdy tento "dil" ma vzdy termin dodani a nestane se tak, ze pokud bude tento termin dodani dlouhy, ze APS "vygeneruje"
              -- novou polozku s terminem dodanim DNES + AVGLEADTIME.
              insert into I2_APPRSUPITEM (SUPPLIERID, ITEM, AVGLEADTIME, TIMEUOM)
                values ('ICO-NEURCEN', :xitem_koop, 3650, 'DAYS');
              /*KN20151123 Sjednoceno kvuli simulacim
              insert into I2_APPRSUPITEM2 (SUPPLIERID, ITEM, AVGLEADTIME, TIMEUOM)
                values ('ICO-NEURCEN', :xitem_koop, 3650, 'DAYS');*/
--              execute procedure LOG_DEBUG_MSG ('fiktivni zalozen');

              --KN20151027 I presto, ze se vklada virtualni polozka s terminem dodani pro vsechny operace, tak mezioperace je pouze u posledni operace.
              if (xpocet = 0) then
              begin
--                execute procedure LOG_DEBUG_MSG ('xpocet = 0 2');
                --KN20150605 Pokud je termin pozadavku v minulosti a operace neni dokoncena (termin dokonceni je na dnesek), tak pripoctu dva dny.
                --JH20181219 Jsem s terminem v minulosti prictu 2 dny, v budoucnosti prictu 1 den u zpozdenych dilu o vice jak 4, 4 u ostatnich
                if (xtermin_pozadavku < current_date) then
                begin
                  xmezioperace_skutecna = 2;
                end else 
                begin
                  xmezioperace_skutecna = 4;
                  if (exists (select Z.PKID from ZAK_APS_ZPOZDENI Z
                    where Z.ZPOZDENI > :xmezioperace_skutecna and Z.ZAKAZKA_VYROBNI = :i_zakazka and Z.VYROBNI_CISLO_VYROBNI = :i_vyrobni_cislo)) then
                  begin
                    xmezioperace_skutecna = 1;
                  end
                end
                xmezioperace_skutecna = xmezioperace_skutecna * 24;
              end else begin
                -- Nejedna se o posledni operaci v rade kooperaci.
                xmezioperace_skutecna = 0;
              end
            end else begin
              xmezioperace_skutecna = xmezioperace * 24;
            end
          end else begin
            --neni to kooperace
            xmezioperace_skutecna = xmezioperace * 24;
          end
          --pripocteni specialni mezioperacni doby u detailu svarencu na posledni operaci (7 dnu)
          if (xnasledujici_operace = :xposledni_operace) then
          begin
--            execute procedure LOG_DEBUG_MSG ('detail svarence');
            if (exists (select  P.PKID from  ZAK_POSTUPY P
              left outer join ZAK_ROZPISKY R on R.ZAKAZKA = P.ZAKAZKA and R.VYROBNI_CISLO = P.VYROBNI_CISLO
              left outer join ZAK_ROZPISKY RO on RO.ZAKAZKA = R.ZAKAZKA and RO.MONTAZNI_CISLO = R.MONTAZNI_CISLO_RODIC
              left outer join ZAR_KODY_SKUPIN KSO on RO.KOD_SKUPINY = KSO.KOD
              left outer join ZAK_POSTUPY PO on PO.ZAKAZKA = RO.ZAKAZKA and PO.VYROBNI_CISLO  = RO.VYROBNI_CISLO and PO.VYCHOZI_OPERACE = '*'
              where P.ZAKAZKA = :i_zakazka and P.VYROBNI_CISLO = :i_vyrobni_cislo and P.CISLO_OPERACE = :xcislo_operace and KSO.SVARENEC = '*' and KSO.KOD= 'S'
              --pokud je posledni OP v koo a prvni nadrazena v koo u stejneho dod tak nepridavam 
              and not
              (P.KOOPERACE = '*' and PO.KOOPERACE = '*' and 
                (select coalesce(min(O.ICO),'') from ZAK_VYROBNI_PRIKAZY VP 
                  left outer join MTZ_OBJEDNAVKY O on O.OBJEDNAVKA = VP.OBJEDNAVKA
                  where VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
                ) = 
                (select coalesce(min(O.ICO),'') from ZAK_VYROBNI_PRIKAZY VP
                  left outer join MTZ_OBJEDNAVKY O on O.OBJEDNAVKA = VP.OBJEDNAVKA
                  where VP.ZAKAZKA = PO.ZAKAZKA and VP.VYROBNI_CISLO = PO.VYROBNI_CISLO and VP.CISLO_OPERACE = PO.CISLO_OPERACE
                )
              )
            )) then
            begin
              xmezioperace_skutecna = xmezioperace_skutecna + 168;
            end 
--            execute procedure LOG_DEBUG_MSG ('KONEC detail svarence');
          end
        end else begin
          -- Operace je cela odvedena ("ukrajujeme" cas od mezioperace od data dokonceni - tzn. odecitani mezioperacni doby az do nuly).
          xmezioperace_skutecna = maxvalue(0, iif(xkooperace <> '', 1, xmezioperace) - datediff(DAY, coalesce(xdatum_dokonceni, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)) * 24;
        end
      end      
      --KN20151113 U zrusenych operaci neni mezioperacni doba.
      if (xzruseno > '') then
      begin
        xmezioperace_skutecna = 0;
      end
      --u operace kdy nasledujici operace je se stejnou profesi vynuluju mezioperacni cas
      if (xnasledujici_operace <> :xposledni_operace) then
      begin
        if (exists(select P.PKID from ZAK_POSTUPY P 
                     where P.ZAKAZKA = :i_zakazka and P.VYROBNI_CISLO = :i_vyrobni_cislo and P.CISLO_OPERACE = :xnasledujici_operace 
                           and P.VETEV = :i_vetev and P.PROFESE_PUVODNI = :xprofese_puvodni)) then 
        begin
          xmezioperace_skutecna = 0;
          xspojit_operace = 1;
          --JH20161122 vyruseni vlacku u zayera 3550.
          if (xnepouzivat_linkdownstream = '*') then
          begin
            xspojit_operace = 0;
          end
        end
      end 
      -- K mezioperaci se u posledni operace pripocitava predstih, ktery se ponizuje o dane dny, pokud xmezioperace_skutecna rovna nule.
      xpredstih_skutecny = 0;
      if (xnasledujici_operace = :xposledni_operace) then
      begin
        xpredstih_skutecny = maxvalue(0, xpredstih -
                   -- Odecitam az pokud je nulova xmezioperace_skutecna.
                   iif(xmezioperace_skutecna = 0, datediff(DAY, xdatum_dokonceni, CURRENT_TIMESTAMP), 0)) * 24;
      end
      --predstih skutecny pravdepodobne neni > 0 nikdy -- testovaci log
      if (xpredstih_skutecny > 0) then
      begin
        execute procedure LOG_DEBUG_MSG ('I2FP export dil ? xpredstih_skutecny > 0', :xbomid);
      end
      xmezioperace_skutecna = xmezioperace_skutecna + xpredstih_skutecny;
      
      -- Slucovani operaci vykresu a materialu v rezarne.
      --20151026 - neni v profesich nastaveno, takze se to k dnesku nevyuziva.
      --201606   - zacalo se to pouzivat
      xbatchtype = null;
      if (xdavkovy_zdroj = '*' and xprofese > '') then
      begin
        if (xrezarna = '') then
        begin
          -- Slucuji dle ID vykresu.
          /* Sloucene operace se daji ze sebe (seriove), ale terminy zahajeni a dokonceni budou mit stejne
             a budou vychazet ze SOUCTU vsech sloucenych operaci. U kratkodobych operaci to nevadi, ale vadi
             to u dloho trvajicich operaci (trvani ve dnech a vice).
             Termin dokonceni tak bude stejny pro vsechny dily v davce. Pokud se budou dily zpracovavat kus po
             kuse a dily za VZ1 budou dokonceny druhy den (ze tri), tak se nasledujici operace VZ1 naplanuje 
             stejne az na PET posledniho dilu v davce -> vznikne tim mozne zpozdeni VZ1.
             TOTO ZPOZDENI NASTANE POUZE V PRIPADE NEDOSTUPNOSTI KAPACIT - JINAK BUDE TERMIN DOKONCENI DILU
             V DAVCE ROVEN TERMINU DOKONCENI NEJDRIVEJSIHO DILU V DAVCE.
             Musi se to zapnout v i2FP (stejne jako CAO) - rhythm_server.rd.
             BATCHTYPE by mel respektovat i cislo operace (jak to vyresit u nestandardnich vstupu, kde "stejne"
             operace mohou byt 20 a 80 - podle udaje VYTVORENO_Z_OPERACE). */
          -- !!! Najit vhodny retezec pro slucovani. Prvni pokus je profese 3542 a skupina vykresu z tabulky ZAR_VYKRESY_SKUPINY_PROFESE
          select SKUPINA from ZAR_VYKRESY_SKUPINY_PROFESE VSP
            where VSP.VYKRES = :i_vykres and VSP.PROFESE = :xprofese
          into :xbatchtype;
          if (xbatchtype is null) then
          begin
            xbatchtype = 'V' || xid_dilu;
          end
        end else begin
          -- Slucuji dle ID materialu.
          select 'N' || MAT.ID_NAKUPOVANY_DIL
            from ZAK_MATERIALY M, MTZ_MATERIAL MAT
            where M.ZAKAZKA = :i_zakazka and M.VM_CISLO = :i_vyrobni_cislo and M.CISLO_OPERACE = :xcislo_operace and 
                  M.HLAVNI_MATERIAL = '*' and M.STORNOVANO = '' and M.ZRUSENO = '' and M.MNOZSTVI_VYDANO < M.MNOZSTVI_MATERIALU and
                  MAT.NAZEV_MATERIALU = M.NAZEV_MATERIALU
            into :xbatchtype;
        end
      end
      if (xbatchtype is not null) then
      begin
        select count(*)
          from I2_BATCHRESOURCEDEF
          where WORKCENTERNAME = 'Soma' and RESOURCENAME = :xprofese and BATCHTYPE = :xbatchtype
          into :xpocet;
        if (xpocet = 0) then
        begin
          insert into I2_BATCHRESOURCEDEF (WORKCENTERNAME, RESOURCENAME, BATCHTYPE, MINBATCHSIZE, MAXBATCHSIZE, IDEALBATCHSIZE)
            values ('Soma', :xprofese, :xbatchtype, 0, 999999999, :i_mnozstvi);
            --JH20160613 dana verze pouze s mnozstvim
            --values ('Soma', :xprofese, :xbatchtype, 0, 999999999, :i_mnozstvi - :i_mnozstvi_dokonceno);
        end else 
        begin
          --JH20160613 dana verze pouze s mnozstvim
          --update I2_BATCHRESOURCEDEF set IDEALBATCHSIZE = IDEALBATCHSIZE + (:i_mnozstvi - :i_mnozstvi_dokonceno) 
          update I2_BATCHRESOURCEDEF set IDEALBATCHSIZE = IDEALBATCHSIZE + (:i_mnozstvi) 
            where WORKCENTERNAME = 'Soma' and RESOURCENAME = :xprofese and BATCHTYPE = :xbatchtype;
        end
      end
      
      --KN20151003 Od normativu musim odecist jiz odvedeny cas.
      --JH20160824 Musim pocitat se sloucenymi VP, kde se zahajuje pouze 1. operace
      select coalesce (sum (CAS.DOBA / 60.0 / 60.0), 0) as ODVEDENY_CAS 
      from (select PC.*, VP2.MNOZSTVI
        from ZAK_VYROBNI_PRIKAZY VP2 
          join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = VP2.ZAKAZKA and VP.VYROBNI_CISLO = VP2.VYROBNI_CISLO and 
                     ((VP2.CISLO_SLOUCENEHO_VP > 0 and VP.CISLO_SLOUCENEHO_VP = VP2.CISLO_SLOUCENEHO_VP) or 
                     (VP2.CISLO_SLOUCENEHO_VP = 0 and VP.CISLO_OPERACE = VP2.CISLO_OPERACE and VP.INDEX_DETAILU = VP2.INDEX_DETAILU))
          left outer join ZAK_VYROBNI_PRIKAZY_CASY PC on PC.ZAKAZKA = VP.ZAKAZKA and VP.VYROBNI_CISLO = PC.VYROBNI_CISLO
                          and VP.CISLO_OPERACE = PC.CISLO_OPERACE and VP.INDEX_DETAILU = PC.INDEX_DETAILU          
          where VP2.ZAKAZKA = :i_zakazka and VP2.VYROBNI_CISLO = :i_vyrobni_cislo and VP2.CISLO_OPERACE = :xcislo_operace and
                VP.ODVEDENO = '' and VP.STORNOVANO = '' and VP.ZMETEK = '' and PC.PKID is not null) PC
        left outer join ZAK_VYR_PRIK_CASY_VYPOCIST(PC.DATUM_ZAHAJENI, PC.DATUM_DOKONCENI, PC.CISLO_SPOL_ZAHAJENI, PC.MNOZSTVI) CAS on 1=1
      into :xodvedeny_cas;

      if (xodvedeny_cas > 0) then
      begin
        select cast (coalesce (sum (VP.MNOZSTVI * P.NORMATIV_HODIN_KS),0) as MNOZSTVI) as NORMATIV
          from ZAK_VYROBNI_PRIKAZY VP2 
            join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = VP2.ZAKAZKA and VP.VYROBNI_CISLO = VP2.VYROBNI_CISLO and 
                       ((VP2.CISLO_SLOUCENEHO_VP > 0 and VP.CISLO_SLOUCENEHO_VP = VP2.CISLO_SLOUCENEHO_VP) or 
                       (VP2.CISLO_SLOUCENEHO_VP = 0 and VP.CISLO_OPERACE = VP2.CISLO_OPERACE and VP.INDEX_DETAILU = VP2.INDEX_DETAILU))
            left outer join ZAK_POSTUPY P on P.ZAKAZKA = VP.ZAKAZKA and P.VYROBNI_CISLO = VP.VYROBNI_CISLO and P.CISLO_OPERACE = VP.CISLO_OPERACE                
            where VP2.ZAKAZKA = :i_zakazka and VP2.VYROBNI_CISLO = :i_vyrobni_cislo and VP2.CISLO_OPERACE = :xcislo_operace and
                  VP.ODVEDENO = '' and VP.STORNOVANO = '' and VP.ZMETEK = '' and VP.CISLO_OPERACE < VP2.CISLO_OPERACE
        into :xnormativ_predchozich_op;
        xodvedeny_cas = maxvalue (:xodvedeny_cas - :xnormativ_predchozich_op, 0);
      end  


      

      if (xodvedeny_cas > 0 and xmnozstvi_operace > 0) then
      begin
        --KN20151123 Normativ prace pro APS je za jeden kus!!!
        xodvedeny_cas = xodvedeny_cas / xmnozstvi_operace;

        xn = xnormativ_prace - minvalue(xnormativ_prace, xodvedeny_cas);
        xodvedeny_cas = xodvedeny_cas - (xnormativ_prace - xn);
        xnormativ_prace = xn;
        xnormativ_priprava = xnormativ_priprava - minvalue(xnormativ_priprava, xodvedeny_cas);
      end
      insert into I2_ROUTINGOPERATION
          (ROUTINGID, OPERATIONSEQ, OPERATION, INTSETUPTIME, RUNTIMEPER, TEARDOWNTIME, LINKDOWNSTREAM, MINQUEUETIME, AVGQUEUETIME, TIMEUOM, PRIMARYBATCHTYPE, SOMA_VYROBNI_DATA)
        values
          (:xbomid, :xcislo_operace, :xpopis, :xnormativ_priprava, :xnormativ_prace, :xmezioperace_skutecna, :xspojit_operace, 0, 0, 'HOURS', :xbatchtype, '*');      
      --JH20160108 zmena, i do portu 2 na zadost Z. Vernera vkladame pokraceny normativ (puvodne se vkladaly nezkracene varianty xnormativ_priprava_nezkraceny)
      insert into I2_ROUTINGOPERATION2
          (ROUTINGID, OPERATIONSEQ, OPERATION, INTSETUPTIME, RUNTIMEPER, TEARDOWNTIME, LINKDOWNSTREAM, MINQUEUETIME, AVGQUEUETIME, TIMEUOM, PRIMARYBATCHTYPE, SOMA_VYROBNI_DATA)
        values
          (:xbomid, :xcislo_operace, :xpopis, :xnormativ_priprava, :xnormativ_prace, :xmezioperace_skutecna, :xspojit_operace, 0, 0, 'HOURS', :xbatchtype, '*');
    end
    
--    execute procedure LOG_DEBUG_MSG ('po operacich');
    --KN20151012 Pokud zdroj nema do budoucna zadnou kapacitu (je zde pouze pracovnik s dennim fondem = 0), tak prevedu zdroj na alternaci s nejvyssi prioritou.
    --KN20151026 Nemuzu pouzit CURRENT_DATE, protoze to ve svatcich a o vikendu priradi spatne profese (protoze je v techto dnech denni fond a pocet pracovniku roven nule).
    -- Naleznu nasledujici pracovni den.
    xdatum = CURRENT_DATE;
    while (exists(select * from ZAK_SVATKY where DATUM = :xdatum)) do
    begin
      xdatum = xdatum + 1;
    end
    insert into I2_OPRESOURCE (ROUTINGID, OPERATIONSEQ, WORKCENTERNAME, RESOURCENAME, SOMA_VYROBNI_DATA)
      select :xbomid, O.CISLO_OPERACE, 'Soma', 
             coalesce(iif(right(O.PROFESE, 1) = 'K',
                        O.PROFESE,
                        (with recursive PROFESE as (
                          --JH20160602 zmena z PROFESE na PROFESE_PUVODNI -- kvuli ZAYERUM a odstraneni alternace zayeru.
                          --JH20160603 zmena z PROFESE_PUVODNI na PROFESE -- kvulirucne zadanym alternacim u ostatnich profesi.                    
                          select P.PROFESE as PROFESE, 99999 PRIORITA, 0 as KOREN
                            from ZAK_POSTUPY P
                            where P.ZAKAZKA = O.ZAKAZKA and P.VYROBNI_CISLO = O.VYROBNI_CISLO and P.CISLO_OPERACE = O.CISLO_OPERACE
                          union all
                          select A.ALTERNATIVA PROFESE, A.PRIORITA, 1 as KOREN
                            from PROFESE PROFESE
                            join ZAK_PROFESE_ALTERNATIVY A on A.PROFESE = PROFESE.PROFESE
                            where PROFESE.KOREN = 0
                            order by A.  desc)
                        select first 1 P.PROFESE
                          from PROFESE P
                          left outer join KAP_PROFESE_DENNI_FOND (coalesce(P.PROFESE, ''), :xdatum) DF on 1=1
                          left outer join (select KAP.PROFESE, min(KAP.DATUM) NASLEDUJICI_KAPACITA 
                                             from ZAK_PROFESE_KAPACITY KAP 
                                             where KAP.DATUM > :xdatum and KAP.VYRAZENI = '' and KAP.DENNI_FOND > 0 group by KAP.PROFESE) KAP on KAP.PROFESE = P.PROFESE
                          where not (DF.POCET_PRACOVNIKU >= 0 and DF.DENNI_FOND = 0 and (KAP.NASLEDUJICI_KAPACITA is null or CURRENT_DATE + 14 < KAP.NASLEDUJICI_KAPACITA))
                          order by P.PRIORITA desc)), O.PROFESE), '*'
        from (select P.ZAKAZKA, P.VYROBNI_CISLO, coalesce(P.CISLO_OPERACE, 0) CISLO_OPERACE, 
                     coalesce(P.PROFESE || iif((P.KOOPERACE_NAVRH = 2) or (P.KOOPERACE = '*' and P.KOOPERACE_TECHNOLOGICKA = ''), 'K', ''), '0001') PROFESE
                from ZAK_VYKRESY V
                left outer join ZAK_POSTUPY P on P.ZAKAZKA = V.ZAKAZKA and P.VYROBNI_CISLO = V.VYROBNI_CISLO and P.VETEV = :i_vetev
                where V.ZAKAZKA = :i_zakazka and V.VYROBNI_CISLO = :i_vyrobni_cislo) O;
    
--    execute procedure LOG_DEBUG_MSG ('po zdrojich');
    -- Alternativni postup.
    insert into I2_OPRESOURCEALT
      (ROUTINGID, OPERATIONSEQ, ALTWORKCENTERNAME, ALTRESOURCENAME, ALTRESOURCECOUNT, PRIORITY, 
       OFFLOADTYPE, WORKCENTERNAME, RESOURCENAME,
       INTSETUPTIME, RUNTIMEPER, TEARDOWNTIME,
       SOMA_VYROBNI_DATA)
      select :xbomid, CISLO_OPERACE, 'Soma', ALTERNATIVA, 1, PRIORITA,
             'CAO', 'Soma', PROFESE,
             NORMATIV_PRIPRAVA, NORMATIV_PRACE, MEZIOPERACE * 24,
             '*'
        from (select P.CISLO_OPERACE, P.PROFESE, A.ALTERNATIVA, A.PRIORITA,
                     ALTPROF.POCET_ZDROJU, ALTPROF.MEZIOPERACE,
                     P.NORMATIV_PRIPRAVA * iif(A.KOEFICIENT = 0, 1, A.KOEFICIENT) NORMATIV_PRIPRAVA,
                     P.NORMATIV_PRACE / P.MNOZSTVI * iif(A.KOEFICIENT = 0, 1, A.KOEFICIENT) NORMATIV_PRACE
                from ZAK_POSTUPY P
                join ZAK_PROFESE PROF on PROF.PROFESE = P.PROFESE
                -- Alternativy jsou pro puvodni profesi - ne pro zmenenou z APS.
                join ZAK_PROFESE_ALTERNATIVY A on A.PROFESE = P.PROFESE_PUVODNI
                join ZAK_PROFESE ALTPROF on ALTPROF.PROFESE = A.ALTERNATIVA
                left outer join KAP_PROFESE_DENNI_FOND (coalesce(A.ALTERNATIVA, ''), :xdatum) DF on 1=1
                left outer join (select KAP.PROFESE, min(KAP.DATUM) NASLEDUJICI_KAPACITA 
                                   from ZAK_PROFESE_KAPACITY KAP 
                                   where KAP.DATUM > :xdatum and KAP.VYRAZENI = '' and KAP.DENNI_FOND > 0 group by KAP.PROFESE) KAP on KAP.PROFESE = A.ALTERNATIVA
                where P.ZAKAZKA = :i_zakazka and P.VYROBNI_CISLO = :i_vyrobni_cislo and P.VETEV = :i_vetev and
                      -- Pokud je nastaven priznak kooperace, tak negeneruji alternativy.
                      P.KOOPERACE = '' and
                      -- Pokud je operace zpracovana, tak negeneruji alternativy.
                      P.MNOZSTVI_NEZPRACOVANO > 0 and
                      P.KOOPERACE_NAVRH in (0, 1) and
                      -- Je povoleno planovani pro APS
                      ALTPROF.APS_NEPLANOVAT = ''
                      /* Nasledujici parametry jsou pouze pro kooperace - ne pro vnitrofiremni alternativy.
                      -- Neni zakazano vytvoreni alternativy.
                      and ALTPROF.NEVYTVARET_ALTERNATIVY_KK = '' and
                      -- Generuji alternativy pokud je normativ hodin vetsi nez zadana hodnota u profese.
                      and ALTPROF.APS_NORMATIV_HODIN_PRO_KK < P.NORMATIV_HODIN*/
                      and not (DF.POCET_PRACOVNIKU >= 0 and DF.DENNI_FOND = 0 and (KAP.NASLEDUJICI_KAPACITA is null or CURRENT_DATE + 14 < KAP.NASLEDUJICI_KAPACITA)));                        
--    execute procedure LOG_DEBUG_MSG ('po alternativnich postupech');
    -- Alternativy kapacitni kooperace.
    --20110307 Kontrolovana vhodnost kooperace jak na vykresu, tak na operaci.
    /*xvhodnost_kooperace = '';
    select VHODNOST_KOOPERACE
      from ZAR_VYKRESY
      where VYKRES = :i_vykres and ZRUSENO = ''
      into :xvhodnost_kooperace;
    if (xvhodnost_kooperace <> 'S') then
    begin*/
      /*KN20150413 - Na zadost Z.Vernera jsou KOOPERACNI ALTERNATIVY vypnuty - aby bylo mozne automaticky vyuzit interni alternativy v Soma, ktere se automaticky preplanovavaji.
      insert into I2_OPRESOURCEALT
        (ROUTINGID, OPERATIONSEQ, ALTWORKCENTERNAME, ALTRESOURCENAME, ALTRESOURCECOUNT, PRIORITY, 
         OFFLOADTYPE, WORKCENTERNAME, RESOURCENAME,
         INTSETUPTIME, RUNTIMEPER, TEARDOWNTIME,
         SOMA_VYROBNI_DATA)
        select :xbomid, CISLO_OPERACE, 'Soma', ALTERNATIVA, 1, PRIORITA,
               'CAO', 'Soma', PROFESE,
               NORMATIV_PRIPRAVA, NORMATIV_PRACE, MEZIOPERACE * 24,
               '*'
          from (select P.CISLO_OPERACE, P.PROFESE, P.PROFESE || 'K' as ALTERNATIVA, iif(V.VHODNOST_KOOPERACE = 'K' or P.VHODNOST_KOOPERACE = 'K', 5, 0) as PRIORITA,
                       9999 as POCET_ZDROJU, iif(ALTPROF.MEZIOPERACE_KK = 0, ALTPROF.MEZIOPERACE, ALTPROF.MEZIOPERACE_KK) MEZIOPERACE,
                       P.NORMATIV_PRIPRAVA  NORMATIV_PRIPRAVA,
                       P.NORMATIV_PRACE  / P.MNOZSTVI NORMATIV_PRACE
                  from ZAK_POSTUPY P, ZAK_PROFESE ALTPROF, ZAR_VYKRESY V
                  where P.ZAKAZKA = :i_zakazka and P.VYROBNI_CISLO = :i_vyrobni_cislo and P.VETEV = :i_vetev and
                        V.VYKRES = P.VYKRES and V.ZRUSENO = '' and
                        (V.VHODNOST_KOOPERACE <> 'S' or P.VHODNOST_KOOPERACE = 'K') and P.VHODNOST_KOOPERACE <> 'S' and
                        -- Pokud je nastaven priznak kooperace, tak negeneruji alternativy.
                        P.KOOPERACE = '' and
                        -- Pokud byl priznak kooperace zrusen rucne, tak negeneruji kooperacni alternativy.
                        --KN20110308 Nahrazeno KOOPERACE_NAVRH.
                        --P.APS_NEGENEROVAT_ALTERNATIVY = '' and
                        -- Pokud je operace zpracovana, tak negeneruji alternativy.
                        P.MNOZSTVI_NEZPRACOVANO > 0 and 
                        ALTPROF.PROFESE = P.PROFESE_PUVODNI and
                        ALTPROF.KOOPERACE = '' and
                        -- Neni zakazano vytvoreni alternativy.
                        ALTPROF.NEVYTVARET_ALTERNATIVY_KK = '' and
                        ALTPROF.APS_NEBALANCOVAT_KAPACITY = '' and
                        -- Je povoleno planovani pro APS
                        ALTPROF.APS_NEPLANOVAT = '' and
                        -- Generuji alternativy pokud je normativ hodin vetsi nez zadana hodnota u profese.
                        ALTPROF.APS_NORMATIV_HODIN_PRO_KK < P.NORMATIV_HODIN and
                        -- Pokud jsem navrzenou kooperaci zrusil, tak znovu nevytvarim alternativy.
                        P.KOOPERACE_NAVRH in (0, 1));
      */
    /*end*/

    /* Dle mailu SFCLine z 20.4.2010 se v pripade, ze dil nema operaci, nevytvari default operace. */
    /* UD_QTYPRODUCED nesmi byt vetsi nez mnozstvi vyrobni zakazky, jinak by toto mnozstvi take povysil. */
--    execute procedure LOG_DEBUG_MSG ('pred sfc line');
    insert into I2_SFCLINE (PRODUCTIONORDERID, OPERATIONSEQ, OPERATION, UD_ACTUALSTARTDATE_ORIG, ACTUALSTARTDATE, UD_QTYPRODUCED)
      --KN20150611 na zkousku kvuli terminum koop.polozek. select :xbomid, CISLO_OPERACE, POPIS, DATUM_ZAHAJENI, DATUM_ZAHAJENI, minvalue(:i_mnozstvi, iif(MNOZSTVI_DOKONCENO = 0 and (OBJEDNAVKA > 0 and (DATUM_ZADANI_VP is not null or PRIPRAVENO = '*')), 0.001, MNOZSTVI_DOKONCENO))
      --select :xbomid, P.CISLO_OPERACE, P.POPIS, P.DATUM_ZAHAJENI, P.DATUM_ZAHAJENI,
      --JH20170412 neplnime ACTUALSTARTDATE u nekterych zakazek zpusoboval nesplnitelnost
      select :xbomid, P.CISLO_OPERACE, P.POPIS, null,null,
             minvalue(:i_mnozstvi, iif(P.MNOZSTVI_DOKONCENO = 0 and exists(select * from ZAK_VYROBNI_PRIKAZY_CASY PC where PC.ZAKAZKA = :i_zakazka and PC.VYROBNI_CISLO = :i_vyrobni_cislo and PC.CISLO_OPERACE = P.CISLO_OPERACE), 0.001, P.MNOZSTVI_DOKONCENO))
        from (select P.CISLO_OPERACE,
                     substring(P.POPIS from 1 for 40) POPIS,
                     min(coalesce(S.DATUM_STORNA, VP.DATUM_ZADANI)) DATUM_ZADANI_VP,
                     min(coalesce(S.DATUM_STORNA, VP.DATUM_ZAHAJENI_PRACE)) DATUM_ZAHAJENI,
                     max(P.PRIPRAVENO) PRIPRAVENO,
                     case
                       -- Pokud je dil dokoncen, tak pocitam s celym mnozstvi jako s dokoncenym (i kdyz neni).
                       when :xdil_dokoncen = '*' then P.MNOZSTVI
                       -- Pokud je zadan slouceny vyrobni prikaz a nasledujici operace ma nastaveny datum zahajeni prace, tak je tato operace dokoncena
                       when min(VP.CISLO_SLOUCENEHO_VP) > 0 and 
                            exists(select * from ZAK_POSTUPY P2, ZAK_VYROBNI_PRIKAZY VP2
                                     where P2.ZAKAZKA = P.ZAKAZKA and P2.VYROBNI_CISLO = P.VYROBNI_CISLO and P2.CISLO_OPERACE = P.NASLEDUJICI_OPERACE and
                                           VP2.ZAKAZKA = P2.ZAKAZKA and VP2.VYROBNI_CISLO = P2.VYROBNI_CISLO and VP2.CISLO_OPERACE = P2.CISLO_OPERACE and
                                           VP2.CISLO_SLOUCENEHO_VP = min(VP.CISLO_SLOUCENEHO_VP) and VP2.DATUM_ZAHAJENI_PRACE is not null)
                       then P.MNOZSTVI
                       -- Pokud se zdroj neplanuje, tak oznacim za dokonceny pokud je predchozi operace dokoncena.
                       when PROF.APS_NEPLANOVAT = '*' and exists(select * from ZAK_POSTUPY P2 where P2.ZAKAZKA = P.ZAKAZKA and P2.VYROBNI_CISLO = P.VYROBNI_CISLO and P2.VETEV = P.VETEV and P2.NASLEDUJICI_OPERACE = P.CISLO_OPERACE and P2.DOKONCENO = '*')
                       then P.MNOZSTVI
                       -- Pocitam pouze odvedene a zkontrolovane mnozstvi.
                       else P.MNOZSTVI_ODVEDENO + P.MNOZSTVI_KONTROLOVANO
                       /* Pripoctu k tomu mnozstvi stornovano, ktere ponizuje mnozstvi operace (zmetkove mnozstvi nepricitam, protoze bude znovu
                          vytvoreno nestandardnim vstupem a zvysovalo by to mnozstvi vyrovbni zakazky). */
                     end + P.MNOZSTVI_STORNOVANO - :i_mnozstvi_pred_kompl_na_sklad as MNOZSTVI_DOKONCENO,
                     max(VP.OBJEDNAVKA) OBJEDNAVKA
                from ZAK_POSTUPY P
                left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
                left outer join ZAK_STORNO_DOKLADY S on S.DOKLAD_STORNA = VP.DOKLAD_STORNA
                left outer join ZAK_PROFESE PROF on PROF.PROFESE = P.PROFESE
                where P.ZAKAZKA = :i_zakazka and P.VYROBNI_CISLO = :i_vyrobni_cislo and P.VETEV = :i_vetev
                group by P.ZAKAZKA, P.VYROBNI_CISLO, P.VETEV, P.CISLO_OPERACE, P.NASLEDUJICI_OPERACE, P.POPIS, P.MNOZSTVI, P.MNOZSTVI_ODVEDENO, P.MNOZSTVI_KONTROLOVANO, P.MNOZSTVI_STORNOVANO, PROF.APS_NEPLANOVAT) P;
  end
--  execute procedure LOG_DEBUG_MSG ('vlozeni vazby kusovnik postup dil (bomid: ?, routingid: ?, item: ? )');
  -- Vlozeni vazby kusovnik-postup-dil
  insert into I2_ITEMBOMROUTING (BOMID, ROUTINGID, ITEM, USABLEBYNEWMFGORD, SOMA_VYROBNI_DATA)
    values (:xbomid, :xbomid, :xitemid, 0, '*');

  -- Rezervace polozek (kam dana polozka musi vstoupit a v jakem mnozstvi). I2_SUPPLYDMDPEGGING
--  execute procedure LOG_DEBUG_MSG ('vlozeni rezervace');
  execute procedure I2FP_EXPORT_DAT_VYR_EXP_DIL_REZ (i_zakazka, i_vyrobni_cislo, i_vetev, i_id_vyrabeny_dil, i_vykres, xitemid, i_vyrobni_zakazka, xjednoucelova_zakazka, xbomid, i_mnozstvi, i_mnozstvi_dokonceno);
--  execute procedure LOG_DEBUG_MSG ('EXP_DIL END ? at ?',:xbomid, cast ('NOW' as time));
--  execute procedure LOG_DEBUG_MSG ('EXP_DIL ? runtime ?', :xbomid, cast ('NOW' as time) - :xcas);
end
^


create or alter procedure MTZ_APS_UPRAVA_TERMINU (i_zakazka UCETNI_DOKLAD default 0)
as
  declare xpredstih_nakup_vd_lpst         POCET;
  
begin
  select coalesce (min (cast (S.KEY_VALUE as POCET)),0) PREDSTIH_NAKUP
    from SYS_SETTINGS S where S.KEY_NAME = 'APS_PREDSTIH_NAKUP_VD_LPST'
  into :xpredstih_nakup_vd_lpst;

  --Rozpiskovy material
  /* Nejprve se bere v uvahu termin IS , potom termin MP (5 pracovnich dnu dopredu), pokud neni nalezen, tak se bere Termin nadrazene rozpisky u V zakazek,
     pokud neni nalezen tak se bere termin individualniho dilu, pokud neni nalezen tak se bere termin ETAPY a pokud neni nalezen, tak se bere termin nadrazene skupiny OZ
     Od terminu se odecte 1 den -- kvuli APS a pokud je mensi jak current_date tak se da current_date (nejdeme do minulosti)
  */
  merge into ZAK_MATERIALY M 
  using 
  (with RUCNI_OZ_TERMIN as (select R.ZAKAZKA, R.MONTAZNI_CISLO, min (R.TERMIN_POZADOVANY) as RUCNI_TERMIN_POZADOVANY
       from ZAK_ZAKAZKY_AKTIVNI Z
       join ZAK_ROZPISKY R on R.ZAKAZKA = Z.ZAKAZKA
       join ZAR_KODY_SKUPIN KS on KS.KOD = R.KOD_SKUPINY
       where R.TERMIN_POZADOVANY is not null and KS.MATERIAL = '*'
       group by 1,2
  ),
  TERM_V_ROZPISKY as (
     select R.ZAKAZKA, R.MONTAZNI_CISLO, 
            min (minvalue(coalesce(V.TERMIN_ZAHAJENI, V.TERMIN_LPST), (V.TERMIN_LPST - :xpredstih_nakup_vd_lpst))) as TERMIN_MATERIALU, 
            min (V.TERMIN_LPST) as TERMIN_MAT_LPST
       from ZAK_ZAKAZKY_AKTIVNI Z
       join ZAK_ROZPISKY R on R.ZAKAZKA = Z.ZAKAZKA
       left outer join ZAK_STRUKTURA_ROZPISKY_UP (R.ZAKAZKA, R.MONTAZNI_CISLO) SR on 1=1
       left outer join ZAR_KODY_SKUPIN KS on KS.KOD = SR.O_KOD_SKUPINY
       left outer join ZAK_VYKRESY V on V.ZAKAZKA = SR.O_ZAKAZKA and V.VYROBNI_CISLO = SR.O_VYROBNI_CISLO
       where KS.POSTUP = '*' and KS.ZARIZENI <> '*' 
       group by 1,2
  ),
  INDIVIDUALNI_DIL as (
    select R.ZAKAZKA, R.MONTAZNI_CISLO, 
           min (ZDO.TERMIN_DOKONCENI) as TERMIN_INDIVIDUALNI
      from ZAK_ZAKAZKY_AKTIVNI A
      join ZAK_ROZPISKY R on R.ZAKAZKA = A.ZAKAZKA
      join ZAK_DODAVKY_OBSAH ZDO on ZDO.DODAVKA = R.DODAVKA and ZDO.POLOZKA = R.POLOZKA_DODAVKY
      where ZDO.TYP_POLOZKY in ('I', 'M')
      group by 1,2
  ),
  ET_VS_MAT as (
    select M.PKID, min (T.TERMIN) as VS_TERMIN_ETAPY
      from ZAK_MATERIALY M 
      join ZAK_ZAKAZKY Z on Z.ZAKAZKA = M.ZAKAZKA
      left outer join ZAK_MONT_PRIK_POLOZKY MPP on M.ZAKAZKA = MPP.ZAKAZKA and M.VM_CISLO = MPP.MONTAZNI_CISLO and M.CISLO_OPERACE = 0
      left outer join ZAK_MONTAZNI_PRIKAZY MP on MP.ZAKAZKA = MPP.ZAKAZKA and MP.MONTAZNI_PRIKAZ = MPP.MONTAZNI_PRIKAZ
      left outer join ZAK_ROZPISKY R on R.ZAKAZKA = MPP.ZAKAZKA and R.MONTAZNI_CISLO = MPP.MONTAZNI_CISLO
      left outer join ZAK_ROZPISKY RR on R.ZAKAZKA = RR.ZAKAZKA and R.MONTAZNI_CISLO_RODIC = RR.MONTAZNI_CISLO
      left outer join ZAR_KODY_SKUPIN RKS on RKS.KOD = RR.KOD_SKUPINY
      left outer join ZAK_TERMIN_VS (M.ZAKAZKA, coalesce(MP.PODSKUPINA,0),'N') T on 1=1
      where M.CISLO_OPERACE = 0 and 
            M.ODEVZDANO = '*' and 
            M.ZRUSENO = '' and 
            M.MNOZSTVI_VYDANO < M.MNOZSTVI_MATERIALU 
            --VO na J zakazkach musim pocitat jinde
            and not (Z.DRUH_ZAKAZKY = 'J' and RKS.POSTUP = '*')                 
      group by M.PKID

  ),
  TERMIN_O_ROZPISKY as  (
    select  R.ZAKAZKA, R.MONTAZNI_CISLO, 
            min (RR.TERMIN_ZAHAJENI) as TERMIN_NADRAZENE 
      from ZAK_ZAKAZKY_AKTIVNI Z 
      join ZAK_ROZPISKY R  on R.ZAKAZKA = Z.ZAKAZKA
      join ZAK_ROZPISKY RR on R.ZAKAZKA = RR.ZAKAZKA and R.MONTAZNI_CISLO_RODIC = RR.MONTAZNI_CISLO
      group by 1,2
  )
  select M.PKID,M.NAZEV_MATERIALU, T.O_TERMIN_SKUTECNY TERMIN, VR.TERMIN_MAT_LPST, 
    cast(case 
           when RT.RUCNI_TERMIN_POZADOVANY is not null then 'R'
           when VR.TERMIN_MATERIALU is not null then 'VR'
           when ID.TERMIN_INDIVIDUALNI is not null then 'I'
           when EVM.VS_TERMIN_ETAPY is not null then 'E'
           when ZR.TERMIN_NADRAZENE is not null then 'TN'
           else 'XX'
         end as TEXT2) as TERMIN_MATERIALU_DRUH
          from ZAK_ZAKAZKY_AKTIVNI Z
          join ZAK_MATERIALY M on M.ZAKAZKA = Z.ZAKAZKA
          left outer join RUCNI_OZ_TERMIN RT on RT.ZAKAZKA = M.ZAKAZKA and RT.MONTAZNI_CISLO = M.VM_CISLO and RT.RUCNI_TERMIN_POZADOVANY is not null
          left outer join TERM_V_ROZPISKY VR on RT.RUCNI_TERMIN_POZADOVANY is null and (Z.VYROBNI_ZAKAZKA = '*' or Z.JEDNOUCELOVA_ZAKAZKA = '*') and VR.ZAKAZKA = M.ZAKAZKA and VR.MONTAZNI_CISLO = M.VM_CISLO
          left outer join INDIVIDUALNI_DIL ID on RT.RUCNI_TERMIN_POZADOVANY is null and VR.TERMIN_MATERIALU is null and ID.ZAKAZKA = M.ZAKAZKA and ID.MONTAZNI_CISLO = M.VM_CISLO
          left outer join ET_VS_MAT EVM on RT.RUCNI_TERMIN_POZADOVANY is null and VR.TERMIN_MATERIALU is null and ID.TERMIN_INDIVIDUALNI is null and EVM.PKID = M.PKID 
          left outer join TERMIN_O_ROZPISKY ZR on RT.RUCNI_TERMIN_POZADOVANY is null and VR.TERMIN_MATERIALU is null and ID.TERMIN_INDIVIDUALNI is null and 
                          EVM.VS_TERMIN_ETAPY is null and M.ZAKAZKA = ZR.ZAKAZKA  and M.VM_CISLO = ZR.MONTAZNI_CISLO
          --JH20190118 get_termin_skutecny volam dvakrat, -+1den abych mel jistotu, ze termin objednani je na pracovni den soma
          left outer join GET_TERMIN_SKUTECNY ((select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY (coalesce (RT.RUCNI_TERMIN_POZADOVANY, VR.TERMIN_MATERIALU, ID.TERMIN_INDIVIDUALNI, EVM.VS_TERMIN_ETAPY, ZR.TERMIN_NADRAZENE), -1)),1) T on 1=1
          where M.CISLO_OPERACE = 0 and 
                M.ODEVZDANO = '*' and 
                M.ZRUSENO = '' and 
                M.MNOZSTVI_VYDANO < M.MNOZSTVI_MATERIALU and (0 = :i_zakazka or M.ZAKAZKA = :i_zakazka)
    ) RM 
  on M.PKID = RM.PKID and ((cast (M.TERMIN_MATERIALU as date) is distinct from cast (RM.TERMIN as date)) or 
                           (cast (M.TERMIN_LPST as date) is distinct from cast (RM.TERMIN_MAT_LPST as date)) or 
                            (M.TERMIN_MATERIALU_DRUH is distinct from RM.TERMIN_MATERIALU_DRUH)
                          ) 
  when matched then update set M.TERMIN_MATERIALU = cast (RM.TERMIN as date), M.TERMIN_LPST = RM.TERMIN_MAT_LPST, M.TERMIN_MATERIALU_DRUH = RM.TERMIN_MATERIALU_DRUH;
--JH20180424 - prvni verze, datum nemuze jit do minulosti
--when matched then update set M.TERMIN_MATERIALU = iif (RM.TERMIN < current_date, current_date, cast (RM.TERMIN as date)), M.TERMIN_LPST = RM.TERMIN_MAT_LPST;

  -- Material vstupujici do operaci.
  /* Nejprve se bere v uvahu termin zahajeni operace na zakazce/vyrobni cislo u materialu vstupujiciho do operace vyrobni zakazky
     potom beru terminy MP u materialu vstupujiciho do OZ , ktere maji MP - 5 pracovních dnů
     potom beru termín etapy 65
     nakonec vezmu termin zahajeni 
  */
  merge into ZAK_MATERIALY M
  using 
    (with TERM_V_DILU as (
      select P.ZAKAZKA, P.VYROBNI_CISLO, P.CISLO_OPERACE, 
             min (minvalue (coalesce(P.TERMIN_ZAHAJENI, P.TERMIN_LPST),(P.TERMIN_LPST - :xpredstih_nakup_vd_lpst))) as TERMIN_MATERIALU, 
             min (P.TERMIN_LPST) as TERMIN_MAT_LPST from ZAK_ZAKAZKY_AKTIVNI A
        join ZAK_POSTUPY P on P.ZAKAZKA = A.ZAKAZKA
        join ZAK_ROZPISKY R on R.ZAKAZKA = P.ZAKAZKA and R.VYROBNI_CISLO = P.VYROBNI_CISLO
        where not (A.JEDNOUCELOVA_ZAKAZKA = '*' and R.KOD_SKUPINY = 'VM')
        group by 1,2,3 
    ), ET_VS_MAT as (
      select M.PKID, min(T.TERMIN) as VS_TERMIN_ETAPY
           from ZAK_MATERIALY M 
             join ZAK_ZAKAZKY Z on Z.ZAKAZKA = M.ZAKAZKA
             left outer join ZAK_MONT_PRIK_POLOZKY MPP on M.ZAKAZKA = MPP.ZAKAZKA and M.VM_CISLO = MPP.MONTAZNI_CISLO and M.CISLO_OPERACE = 0
             left outer join ZAK_MONTAZNI_PRIKAZY MP on MP.ZAKAZKA = MPP.ZAKAZKA and MP.MONTAZNI_PRIKAZ = MPP.MONTAZNI_PRIKAZ
             left outer join ZAK_ROZPISKY R on R.ZAKAZKA = MPP.ZAKAZKA and R.MONTAZNI_CISLO = MPP.MONTAZNI_CISLO
             left outer join ZAK_ROZPISKY RR on R.ZAKAZKA = RR.ZAKAZKA and R.MONTAZNI_CISLO_RODIC = RR.MONTAZNI_CISLO
             left outer join ZAR_KODY_SKUPIN RKS on RKS.KOD = RR.KOD_SKUPINY
             left outer join ZAK_TERMIN_VS (M.ZAKAZKA, coalesce(MP.PODSKUPINA,0),'N') T on 1=1
           where M.CISLO_OPERACE > 0 and 
                 M.ODEVZDANO = '*' and 
                 M.ZRUSENO = '' and 
                 M.MNOZSTVI_VYDANO < M.MNOZSTVI_MATERIALU 
      group by M.PKID
    ),TERMIN_O_ROZPISKY as  (
      select  R.ZAKAZKA, R.VYROBNI_CISLO, 
              min (R.TERMIN_ZAHAJENI) as TERMIN_ROZPISKY 
        from ZAK_ZAKAZKY_AKTIVNI Z 
        join ZAK_ROZPISKY R  on R.ZAKAZKA = Z.ZAKAZKA
        group by 1,2
    )  

     select M.PKID, M.ZAKAZKA, M.VM_CISLO, M.CISLO_OPERACE, TS.O_TERMIN_SKUTECNY as TERMIN , VD.TERMIN_MAT_LPST,
       cast (case 
               when VD.TERMIN_MATERIALU is not null then 'VR'
               when EVM.VS_TERMIN_ETAPY is not null then 'E'
               when ZR.TERMIN_ROZPISKY  is not null then 'TN'
               else 'XX'
             end as TEXT2) as TERMIN_MATERIALU_DRUH

            from ZAK_ZAKAZKY_AKTIVNI Z
            join ZAK_MATERIALY M on M.ZAKAZKA = Z.ZAKAZKA        
            left outer join TERM_V_DILU VD on (Z.VYROBNI_ZAKAZKA = '*' or Z.JEDNOUCELOVA_ZAKAZKA = '*') and VD.ZAKAZKA = M.ZAKAZKA and VD.VYROBNI_CISLO = M.VM_CISLO and VD.CISLO_OPERACE = M.CISLO_OPERACE
            left outer join ET_VS_MAT EVM on VD.TERMIN_MATERIALU is null  and M.PKID = EVM.PKID 
            left outer join TERMIN_O_ROZPISKY ZR on VD.TERMIN_MATERIALU is null  and EVM.VS_TERMIN_ETAPY is null 
                                                    and ZR.ZAKAZKA = M.ZAKAZKA and ZR.VYROBNI_CISLO = M.VM_CISLO
            --JH20190118 get_termin_skutecny volam dvakrat, -+1den abych mel jistotu, ze termin objednani je na pracovni den soma
            left outer join GET_TERMIN_SKUTECNY ((select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY (coalesce (VD.TERMIN_MATERIALU,EVM.VS_TERMIN_ETAPY, ZR.TERMIN_ROZPISKY), -1)),1) TS on 1=1
            where M.CISLO_OPERACE > 0 and 
                  M.ODEVZDANO = '*' and 
                  M.ZRUSENO = '' and M.MNOZSTVI_VYDANO < M.MNOZSTVI_MATERIALU and (0 = :i_zakazka or M.ZAKAZKA = :i_zakazka)
    )RM 
  on M.PKID = RM.PKID and ((cast (M.TERMIN_MATERIALU as date) is distinct from cast (RM.TERMIN as date)) or 
                           (cast (M.TERMIN_LPST as date) is distinct from cast (RM.TERMIN_MAT_LPST as date)) or 
                           (M.TERMIN_MATERIALU_DRUH is distinct from RM.TERMIN_MATERIALU_DRUH)
                          )   
  when matched then update set M.TERMIN_MATERIALU = cast (RM.TERMIN as date), M.TERMIN_LPST = RM.TERMIN_MAT_LPST, M.TERMIN_MATERIALU_DRUH = RM.TERMIN_MATERIALU_DRUH;
  --JH20180424 - prvni verze, datum nemuze jit do minulosti
  --when matched then update set M.TERMIN_MATERIALU = iif (RM.TERMIN < current_date, current_date, cast (RM.TERMIN as date)), M.TERMIN_LPST = RM.TERMIN_MAT_LPST;
end
^



create procedure MTZ_ZASOBY_NOVY_VYPOCET as begin exception bp; end^

alter procedure MTZ_ZASOBY_NOVY_VYPOCET
returns (CISLO_VYPOCTU UCETNI_DOKLAD)
as
  declare xvykres             CISLO_VYKRESU;
  declare xdruh_stroje        DRUH_STROJE;
  declare xzakazka            UCETNI_DOKLAD;
  declare xvyrobni_cislo      VYROBNI_VYKRES;
  declare xmontazni_cislo     VYROBNI_VYKRES;
  declare xmnozstvi           MNOZSTVI;
  declare xmnozstvi_fakturovano     MNOZSTVI;
  declare xmnozstvi_rozpiska  MNOZSTVI;
  declare xmnozstvi_celkem    MNOZSTVI;
  declare xmnozstvi_zbyva     MNOZSTVI;
  declare xcena               CENA;
  declare xminimalni_zasoba   MNOZSTVI;
  
  declare zasoba_zakaznik       CENA;
  declare zasoba_soma           CENA;
  declare zasoba_ostatni        CENA;
  declare zasoba_nezarazeno     CENA;
  declare zasoba_minimum        CENA;
  declare xico                  varchar(11);
  declare xnakupovany_dil       NAKUPOVANY_DIL;
  declare xnazev_materialu      NAZEV_MATERIALU;
  declare xid_dilu              UCETNI_DOKLAD;
  declare xhodnota              CENA;
  declare xhodnota_celkem       CENA;
  declare xhodnota_zbyva        CENA;
  declare xpomer                double precision; 
  declare xsklad                TEXT30;
begin

  --vytvorim zaznam v tabulce MTZ_ZASOBY_VYPOCET
  insert into MTZ_ZASOBY_VYPOCTY default values
    returning CISLO_VYPOCTU
    into :cislo_vypoctu;

  --postupne naplnim jednotlive 3 tabulky
  
  --OBROBNA
  for select R.VYKRES, coalesce (DA.DRUH_STROJE, ''), R.ZAKAZKA, R.VYROBNI_CISLO, R.MNOZSTVI - (R.MNOZSTVI_PREDANO_NA_SKLAD + R.MNOZSTVI_PRIJEM_ZE_SKLADU) MNOZSTVI, 
        coalesce (sum (VDO.HODINY_PM + VDO.HODINY_PN + VDO.HODINY_VR + VDO.MATERIAL + VDO.KOOPERACE + VDO.KOOPERACE_KAPACITNI 
               + VDO.KOOPERACE_MATERIAL + VDO.KOOPERACE_KAPACITNI_MATERIAL + VDO.OSTATNI_REZIE), 0)
        from ZAK_ROZPISKY R
        join ZAK_ZAKAZKY Z on Z.ZAKAZKA = R.ZAKAZKA and Z.DRUH_ZAKAZKY = 'V'
        left outer join ZAR_VYKRESY V on R.VYKRES = V.VYKRES and V.ZRUSENO = ''
        left outer join ZAR_DILY_ATRIBUTY DA on DA.TYP_DILU = 'V' and DA.ID_DILU = V.ID_VYRABENY_DIL
        
        join ZAR_KODY_SKUPIN KS2 on KS2.KOD = R.KOD_SKUPINY and KS2.SYSTEMOVY_KOD <> '*'
        --rodic je systemovy kod
        left outer join ZAK_ROZPISKY RODIC on RODIC.ZAKAZKA = R.ZAKAZKA and RODIC.MONTAZNI_CISLO = R.MONTAZNI_CISLO_RODIC
        join ZAR_KODY_SKUPIN KS on KS.KOD = RODIC.KOD_SKUPINY and KS.SYSTEMOVY_KOD = '*'
        --ocenim vcetne deti
        left outer join ZAK_VYR_DIL_OCENENI(R.ZAKAZKA, R.MONTAZNI_CISLO, R.MNOZSTVI - (R.MNOZSTVI_PREDANO_NA_SKLAD + R.MNOZSTVI_PRIJEM_ZE_SKLADU)) VDO on 1=1


        where /*RODIC.ODEVZDANO = '' and*/ R.ODEVZDANO = '*' and R.STORNOVANO = '' and Z.UCETNI_OBDOBI_ODVOD = ''
          --nesmi existovat pohyb na SKLAD
          --TB muze! and not exists (select first 1 1 from ZAK_VYROBENE_DILY_POHYBY DP where DP.ZAKAZKA_PRIJEM = R.ZAKAZKA and DP.MONTAZNI_CISLO_PRIJEM = R.MONTAZNI_CISLO)
          and R.MNOZSTVI - (R.MNOZSTVI_PREDANO_NA_SKLAD + R.MNOZSTVI_PRIJEM_ZE_SKLADU) > 0
        group by R.VYKRES, DA.DRUH_STROJE, R.ZAKAZKA, R.VYROBNI_CISLO, R.MNOZSTVI, R.MNOZSTVI_PREDANO_NA_SKLAD, R.MNOZSTVI_PRIJEM_ZE_SKLADU
        into :xvykres, :xdruh_stroje, :xzakazka, :xvyrobni_cislo, :xmnozstvi, :xhodnota
  do begin
    zasoba_zakaznik = 0;
    zasoba_soma = 0;
    zasoba_ostatni = 0;
    zasoba_nezarazeno = 0;

    --zjistim kam to mam zaradit podle cisla vyrobni zakazky (45536724 = SOMA)
    select coalesce (max (OP.ICO), '')
      from ZAK_ZAKAZKY Z 
      left outer join ODB_DODAVKY D on D.ZAKAZKA = Z.ZAKAZKA
      left outer join ODB_OBCHODNI_PRIPADY OP on OP.OBCHODNI_PRIPAD = D.OBCHODNI_PRIPAD
      where Z.ZAKAZKA_VYROBNI = :xzakazka and :xzakazka > 0
      into :xico;

    if (xico = '45536724') then
    begin
      zasoba_soma = :xhodnota;
    end else if (xico > '') then
    begin
      zasoba_zakaznik = :xhodnota;
    end else begin
      zasoba_ostatni = :xhodnota;
    end
    
    insert into MTZ_ZASOBY_OBROBNA (CISLO_VYPOCTU, VYKRES, DRUH_STROJE, ZAKAZKA, VYROBNI_CISLO, MNOZSTVI, ZASOBA_ZAKAZNIK, ZASOBA_SOMA, ZASOBA_BEZ_ZAKAZNIKA)
      values (:cislo_vypoctu, :xvykres, :xdruh_stroje, :xzakazka, :xvyrobni_cislo, :xmnozstvi, :zasoba_zakaznik, :zasoba_soma, :zasoba_ostatni);
  end
  
  --SKLAD NAKUPOVANYCH
  --N dily - rozdelim podle prirazenych terminu potreby
  for select coalesce (DA.DRUH_STROJE, '') DRUH_STROJE, M.NAZEV_MATERIALU, M.NAKUPOVANY_DIL, M.ID_NAKUPOVANY_DIL, cast (coalesce (sum(SK.MNOZSTVI / coalesce (M.KOEFICIENT_JEDNOTKY, 0)), 0) as MNOZSTVI) MNOZSTVI, 
          coalesce (sum (SK.KC), 0) KC, list(distinct SK.SKLAD) SKLAD
        from MTZ_MATERIAL M
        join MTZ_SKLADOVE_KARTY SK on SK.NAZEV_MATERIALU = M.NAZEV_MATERIALU
        left outer join ZAR_DILY_ATRIBUTY DA on M.ID_NAKUPOVANY_DIL = DA.ID_DILU and DA.TYP_DILU = 'N'
        where SK.UCETNI_OBDOBI_VYDEJ = ''
        group by DA.DRUH_STROJE, M.NAZEV_MATERIALU, M.NAKUPOVANY_DIL, M.ID_NAKUPOVANY_DIL
        into :xdruh_stroje, :xnazev_materialu, :xnakupovany_dil, :xid_dilu, :xmnozstvi, :xhodnota_celkem, :xsklad
  do begin
    zasoba_zakaznik = 0;
    zasoba_soma = 0;
    zasoba_ostatni = 0;
    zasoba_nezarazeno = 0;
    zasoba_minimum = 0;

    if (xmnozstvi > 0 and xhodnota_celkem > 0) then
    begin
      xhodnota_zbyva = xhodnota_celkem;
      --postupne projdu pozadavky ze zakazek a co zbyde pošlu do nezarazenych
      for select M.ZAKAZKA, cast ((:xhodnota_celkem / :xmnozstvi) * M.MNOZSTVI_MATERIALU as CENA)
            from ZAK_MATERIALY_POZADAVKY (:xnazev_materialu) M
            where M.ODEVZDANO = '*' and M.VYDANO = '' and M.ZAJISTENI = 'S'
              and :xnakupovany_dil <> 'H'
          union all
          select H.ZAKAZKA,  cast ((:xhodnota_celkem / :xmnozstvi) * H.MNOZSTVI_MATERIALU as CENA)
            from ZAK_MATERIALY_POZADAVKY_HUTNI(:xnazev_materialu, '') H
            where H.ZAJISTENI = 'S' and :xnakupovany_dil = 'H'
            into :xzakazka, :xhodnota
      do begin
        xhodnota_zbyva = xhodnota_zbyva - xhodnota;
        xico = '';
        if (xzakazka = 0) then
        begin
          zasoba_minimum = zasoba_minimum + :xhodnota;
        end else begin 
          select coalesce (max (OP.ICO), '')
            from ZAK_ZAKAZKY Z 
            left outer join ODB_DODAVKY D on D.ZAKAZKA = Z.ZAKAZKA
            left outer join ODB_OBCHODNI_PRIPADY OP on OP.OBCHODNI_PRIPAD = D.OBCHODNI_PRIPAD
            where (Z.ZAKAZKA_VYROBNI = :xzakazka or Z.ZAKAZKA = :xzakazka) and :xzakazka > 0
            into :xico;

          if (xico = '45536724') then
          begin
            zasoba_soma = zasoba_soma + :xhodnota;
          end else if (xico > '') then
          begin
            zasoba_zakaznik = zasoba_zakaznik + :xhodnota;
          end else begin
            zasoba_ostatni = zasoba_ostatni + :xhodnota;
          end
        end
      end
      if (xhodnota_zbyva > 0) then
      begin
        zasoba_nezarazeno = xhodnota_zbyva;
      end 
      insert into MTZ_ZASOBY_SKLADY (CISLO_VYPOCTU, TYP_DILU, ID_DILU, DRUH_STROJE, MNOZSTVI, ZASOBA_ZAKAZNIK, ZASOBA_SOMA, ZASOBA_BEZ_ZAKAZNIKA, ZASOBA_NEZARAZENO, ZASOBA_MINIMUM_SKLADU, SKLAD)
        values (:cislo_vypoctu, 'N', :xid_dilu, :xdruh_stroje, :xmnozstvi, :zasoba_zakaznik, :zasoba_soma, :zasoba_ostatni, :zasoba_nezarazeno, :zasoba_minimum, :xsklad);
    end
  end


  --SKLAD VYRABENYCH
  for select coalesce (DA.DRUH_STROJE, '') DRUH_STROJE, V.VYKRES, V.ID_VYRABENY_DIL, sum(DP.MNOZSTVI) MNOZSTVI, sum (DP.HODINY_PM + DP.HODINY_PN + DP.MATERIAL + DP.KOOPERACE + DP.KOOPERACE_KAPACITNI + DP.KOOPERACE_KAPACITNI_MATERIAL + DP.KOOPERACE_MATERIAL + DP.HODINY_VR) KC,
          coalesce (max (V.MINIMALNI_ZASOBA), 0)
        from ZAK_VYROBENE_DILY_POHYBY DP
        left outer join ZAR_VYKRESY V on V.VYKRES = DP.VYKRES and V.ZRUSENO = ''
        left outer join ZAR_DILY_ATRIBUTY DA on DA.TYP_DILU = 'V' and DA.ID_DILU = V.ID_VYRABENY_DIL
        where DP.UCETNI_OBDOBI_VYDEJ = '' and V.ZRUSENO = ''
        group by DA.DRUH_STROJE, V.VYKRES, V.ID_VYRABENY_DIL
        having sum(DP.MNOZSTVI) > 0
        into :xdruh_stroje, :xvykres, :xid_dilu, :xmnozstvi, :xhodnota_celkem, :xminimalni_zasoba
  do begin
    zasoba_zakaznik = 0;
    zasoba_soma = 0;
    zasoba_ostatni = 0;
    zasoba_nezarazeno = 0;
    zasoba_minimum = 0;
    
    xmnozstvi_zbyva = :xmnozstvi;
    --V dily na sklade - vezmu podle toho kam patri zakazka ktera je na sklad dala
    for select R.ZAKAZKA,  (R.MNOZSTVI - R.MNOZSTVI_DOKONCENO)
          from ZAK_ROZPISKY R
          join ZAK_ZAKAZKY Z on Z.ZAKAZKA = R.ZAKAZKA
          join ZAK_ZAKAZKY_DRUHY ZD on ZD.DRUH_ZAKAZKY = Z.DRUH_ZAKAZKY and ZD.ROZPRACOVANA_VYROBA = '*' and ZD.VYROBNI_ZAKAZKA = ''
          where R.VYKRES = :xvykres and R.POZADAVEK_VD = '*' and R.DOKONCENO = '' and R.STORNOVANO = ''
          --group by R.ZAKAZKA
          order by (R.TERMIN_DOKONCENI)
          into :xzakazka, :xmnozstvi_rozpiska
    do begin
      xmnozstvi_rozpiska = minvalue (xmnozstvi_zbyva, :xmnozstvi_rozpiska);
      xpomer = :xmnozstvi_rozpiska / xmnozstvi;
      xmnozstvi_zbyva = xmnozstvi_zbyva - xmnozstvi_rozpiska;
      
      select coalesce (max (OP.ICO), '')
        from ZAK_ZAKAZKY Z 
        left outer join ODB_DODAVKY D on D.ZAKAZKA = Z.ZAKAZKA
        left outer join ODB_OBCHODNI_PRIPADY OP on OP.OBCHODNI_PRIPAD = D.OBCHODNI_PRIPAD
        where (Z.ZAKAZKA_VYROBNI = :xzakazka or Z.ZAKAZKA = :xzakazka) and :xzakazka > 0
        into :xico;
      
      if (xico = '45536724') then
      begin
        zasoba_soma = zasoba_soma + (:xhodnota_celkem * :xpomer);
      end else if (xico > '') then
      begin
        zasoba_zakaznik = zasoba_zakaznik + (:xhodnota_celkem * :xpomer);
      end else begin
        zasoba_ostatni = zasoba_ostatni + (:xhodnota_celkem * :xpomer);
      end 
    end
    
    if (xmnozstvi_zbyva > 0) then
    begin
      --xmnozstvi_rozpiska = minvalue (xmnozstvi_zbyva, :xminimalni_zasoba);
      xminimalni_zasoba = minvalue (xmnozstvi_zbyva, :xminimalni_zasoba);
      
      xpomer = :xminimalni_zasoba / :xmnozstvi;
      zasoba_minimum = xpomer * xhodnota_celkem;
      
      xmnozstvi_zbyva = xmnozstvi_zbyva - xminimalni_zasoba;
      if (xmnozstvi_zbyva > 0) then
      begin
        zasoba_nezarazeno = :xhodnota_celkem - zasoba_soma - zasoba_zakaznik - zasoba_ostatni - zasoba_minimum;
      end
    end
    insert into MTZ_ZASOBY_SKLADY (CISLO_VYPOCTU, TYP_DILU, ID_DILU, DRUH_STROJE, MNOZSTVI, ZASOBA_ZAKAZNIK, ZASOBA_SOMA, ZASOBA_BEZ_ZAKAZNIKA, ZASOBA_NEZARAZENO, ZASOBA_MINIMUM_SKLADU, SKLAD)
      values (:cislo_vypoctu, 'V', :xid_dilu, :xdruh_stroje, :xmnozstvi, :zasoba_zakaznik, :zasoba_soma, :zasoba_ostatni, :zasoba_nezarazeno, :zasoba_minimum, 'V'); 
  end
 
  --zasoba MONTAZ
  for select coalesce (iif (DS.DRUH_STROJE starting '1N', '1N', iif (DS.DRUH_STROJE starting '1P', '1P', iif (DS.KOMPLETNI_STROJ <> '*', '', DS.DRUH_STROJE))), '') DRUH_STROJE, DP.ZAKAZKA_VYDEJ, 
        DP.MONTAZNI_CISLO_VYDEJ, 0 VYROBNI_CISLO,
          coalesce (sum(DP.MATERIAL + DP.HODINY_PM + DP.HODINY_PN + DP.HODINY_VR + DP.KOOPERACE + DP.KOOPERACE_KAPACITNI + DP.KOOPERACE_KAPACITNI_MATERIAL
          + DP.KOOPERACE_MATERIAL + DP.OSTATNI_REZIE), 0) KC
        from ZAK_VYROBENE_DILY_POHYBY DP 
        join ZAK_ZAKAZKY Z on Z.ZAKAZKA = DP.ZAKAZKA_VYDEJ and Z.UCETNI_OBDOBI_ODVOD = ''
        join ZAK_ZAKAZKY_DRUHY ZD on ZD.DRUH_ZAKAZKY = Z.DRUH_ZAKAZKY and ZD.ROZPRACOVANA_VYROBA = '*' and ZD.VYROBNI_ZAKAZKA = ''
        left outer join ZAK_DRUHY_STROJU DS on DS.DRUH_STROJE = Z.DRUH_STROJE
        left outer join ZAK_ROZPISKY R on R.ZAKAZKA = DP.ZAKAZKA_VYDEJ and R.MONTAZNI_CISLO = DP.MONTAZNI_CISLO_VYDEJ
        where DP.ZAKAZKA_VYDEJ > 0 and R.STORNOVANO = ''
        group by DS.DRUH_STROJE, DS.KOMPLETNI_STROJ, DP.ZAKAZKA_VYDEJ, DP.MONTAZNI_CISLO_VYDEJ

      union all
  
      select coalesce (iif (DS.DRUH_STROJE starting '1N', '1N', iif (DS.DRUH_STROJE starting '1P', '1P', iif (DS.KOMPLETNI_STROJ <> '*', '', DS.DRUH_STROJE))), '') DRUH_STROJE, 
          Z.ZAKAZKA, iif (MV.CISLO_OPERACE = 0, MV.VM_CISLO, 0), iif (MV.CISLO_OPERACE > 0, MV.VM_CISLO, 0), sum(MV.CENA) KC
        from ZAK_ZAKAZKY Z 
        join ZAK_ZAKAZKY_DRUHY ZD on ZD.DRUH_ZAKAZKY = Z.DRUH_ZAKAZKY and ZD.ROZPRACOVANA_VYROBA = '*' and ZD.VYROBNI_ZAKAZKA = ''
        join ZAK_MATERIALY_VYDEJ MV on MV.ZAKAZKA = Z.ZAKAZKA and MV.DOKLAD_VYDEJ > 0
        left outer join ZAK_DRUHY_STROJU DS on DS.DRUH_STROJE = Z.DRUH_STROJE
        where  Z.UCETNI_OBDOBI_ODVOD = ''
        group by DS.DRUH_STROJE, DS.KOMPLETNI_STROJ, Z.ZAKAZKA, MV.VM_CISLO, MV.CISLO_OPERACE
      union all

      select coalesce (iif (DS.DRUH_STROJE starting '1N', '1N', iif (DS.DRUH_STROJE starting '1P', '1P', iif (DS.KOMPLETNI_STROJ <> '*', '', DS.DRUH_STROJE))), ''), H.ZAKAZKA,
            0 MONTAZNI_CISLO, H.VYROBNI_CISLO,
            coalesce (sum (H.PM + H.PN + H.VR), 0) KC
        from ZAK_HODINY H 
        left outer join ZAK_VYKRESY V on V.ZAKAZKA = H.ZAKAZKA and V.VYROBNI_CISLO = H.VYROBNI_CISLO
        join ZAK_ZAKAZKY Z on Z.ZAKAZKA = H.ZAKAZKA and Z.UCETNI_OBDOBI_ODVOD = ''
        join ZAK_ZAKAZKY_DRUHY ZD on ZD.DRUH_ZAKAZKY = Z.DRUH_ZAKAZKY and ZD.ROZPRACOVANA_VYROBA = '*' and ZD.VYROBNI_ZAKAZKA = ''
        left outer join ZAR_VYKRESY ZARV on ZARV.VYKRES = V.VYKRES and ZARV.ZRUSENO = ''
        left outer join ZAK_DRUHY_STROJU DS on DS.DRUH_STROJE = Z.DRUH_STROJE
        where (H.VYROBNI_CISLO = 0 or V.PKID is not null) and H.KOOPERACE = ''
        group by DS.DRUH_STROJE, DS.KOMPLETNI_STROJ, H.ZAKAZKA, H.VYROBNI_CISLO

      union all

      select coalesce (iif (DS.DRUH_STROJE starting '1N', '1N', iif (DS.DRUH_STROJE starting '1P', '1P', iif (DS.KOMPLETNI_STROJ <> '*', '', DS.DRUH_STROJE))), '') DRUH_STROJE, VP.ZAKAZKA,
          0 MONTAZNI_CISLO, VP.VYROBNI_CISLO,
          coalesce (sum (VP.CENA + VP.CENA_MATERIAL + VP.CENA_OSTATNI_REZIE), 0) KC
        from ZAK_VYROBNI_PRIKAZY VP 
        join ZAK_VYKRESY V on V.ZAKAZKA = VP.ZAKAZKA and V.VYROBNI_CISLO = VP.VYROBNI_CISLO
        join ZAK_ZAKAZKY Z on Z.ZAKAZKA = VP.ZAKAZKA and Z.UCETNI_OBDOBI_ODVOD = ''
        join ZAK_ZAKAZKY_DRUHY ZD on ZD.DRUH_ZAKAZKY = Z.DRUH_ZAKAZKY and ZD.ROZPRACOVANA_VYROBA = '*' and ZD.VYROBNI_ZAKAZKA = ''
        left outer join ZAK_DRUHY_STROJU DS on DS.DRUH_STROJE = Z.DRUH_STROJE
        where VP.KOOPERACE <> ''
        group by DS.DRUH_STROJE, DS.KOMPLETNI_STROJ, VP.ZAKAZKA, VP.VYROBNI_CISLO
    into :xdruh_stroje, :xzakazka, :xmontazni_cislo, :xvyrobni_cislo, :xhodnota
  do begin
    zasoba_zakaznik = 0;
    zasoba_soma = 0;
    zasoba_ostatni = 0;
    zasoba_nezarazeno = 0;

    --zjistim si mnozstvi a montazni_cislo (pokud neznam)
--     select first 1 MONTAZNI_CISLO, MNOZSTVI
--       from ZAK_ROZPISKY 
--       where (:xvyrobni_cislo > 0 and VYROBNI_CISLO = :xvyrobni_cislo) or (:xvyrobni_cislo = 0 and MONTAZNI_CISLO = :xmontazni_cislo)
--       into :xmontazni_cislo, :xmnozstvi;

    select coalesce (max (OP.ICO), ''), Z.MNOZSTVI, Z.MNOZSTVI_FAKTUROVANO
      from ZAK_ZAKAZKY Z 
      left outer join ODB_DODAVKY D on D.ZAKAZKA = Z.ZAKAZKA
      left outer join ODB_OBCHODNI_PRIPADY OP on OP.OBCHODNI_PRIPAD = D.OBCHODNI_PRIPAD
      where (Z.ZAKAZKA = :xzakazka) and :xzakazka > 0
      group by Z.MNOZSTVI, Z.MNOZSTVI_FAKTUROVANO
      into :xico, :xmnozstvi, :xmnozstvi_fakturovano;
      
    --hodnotu zasob ponizim o jiz vyfakturovane mnozstvi
    if (:xmnozstvi > 0 and :xmnozstvi_fakturovano > 0) then
    begin
      xhodnota = cast (xhodnota * ((xmnozstvi - xmnozstvi_fakturovano) / xmnozstvi) as CENA);
    end

    if (xico = '45536724') then
    begin
      zasoba_soma = :xhodnota;
    end else if (xico > '') then
    begin
      zasoba_zakaznik = :xhodnota;
    end else begin
      zasoba_ostatni = :xhodnota;
    end
    
    insert into MTZ_ZASOBY_MONTAZ (CISLO_VYPOCTU, DRUH_STROJE, ZAKAZKA, MONTAZNI_CISLO, VYROBNI_CISLO, ZASOBA_ZAKAZNIK, ZASOBA_SOMA, ZASOBA_BEZ_ZAKAZNIKA) 
      values (:cislo_vypoctu, :xdruh_stroje, :xzakazka, :xmontazni_cislo, :xvyrobni_cislo, :zasoba_zakaznik, :zasoba_soma, :zasoba_ostatni);
  end

  --pro rychlejsi zobrazeni agendy si na danem vypoctu spocitam aktualni soucty
  for select sum(PZ.ZASOBA_ZAKAZNIK) ZASOBA_ZAKAZNIK, sum(PZ.ZASOBA_SOMA) ZASOBA_SOMA, sum(PZ.ZASOBA_BEZ_ZAKAZNIKA) ZASOBA_BEZ_ZAKAZNIKA, 
                sum(PZ.ZASOBA_NEZARAZENO) ZASOBA_NEZARAZENO, sum(PZ.ZASOBA_MINIMUM_SKLADU) ZASOBA_MINIMUM_SKLADU
        from MTZ_ZASOBY_VYPOCTY ZV
        left outer join TISK_ZASOBY_PO_ZAKAZKACH(ZV.CISLO_VYPOCTU) PZ on 1=1
        where ZV.CISLO_VYPOCTU = :cislo_vypoctu
        into :zasoba_zakaznik, :zasoba_soma, :zasoba_ostatni, :zasoba_nezarazeno, :zasoba_minimum
  do begin
    update MTZ_ZASOBY_VYPOCTY
      set ZASOBA_ZAKAZNIK = :zasoba_zakaznik, ZASOBA_SOMA = :zasoba_soma, ZASOBA_BEZ_ZAKAZNIKA = :zasoba_ostatni, ZASOBA_NEZARAZENO = :zasoba_nezarazeno, ZASOBA_MINIMUM_SKLADU = :zasoba_minimum
      where CISLO_VYPOCTU = :cislo_vypoctu;
  end
  
  suspend; --odeslu cislo vypoctu
end
^


create procedure SALESMAN_FIRMY_OP as begin exit; end^

alter procedure SALESMAN_FIRMY_OP (
  i_uzivatel                  UZIVATEL,
  i_locale                    TEXT2,
  i_predpoklad_uspesnost      smallint default null,
  i_predpoklad_investice      smallint default null,
  i_skupina_firmy             varchar(3) default null,
  i_op_priorita               varchar(1) default null,
  i_op_aktualizace            varchar(1) default null,
  i_postup_op                 varchar(3) default null,
  i_druh_stroje               varchar(4) default null,
  i_zodpovedna_osoba          varchar(15) default null,
  i_stav_op                   varchar(1) default '',
  i_stat                      varchar(2) default null,
  i_obchodnik                 varchar(15) default null,
  i_ico                       varchar(11) default null
) returns (
  ico                         ICO,
  nazev                       TEXT60,
  mesto                       TEXT60,
  stat                        STAT,
  datum_posledni_komunikace   DATUM,
  datum_posledni_navstevy     DATUM,
  osoba_1                     TEXT100,
  osoba_2                     TEXT100,
  osoba_3                     TEXT100,
  nasledujici_akce            varchar(100),
  nasledujici_investice       varchar(7),
  predpoklad_uspesnost        PROCENTA,
  predpoklad_investice        PROCENTA,
  strojovy_park               TEXT1000,
  rok_instalace               POCET,
  skupina_firmy               SKUPINA_FIRMY,
  aktualizace                 LOGICAL2
)
as
  declare xpredpoklad_termin  DATUM;
  declare xstroj              PKID;
  declare xfunkce             TEXT30;
  declare xpocet_instalaci    POCET;
  declare xstroj_nazev        TEXT50_UTF;
  declare xrok_instalace      POCET;
  declare xhierarchie         TEXT50 = 'ZZZZZZ';
  declare xdummy              TEXT100;
  declare xsoma               LOGICAL2;
begin
  select iif(OBCHODNIK_ICO = '', '', '*')
    from SYS_PRACOVNICI
    where UZIVATEL = :i_uzivatel
    into :xsoma;
  if (xsoma = '') then
  begin
    i_uzivatel = coalesce(i_obchodnik, i_uzivatel);
    select CESTA
      from SYS_HIERARCHIE_ZAMESTNANCU
      where UZIVATEL = :i_uzivatel
      into :xhierarchie;
  end

  for select OP.ICO, F.NAZEV, F.MESTO, F.STAT, F.SKUPINA_FIRMY,
             --DATUM_POSLEDNI_KOMUNIKACE
             max((select max(coalesce(K.TERMIN, K.DATUM_ZAPISU)) from ODB_KONTAKTY K where K.OBCHODNI_PRIPAD = OP.OBCHODNI_PRIPAD and K.STAV_KONTAKTU = 'U' and K.FORMA_KONTAKTU in ('T', 'F', 'M', 'P', 'S'))),
             --DATUM_POSLEDNI_NAVSTEVY
             max((select max(coalesce(K.TERMIN, K.DATUM_ZAPISU)) from ODB_KONTAKTY K where K.OBCHODNI_PRIPAD = OP.OBCHODNI_PRIPAD and K.STAV_KONTAKTU = 'U' and K.FORMA_KONTAKTU in ('J', 'C'))),
         
             --NASLEDUJICI_AKCE
             max((select first 1 extract(YEAR from K.TERMIN) || '/' || cast(extract(WEEK from K.TERMIN) as VARCHAR(2)) || ' ' || iif(:i_locale = 'cs', KAT.POPIS, KAT.POPIS_US) || ' ' || F.FORMA_KONTAKTU
                    from ODB_KONTAKTY K
                    left outer join ODB_KONTAKTY_FORMY F on F.FORMA_KONTAKTU = K.FORMA_KONTAKTU
                    left outer join ODB_KONTAKTY_KATEGORIE KAT on KAT.KATEGORIE_KONTAKTU = K.KATEGORIE_KONTAKTU
                    where K.OBCHODNI_PRIPAD = OP.OBCHODNI_PRIPAD and K.TERMIN >= CURRENT_DATE
                    order by K.TERMIN)),
           
            -- Nasledujici investice
            max(OP.PREDPOKLAD_TERMIN), max(OP.PREDPOKLAD_USPESNOST), max(OP.PREDPOKLAD_INVESTICE),
            max(OP.AKTUALIZACE),
            -- Filters.
            min(OP.PRIORITA), min(OP.POSTUP_OP), min(OP.DRUH_STROJE), min(OP.ZODPOVEDNA_OSOBA), min(OP.STAV_OP)
        from ODB_OBCHODNI_PRIPADY OP
        join SYS_PRACOVNICI P on P.UZIVATEL = :i_uzivatel
        join ODB_FIRMY F on F.ICO = OP.ICO
        where P.UZIVATEL = :i_uzivatel and OP.STAV_OP = '' and OP.DATUM_UKONCENI is null and
              ((P.OBCHODNIK_ICO > '' and P.OBCHODNIK_ICO = OP.OBCHODNI_ZASTOUPENI) or
               (P.OBCHODNIK_ICO = '' and exists(select 1 from SYS_HIERARCHIE_ZAMESTNANCU H where H.UZIVATEL = OP.ZODPOVEDNA_OSOBA and H.CESTA starting :xhierarchie))) and
              -- Custom filters.
              (:i_ico is null or OP.ICO >= :i_ico) and
              (:i_predpoklad_uspesnost is null or OP.PREDPOKLAD_USPESNOST >= :i_predpoklad_uspesnost) and
              (:i_predpoklad_investice is null or OP.PREDPOKLAD_INVESTICE >= :i_predpoklad_investice) and
              (:i_skupina_firmy is null or F.SKUPINA_FIRMY = :i_skupina_firmy) and
              (:i_op_aktualizace is null or OP.AKTUALIZACE = :i_op_aktualizace) and
              (:i_op_priorita is null or OP.PRIORITA = :i_op_priorita) and
              (:i_postup_op is null or OP.POSTUP_OP = :i_postup_op) and
              (:i_druh_stroje is null or OP.DRUH_STROJE = :i_druh_stroje) and
              (:i_zodpovedna_osoba is null or OP.ZODPOVEDNA_OSOBA = :i_zodpovedna_osoba) and
              (:i_stav_op is null or OP.STAV_OP = :i_stav_op) and
              (:i_stat is null or F.STAT = :i_stat)
        group by OP.ICO, F.NAZEV, F.MESTO, F.STAT, F.SKUPINA_FIRMY
        into :ico, :nazev, :mesto, :stat, :skupina_firmy,
             :datum_posledni_komunikace, :datum_posledni_navstevy,
             :nasledujici_akce,
             :xpredpoklad_termin, :predpoklad_uspesnost, :predpoklad_investice,
             :aktualizace,
             :xdummy, :xdummy, :xdummy, :xdummy, :xdummy
  do begin
    -- 1. osoba
    osoba_1 = '';
    xfunkce = '';
    select first 1 PRIJMENI_JMENO,FUNKCE
      from ODB_FIRMY_OSOBY
      where ICO = :ico and DULEZITOST = '1'
      order by ZRUSENO, VIP desc, RECORD_CHANGE_DATE desc
      into osoba_1, xfunkce;
    if (xfunkce > '') then
    begin
      osoba_1 = osoba_1 || ' (' || xfunkce || ')';
    end
    -- 2. osoba
    osoba_2 = '';
    xfunkce = '';
    select first 1 PRIJMENI_JMENO, FUNKCE
      from ODB_FIRMY_OSOBY
      where ICO = :ico and DULEZITOST = '2'
      order by ZRUSENO, VIP desc, RECORD_CHANGE_DATE desc
      into osoba_2, xfunkce;
    if (xfunkce > '') then
    begin
      osoba_2 = osoba_2 || ' (' || xfunkce || ')';
    end
    -- 3. osoba
    osoba_3 = '';
    xfunkce = '';
    select first 1 PRIJMENI_JMENO, FUNKCE
      from ODB_FIRMY_OSOBY
      where ICO = :ico and DULEZITOST = '3'
      order by ZRUSENO, VIP desc, RECORD_CHANGE_DATE desc
      into osoba_3, xfunkce;
    if (xfunkce > '') then
    begin
      osoba_3 = osoba_3 || '(' || xfunkce || ')';
    end
     
    -- Nasledujici investice
    nasledujici_investice = extract(YEAR from xpredpoklad_termin) || ' ' ||
      case
        when extract(MONTH from xpredpoklad_termin) between 1 and 3 then '1'
        when extract(MONTH from xpredpoklad_termin) between 4 and 6 then '2'
        when extract(MONTH from xpredpoklad_termin) between 7 and 9 then '3'
        else '4'
      end || 'Q';
   
    -- Strojovy park.
    strojovy_park = '';
    rok_instalace = 0;
    for select VYR.VYROBCE, sum(STR.POCET_KS), max(STR.ROK_INSTALACE)
          from ODB_ZAKAZNIK_STROJE STR
          left outer join ODB_ZAKAZNIK_STROJ_VYROBCE(STR.STROJ) VYR on 1=1
          where STR.ICO = :ico
          group by VYR.VYROBCE
          into :xstroj_nazev, :xpocet_instalaci, :xrok_instalace
    do begin
      if (char_length(strojovy_park) > 0) then strojovy_park = strojovy_park || '; ';
      strojovy_park = strojovy_park || xpocet_instalaci || 'x ' || xstroj_nazev;
      if (xrok_instalace > 0 and rok_instalace < xrok_instalace and xstroj_nazev = 'SOMA') then rok_instalace = xrok_instalace;
    end
   
    suspend;
  end
end
^


create or alter procedure SYS_DATE_REPEAT (
  i_pocatek                       DATUM,
  i_opakovani                     varchar(1),
  i_rekurence                     POCET,
  i_dny                           POCET,
  i_ukonceni_datum                DATUM,
  i_ukonceni_vyskyty              POCET)
returns (
  termin                          DATUM)
as
  declare xvyskyty                POCET;
  declare xpocitadlo              POCET;
  declare xpocitadlo_tydny        POCET;
  declare xtyden                  POCET;
  declare xdatum_original         DATUM;
begin
  if (i_pocatek is null) then
  begin
    exit;
  end
  
  -- Prvni termin je vzdy termin v ODB_KONTAKTY.
  termin = i_pocatek;
  suspend;
  
  if (i_opakovani = '') then
  begin
    exit;
  end
  
  
  xvyskyty = i_ukonceni_vyskyty;
  xdatum_original = i_pocatek;
  while (1=1)
  do begin
    xvyskyty = xvyskyty - 1;
    execute procedure SYS_DATE_REPEAT_NEXT_DATE (xdatum_original, termin, i_opakovani, i_rekurence, i_dny)
      returning_values (termin);
    if ((i_ukonceni_datum is null and i_ukonceni_vyskyty = 0) or termin <= i_ukonceni_datum or xvyskyty > 0) then
    begin
      suspend;
    end else begin
      exit;
    end
    if (termin >= CURRENT_DATE) then
    begin
      xpocitadlo = xpocitadlo + 1;
    end
    
    -- Omezeni poctu zobrazeni.
    if (i_opakovani = 'R') then
    begin
      -- Opakovaci frekvence ROK.
      if (xpocitadlo = 2) then
      begin
        exit;
      end
    end else if (i_opakovani = 'M') then
    begin
      -- Opakovaci frekvence MESIC.
      if (xpocitadlo = 24) then
      begin
        exit;
      end
    end else if (i_opakovani = 'T') then
    begin
      -- Opakovaci frekvence TYDEN (dny v tydnu).
      if (xpocitadlo_tydny = 24) then
      begin
        exit;
      end else begin
        if (termin >= CURRENT_DATE and xtyden <> extract(WEEK from termin)) then
        begin
          xpocitadlo_tydny = xpocitadlo_tydny + 1;
          xtyden = extract(WEEK from termin);
        end
      end
    end else if (i_opakovani = 'D') then
    begin
      -- Opakovaci frekvence DEN.
      if (xpocitadlo = 180) then
      begin
        exit;
      end
    end
    
    if (xpocitadlo > 1000) then
    begin
      exception BP 'Prilis mnoho cyklu.';
    end
  end
end
^

create procedure ZAK_SOMA_UKAZATELE_VYPOCET as begin end^
alter procedure ZAK_SOMA_UKAZATELE_VYPOCET(i_datum DATUM)
as
  declare xnakup_pocet_nedodanych         POCET;
  declare xnakup_neobjednanych            POCET;
  --kooperacni mistnost 
  declare xkoop_pocet_prijatych           POCET;
  declare xkoop_pocet_vydanych            POCET;
  --kooperace
  declare xkoop_pocet_neprijatych         POCET;
  declare xkoop_pocet_neobjednanych_k     POCET;
  declare xkoop_pocet_neobjednanych_t     POCET;
  --nestandardy
  declare xprum_doba_reseni_zmetku_14     MNOZSTVI;
  declare xprum_doba_reseni_zmetku_30     MNOZSTVI;
  declare xprum_doba_reseni_zmetku_180    MNOZSTVI;
  declare xpocet_reklamacnich_protokolu   POCET;
  declare xpocet_neschvalenych_rekl_prot  POCET;
  --pocty neumistenych voziku
  declare xpocet_neum_is_pred_skl         POCET;
  declare xpocet_dilu_neum_is_pred_skl    POCET;
  declare xpocet_neum_is_pred_kontr       POCET;
  declare xpocet_dilu_neum_is_pred_kontr  POCET;
  --Kontrola min.
  declare xpocet_op_pred_kontr            POCET;
  declare xpocet_pred_kontr_dva_dny       POCET;
  declare xpocet_pred_kontr_zah_zitra     POCET;
  declare xpocet_pred_kontr_zah_pozit     POCET;
  declare xpocet_pred_kontr_bez_dva_dny   POCET;
  declare xpocet_pred_kontr_mont          POCET;
  declare xpocet_kontr_dnes               POCET;
  declare xpocet_kontr_dnes_zah_zitra     POCET;
  declare xpocet_kontr_dnes_zah_pozit     POCET;
  declare xpocet_kontr_dnes_bez_dva_dny   POCET;
  declare xpocet_melo_kontr               POCET;
  declare xpocet_melo_kontr_zah_zitra     POCET;
  declare xpocet_melo_kontr_zah_pozit     POCET;
  declare xpocet_melo_kontr_bez_dva_dny   POCET;
  --zmetky montaz
  declare xpocet_mont_zmetek              POCET;
  declare xpocet_mont_zmetek_vyreseno     POCET;
  declare xpocet_mont_zmetek_nevyreseno   POCET;
  --Kontrola bud.
  declare xpocet_fronta_obrobny_t0        POCET;
  declare xpocet_fronta_obrobny_t1        POCET;
  declare xpocet_fronta_obrobny_t2        POCET;
  declare xpocet_fronta_obrobny_t3        POCET;
  declare xpocet_fronta_obr_t0_zah_t0     POCET;
  declare xpocet_fronta_obr_t1_zah_t0     POCET;
  declare xpocet_fronta_obr_t2_zah_t0     POCET;
  declare xpocet_fronta_obr_t3_zah_t0     POCET;
  declare xpocet_fronta_obr_t0_zah_t1     POCET;
  declare xpocet_fronta_obr_t1_zah_t1     POCET;
  declare xpocet_fronta_obr_t2_zah_t1     POCET;
  declare xpocet_fronta_obr_t3_zah_t1     POCET;
  declare xpocet_fronta_koo_t0            POCET;
  declare xpocet_fronta_koo_t1            POCET;
  declare xpocet_fronta_koo_t2            POCET;
  declare xpocet_fronta_koo_t3            POCET;
  declare xpocet_fronta_koo_t0_zah_t0     POCET;
  declare xpocet_fronta_koo_t1_zah_t0     POCET;
  declare xpocet_fronta_koo_t2_zah_t0     POCET;
  declare xpocet_fronta_koo_t3_zah_t0     POCET;
  declare xpocet_fronta_koo_t0_zah_t1     POCET;
  declare xpocet_fronta_koo_t1_zah_t1     POCET;
  declare xpocet_fronta_koo_t2_zah_t1     POCET;
  declare xpocet_fronta_koo_t3_zah_t1     POCET;
  declare xpocet                          POCET;
  declare xtyden_bud                      POCET;
  declare xrok_bud                        POCET;
  declare xdatum_od                       date;
  declare xdatum_do                       date;
  --Uctarna
  declare xpocet_nd_prich_dnes            POCET;
  declare xpocet_nd_prich_fakturou_dnes   POCET;
  declare xpocet_nd_prich_zal_list_dnes   POCET;
  declare xpocet_nd_prich_fakt_isdoc_dnes POCET;
  declare xpocet_nd_prich_fakt_rucne_dnes POCET;
  declare xpocet_nd_prich_poznamkou_dnes  POCET;
  declare xpocet_nd_prich_nezapsano_dnes  POCET;
  declare xpocet_nd_celk_fakturou_dnes    POCET;
  declare xpocet_nd_celk_zal_list_dnes    POCET;
  declare xpocet_nd_celk_fakt_isdoc_dnes  POCET;
  declare xpocet_nd_celk_fakt_rucne_dnes  POCET;
  declare xpocet_nd_celk_poznamkou_dnes   POCET;
  declare xpocet_nd_celk_nezapsano        POCET;
  declare xpocet_faktur_zauctovane_dnes   POCET;
  declare xpocet_faktur_nezauctovane      POCET;
  --Obrobna 
  declare xpocet_pripr_nezad_dnes         POCET;       
  declare xpocet_pripr_nezad_zitra        POCET;
  declare xsuma_usporene_pripravy         POCET;
  declare xvzdalenost_nasl_prac_den       POCET;
begin
  if (i_datum is null) then
  begin
    i_datum = current_date;
  end

  --pocet polozek ktere nebyly prijaty na sklad ale mely byt dodany (jsou po terminu)
  select count(OP.PKID)
    from MTZ_OBJEDNAVKY O
    left outer join MTZ_OBJEDNAVKY_POLOZKY OP on OP.OBJEDNAVKA = O.OBJEDNAVKA
    left outer join MTZ_MATERIAL M on M.NAZEV_MATERIALU = OP.NAZEV_MATERIALU
    where O.DATUM_VYRIZENI is null and coalesce (OP.TERMIN_POTVRZENY, O.TERMIN, OP.TERMIN_POZADOVANY, O.TERMIN_POZADOVANY) <= :i_datum and OP.MNOZSTVI - OP.MNOZSTVI_PRIJATO > 0
      and O.ROK > 2015
    into :xnakup_pocet_nedodanych;

  --pocet neobjednanych polozek po terminu
  select count(*)
    from MTZ_POZADAVKY P
    join MTZ_MATERIAL M on M.NAZEV_MATERIALU = P.NAZEV_MATERIALU
    where P.CHYBI > 0 and P.TERMIN_OBJEDNANI < :i_datum and M.TYP_VYSKLADNENI not in ('A', 'B', 'C', 'D')
    into :xnakup_neobjednanych;

  --pocet prijatych / vydanych dilu z/do kooperace
  --prijate dily beru pouze pokud nejsou vstupujici do svarence na stejne objednavce
  select count(distinct VP.ZAKAZKA || 'V' || VP.VYROBNI_CISLO)
    from ZAK_VYROBNI_PRIKAZY VP
    left outer join ZAK_POSTUPY_GET_NASL_OP(VP.ZAKAZKA, VP.VYROBNI_CISLO, VP.CISLO_OPERACE) GNO on 1=1
    left outer join ZAK_VYROBNI_PRIKAZY NVP on NVP.ZAKAZKA = VP.ZAKAZKA and NVP.VYROBNI_CISLO = GNO.VYROBNI_CISLO and NVP.CISLO_OPERACE = GNO.CISLO_OPERACE
    where VP.KOOPERACE > '' and cast (VP.DATUM_ODVEDENI as date) = :i_datum and VP.OBJEDNAVKA <> coalesce(NVP.OBJEDNAVKA, 0)
    into :xkoop_pocet_prijatych;

  select count(distinct VP.ZAKAZKA || 'V' || VP.VYROBNI_CISLO)
    from ZAK_VYROBNI_PRIKAZY VP
    left outer join ZAK_POSTUPY_GET_PRED_OP(VP.ZAKAZKA, VP.VYROBNI_CISLO, VP.CISLO_OPERACE) GPO on 1=1
    left outer join ZAK_VYROBNI_PRIKAZY PVP on PVP.ZAKAZKA = VP.ZAKAZKA and PVP.VYROBNI_CISLO = GPO.VYROBNI_CISLO and PVP.CISLO_OPERACE = GPO.CISLO_OPERACE
    where cast (VP.DATUM_ZADANI as date) = :i_datum and VP.KOOPERACE > '' and PVP.PKID is not null
      and coalesce (PVP.OBJEDNAVKA, 0) <> VP.OBJEDNAVKA
    into :xkoop_pocet_vydanych;     

  --pocet neobjednanych K - Kapacitně odkloněna je operace alespon 7 pracovních dnu
  select count(distinct iif (P.NEZPRACOVANO = '*', P.ZAKAZKA || '/' || P.VYROBNI_CISLO, null)) NEOBJEDNANO
    from ZAK_ZAKAZKY_AKTIVNI ZA
    join ZAK_ZAKAZKY Z on Z.ZAKAZKA = ZA.ZAKAZKA
    join ZAK_POSTUPY P on P.ZAKAZKA = ZA.ZAKAZKA
    left outer join GET_TERMIN_SKUTECNY(cast (maxvalue(P.KOOPERACE_ZMENA,Z.DATUM_BLOKACE) as date), -7) KT on 1=1
    where P.ZRUSENO = '' and P.KOOPERACE > '' and P.KOOPERACE_TECHNOLOGICKA = '' and KT.O_TERMIN_SKUTECNY < :i_datum
        and P.KONTROLOVANO = '' and P.ODVEDENO = ''
    into :xkoop_pocet_neobjednanych_k;

  --pocet neobjednanych T - ve vyrobe alespon 7 dni s T
  select count(distinct iif (P.NEZPRACOVANO = '*', P.ZAKAZKA || '/' || P.VYROBNI_CISLO, null)) NEOBJEDNANO
      from ZAK_ZAKAZKY_AKTIVNI ZA
      join ZAK_ZAKAZKY Z on Z.ZAKAZKA = ZA.ZAKAZKA
      join ZAK_POSTUPY P on P.ZAKAZKA = ZA.ZAKAZKA
      left outer join GET_TERMIN_SKUTECNY(cast (maxvalue(P.KOOPERACE_ZMENA,Z.DATUM_BLOKACE) as date), -7) KT on 1=1
      where P.ZRUSENO = '' and P.KOOPERACE > '' and P.KOOPERACE_TECHNOLOGICKA = '*' and KT.O_TERMIN_SKUTECNY < :i_datum and P.KONTROLOVANO = '' and P.ODVEDENO = '' and P.PRIPRAVENO = '*'
            --neexistuje predchozi mene jak 3 dny kontrolovana
            and not exists (select * from ZAK_POSTUPY PRED where PRED.ZAKAZKA = P.ZAKAZKA and PRED.VYROBNI_CISLO = P.VYROBNI_CISLO and PRED.NASLEDUJICI_OPERACE = P.CISLO_OPERACE
                and (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(cast (PRED.DATUM_DOKONCENI as date), -3)) >= :i_datum)
            --neexistuje kontrola deti v poslednich 3 pracovnich dnech
            and not exists (select *
                          from ZAK_ROZPISKY R
                          left outer join ZAK_ROZPISKY DETI on DETI.ZAKAZKA = R.ZAKAZKA and DETI.MONTAZNI_CISLO_RODIC = R.MONTAZNI_CISLO
                          left outer join ZAK_POSTUPY PD on PD.ZAKAZKA = DETI.ZAKAZKA and PD.VYROBNI_CISLO = DETI.VYROBNI_CISLO
                          where R.ZAKAZKA = P.ZAKAZKA and R.VYROBNI_CISLO = P.VYROBNI_CISLO and
                            (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(PD.DATUM_DOKONCENI, -3)) >= :i_datum)
            --generovano bylo pred vice jak 3 pracovnimi dny
            and not exists (select * from ZAK_ROZPISKY R join ZAK_VYROBNI_DAVKY VD on VD.DAVKA = R.DAVKA
                    where R.ZAKAZKA = P.ZAKAZKA and R.VYROBNI_CISLO = P.VYROBNI_CISLO and
                   (select O_TERMIN_SKUTECNY from GET_TERMIN_SKUTECNY(maxvalue(VD.DATUM_PREDANI, Z.DATUM_BLOKACE), -3)) >= :i_datum)
    into :xkoop_pocet_neobjednanych_t;

  select count(distinct VP.OBJEDNAVKA || '-' || VP.POLOZKA_OBJEDNAVKY)
    from MTZ_OBJEDNAVKY O
    join ZAK_VYROBNI_PRIKAZY VP on VP.OBJEDNAVKA = O.OBJEDNAVKA
    where O.DATUM_VYRIZENI is null and O.ROK > 2015 and VP.DATUM_ODVEDENI is null
      and coalesce (VP.TERMIN_POTVRZENY, O.TERMIN, VP.TERMIN_POZADAVKU, O.TERMIN_POZADOVANY) <= :i_datum and VP.STORNOVANO = ''
    into :xkoop_pocet_neprijatych;

  select avg (VP.DATUM_RESENI_ZMETKU - VP.DATUM_ZMETEK) PRUMER 
    from ZAK_VYROBNI_PRIKAZY VP
    where VP.ODVEDENO = '*' and VP.KONTROLOVANO = '' and VP.DATUM_ZMETEK is not null and VP.DATUM_RESENI_ZMETKU between :i_datum - 14 and :i_datum
    into :xprum_doba_reseni_zmetku_14;

  select avg (VP.DATUM_RESENI_ZMETKU - VP.DATUM_ZMETEK) PRUMER 
    from ZAK_VYROBNI_PRIKAZY VP
    where VP.ODVEDENO = '*' and VP.KONTROLOVANO = '' and VP.DATUM_ZMETEK is not null and VP.DATUM_RESENI_ZMETKU between :i_datum - 30 and :i_datum
    into :xprum_doba_reseni_zmetku_30;

  select avg (VP.DATUM_RESENI_ZMETKU - VP.DATUM_ZMETEK) PRUMER 
    from ZAK_VYROBNI_PRIKAZY VP
    where VP.ODVEDENO = '*' and VP.KONTROLOVANO = '' and VP.DATUM_ZMETEK is not null and VP.DATUM_RESENI_ZMETKU between :i_datum - 180 and :i_datum 
    into :xprum_doba_reseni_zmetku_180;

  
  select count(*)
    from MTZ_REKLAMACE R
    where cast (R.DATUM_VYTVORENI as date) = :i_datum and exists (select * from MTZ_REKLAMACE_POLOZKY P where P.REKLAMACE = R.REKLAMACE and P.ROK = R.ROK and P.ZAKAZKA > 0 and P.VYROBNI_CISLO > 0)
    into :xpocet_reklamacnich_protokolu;
  
  select count(*)
    from MTZ_REKLAMACE R
    where R.DATUM_SCHVALENI is null and exists (select * from MTZ_REKLAMACE_POLOZKY P where P.REKLAMACE = R.REKLAMACE and P.ROK = R.ROK and P.ZAKAZKA > 0 and P.VYROBNI_CISLO > 0)
    into :xpocet_neschvalenych_rekl_prot;

  --pocty neumistenych voziku pred skladem a pred kontrolou
  select count(distinct PS.IS_VYCHYSTANI) POCET_VOZIKU, count(PS.ID_DILU) POCET_DILU
    from ZAK_DILY_PRED_SKLADEM('0', 0) PS
    left outer join MTZ_IS_VYCHYSTANI I on I.IS_VYCHYSTANI = PS.IS_VYCHYSTANI
    left outer join ZAR_VYKRESY V on V.VYKRES = PS.VYKRES and V.ZRUSENO = '' 
    where PS.IS_VYCHYSTANI > 0 and PS.UMISTENI = '' and I.NEUMISTOVAT_V = '' and V.TYP_VYSKLADNENI = ''
      and (select max (MPP.DATUM_VYDANO)
                from MTZ_IS_VYCHYSTANI I 
                join ZAK_SKUPINY_VYCHYSTANI SV on SV.IS_VYCHYSTANI = I.IS_VYCHYSTANI
                join ZAK_MONT_PRIK_POLOZKY MPP on MPP.ZAKAZKA = SV.ZAKAZKA and MPP.MONTAZNI_PRIKAZ = SV.MONTAZNI_PRIKAZ
                left outer join ZAK_ROZPISKY R on R.ZAKAZKA = MPP.ZAKAZKA and R.MONTAZNI_CISLO = MPP.MONTAZNI_CISLO
                left outer join ZAK_ROZPISKY ROD on ROD.ZAKAZKA = R.ZAKAZKA and ROD.MONTAZNI_CISLO = R.MONTAZNI_CISLO_RODIC
                where I.IS_VYCHYSTANI = PS.IS_VYCHYSTANI and ROD.VYROBNI_CISLO = SV.VYROBNI_CISLO_PODSKUPINY and R.VYROBNI_CISLO > 0) is null
    into :xpocet_neum_is_pred_skl, :xpocet_dilu_neum_is_pred_skl;

  select count(distinct PS.IS_VYCHYSTANI) POCET_VOZIKU, count(PS.ID_DILU) POCET_DILU
    from ZAK_DILY_PRED_SKLADEM('1', 0) PS
    left outer join MTZ_IS_VYCHYSTANI I on I.IS_VYCHYSTANI = PS.IS_VYCHYSTANI
    left outer join ZAR_VYKRESY V on V.VYKRES = PS.VYKRES and V.ZRUSENO = ''
    where PS.IS_VYCHYSTANI > 0 and PS.UMISTENI = '' and I.NEUMISTOVAT_V = '' and V.TYP_VYSKLADNENI = ''
      and (select max (MPP.DATUM_VYDANO)
                from MTZ_IS_VYCHYSTANI I 
                join ZAK_SKUPINY_VYCHYSTANI SV on SV.IS_VYCHYSTANI = I.IS_VYCHYSTANI
                join ZAK_MONT_PRIK_POLOZKY MPP on MPP.ZAKAZKA = SV.ZAKAZKA and MPP.MONTAZNI_PRIKAZ = SV.MONTAZNI_PRIKAZ
                left outer join ZAK_ROZPISKY R on R.ZAKAZKA = MPP.ZAKAZKA and R.MONTAZNI_CISLO = MPP.MONTAZNI_CISLO
                left outer join ZAK_ROZPISKY ROD on ROD.ZAKAZKA = R.ZAKAZKA and ROD.MONTAZNI_CISLO = R.MONTAZNI_CISLO_RODIC
                where I.IS_VYCHYSTANI = PS.IS_VYCHYSTANI and ROD.VYROBNI_CISLO = SV.VYROBNI_CISLO_PODSKUPINY and R.VYROBNI_CISLO > 0) is null
    into :xpocet_neum_is_pred_kontr, :xpocet_dilu_neum_is_pred_kontr;

  --pocty oper. pred kontrolou
  select POCET_OP_PRED_KONTR, POCET_OP_PRED_KONTR_2_DNY, POCET_OP_PRED_KONTR_ZAH_ZITRA, POCET_OP_PRED_KONTR_ZAH_POZIT,
        POCET_OP_PRED_KONTR - (POCET_OP_PRED_KONTR_ZAH_ZITRA + POCET_OP_PRED_KONTR_ZAH_POZIT) POCET_BEZ_ZIT_POZ
    from (
    select count(*) POCET_OP_PRED_KONTR, 
    sum(iif(cast (VP.DATUM_ODVEDENI as date) < current_date - 2,1,0)) POCET_OP_PRED_KONTR_2_DNY,
    sum(iif(NT.TERMIN_ZAHAJENI= (select * from GET_TERMIN_SKUTECNY(current_date,-1)) ,1,0)) POCET_OP_PRED_KONTR_ZAH_ZITRA,
    sum(iif(NT.TERMIN_ZAHAJENI= (select * from GET_TERMIN_SKUTECNY(current_date,-2)) ,1,0)) POCET_OP_PRED_KONTR_ZAH_POZIT
    from ZAK_ZAKAZKY_AKTIVNI ZA
    join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = ZA.ZAKAZKA and VP.STORNOVANO = ''
    join ZAK_POSTUPY P on P.ZAKAZKA = VP.ZAKAZKA and P.VYROBNI_CISLO = VP.VYROBNI_CISLO and P.CISLO_OPERACE = VP.CISLO_OPERACE
    and P.STREDISKO in ('4','7')  and P.ZRUSENO = ''
    left outer join ZAK_POSTUPY_GET_NASL_TERMINY(P.ZAKAZKA, P.VYROBNI_CISLO, P.CISLO_OPERACE) NT on 1=1
    where VP.ODVEDENO = '*' and VP.DATUM_ODVEDENI is not null and VP.DATUM_KONTROLY is null and VP.ZMETEK = ''
    )
    into :xpocet_op_pred_kontr, :xpocet_pred_kontr_dva_dny, :xpocet_pred_kontr_zah_zitra, :xpocet_pred_kontr_zah_pozit, :xpocet_pred_kontr_bez_dva_dny;
    
    --operace pred kontr, na montaz
  select count(*) POCET
    from ZAK_ZAKAZKY_AKTIVNI ZA
    join ZAK_VYROBNI_PRIKAZY VP on ZA.ZAKAZKA = VP.ZAKAZKA
    join ZAK_POSTUPY p on P.ZAKAZKA = VP.ZAKAZKA and P.VYROBNI_CISLO = VP.VYROBNI_CISLO and P.CISLO_OPERACE = VP.CISLO_OPERACE 
            and P.ZRUSENO = '' and P.NASLEDUJICI_OPERACE = 0 and P.PROFESE not in ('0629')
    join ZAK_PROFESE PR on PR.PROFESE = P.PROFESE and PR.LAKOVNA = ''
    where VP.ODVEDENO = '*' and VP.KONTROLOVANO = '' and VP.STORNOVANO = ''
    into :xpocet_pred_kontr_mont;
    
    --zkontrolovane operace
  select A.POCET, A.POCET_ZITRA, A.POCET_POZITRI, A.POCET - (A.POCET_ZITRA + A.POCET_POZITRI) POCET_BEZ_ZIT_POZ
    from(
    select count(*) POCET,
    coalesce(sum(iif(NT.TERMIN_ZAHAJENI = (select * from GET_TERMIN_SKUTECNY(current_date,-1)) ,1,0)),0) POCET_ZITRA,
    coalesce(sum(iif(NT.TERMIN_ZAHAJENI = (select * from GET_TERMIN_SKUTECNY(current_date,-2)) ,1,0)),0) POCET_POZITRI
    from ZAK_ZAKAZKY_AKTIVNI ZA
    join ZAK_VYROBNI_PRIKAZY VP on ZA.ZAKAZKA = VP.ZAKAZKA
    join ZAK_POSTUPY P on P.ZAKAZKA = VP.ZAKAZKA and P.VYROBNI_CISLO = VP.VYROBNI_CISLO and P.CISLO_OPERACE = VP.CISLO_OPERACE and P.STREDISKO in ('4','7')
    left outer join ZAK_POSTUPY_GET_NASL_TERMINY(VP.ZAKAZKA, VP.VYROBNI_CISLO, VP.CISLO_OPERACE) NT on 1=1
    where cast(VP.DATUM_KONTROLY as date) = current_date and VP.KONTROLOVANO = '*' and VP.STORNOVANO = ''
    ) A
    into :xpocet_kontr_dnes, :xpocet_kontr_dnes_zah_zitra, :xpocet_kontr_dnes_zah_pozit, :xpocet_kontr_dnes_bez_dva_dny;

    --melo se zkontrolovat
  select POCET_OP_PRED_KONTR, POCET_OP_PRED_KONTR_ZAH_ZITRA, POCET_OP_PRED_KONTR_ZAH_POZIT,
        POCET_OP_PRED_KONTR - (POCET_OP_PRED_KONTR_ZAH_ZITRA + POCET_OP_PRED_KONTR_ZAH_POZIT) POCET_BEZ_ZIT_POZ
    from (
    select count(*) POCET_OP_PRED_KONTR,
    sum(iif(NT.TERMIN_ZAHAJENI= (select * from GET_TERMIN_SKUTECNY(current_date,-1)) ,1,0)) POCET_OP_PRED_KONTR_ZAH_ZITRA,
    sum(iif(NT.TERMIN_ZAHAJENI= (select * from GET_TERMIN_SKUTECNY(current_date,-2)) ,1,0)) POCET_OP_PRED_KONTR_ZAH_POZIT
    from ZAK_ZAKAZKY_AKTIVNI ZA
    join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = ZA.ZAKAZKA and VP.STORNOVANO = ''
    join ZAK_POSTUPY P on P.ZAKAZKA = VP.ZAKAZKA and P.VYROBNI_CISLO = VP.VYROBNI_CISLO and P.CISLO_OPERACE = VP.CISLO_OPERACE
    and P.STREDISKO in ('4','7') and P.ZRUSENO = ''
    left outer join ZAK_POSTUPY_GET_NASL_TERMINY(P.ZAKAZKA, P.VYROBNI_CISLO, P.CISLO_OPERACE) NT on 1=1
    where VP.ODVEDENO = '*' and VP.ZMETEK = '' and cast(VP.DATUM_ODVEDENI as date) < current_date 
    and (VP.DATUM_KONTROLY is null or cast(VP.DATUM_KONTROLY as date) >= current_date)
    )
    into :xpocet_melo_kontr, :xpocet_melo_kontr_zah_zitra, :xpocet_melo_kontr_zah_pozit, :xpocet_melo_kontr_bez_dva_dny;

  --pozor změny dělat i v DM ZMETKY_Z_MONTAZE
  select 
    --pocet zmetku
      (select count(distinct VP.ZAKAZKA || '/' || VP.VYROBNI_CISLO || '/' || VP.CISLO_OPERACE)
        from ZAK_VYROBNI_PRIKAZY VP
        where VP.STORNOVANO = '' and VP.DATUM_ZMETEK between SD.DATUM and SD.DATUM + 1 and VP.ZDROJ_ZMETKU_POZNAMKA starting 'Zmetek z montáže') POCET_ZMETKU_MONTAZE,
      --pocet vyresenych
      (select count(distinct VP.ZAKAZKA || '/' || VP.VYROBNI_CISLO || '/' || VP.CISLO_OPERACE)
        from ZAK_VYROBNI_PRIKAZY VP
        where VP.STORNOVANO = '' and cast (VP.DATUM_RESENI_ZMETKU as date) = SD.DATUM and VP.ZDROJ_ZMETKU_POZNAMKA starting 'Zmetek z montáže') POCET_VYRESENYCH,
      --pocet nevyresenych
      (select count(distinct VP.ZAKAZKA || '/' || VP.VYROBNI_CISLO || '/' || VP.CISLO_OPERACE)
        from ZAK_VYROBNI_PRIKAZY VP
        where VP.STORNOVANO = '' and VP.DATUM_ZMETEK < SD.DATUM + 0.999 and (VP.DATUM_RESENI_ZMETKU is null or cast (VP.DATUM_RESENI_ZMETKU as date) > SD.DATUM) and VP.ZDROJ_ZMETKU_POZNAMKA starting 'Zmetek z montáže') POCET_NEVYRESENYCH
    from SELECT_DNY(:i_datum, :i_datum) SD
    where SD.SVATEK = ''
    into :xpocet_mont_zmetek, :xpocet_mont_zmetek_vyreseno, :xpocet_mont_zmetek_nevyreseno;

  /*Ukaz. do bud.
  Fronta z obrobny*/
  xpocet = 0;
  xtyden_bud = extract(week from current_date +21);
  xrok_bud = extract(year from current_date + 21);

  select datum_do from TYDENNI_OBDOBI2DATUM(:xrok_bud,:xtyden_bud)
    into :xdatum_do;
  select datum_od from TYDENNI_OBDOBI2DATUM(extract(year from current_date),extract(week from current_date))
    into :xdatum_od;

  for select count(*) POCET,
    sum(iif(extract (week from NT.TERMIN_ZAHAJENI) = extract (week from P.TERMIN_DOKONCENI),1,0)),
    sum(iif(extract (week from NT.TERMIN_ZAHAJENI) = extract (week from P.TERMIN_DOKONCENI) +1,1,0))
    from ZAK_ZAKAZKY_AKTIVNI ZA
    join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = ZA.ZAKAZKA and VP.STORNOVANO = ''
    join ZAK_POSTUPY P on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
    left outer join ZAK_POSTUPY_GET_NASL_TERMINY(P.ZAKAZKA, P.VYROBNI_CISLO, P.CISLO_OPERACE) NT on 1=1
    where P.STREDISKO in ('4','7') and VP.KONTROLOVANO = '' and P.KOOPERACE = '' and P.KOOPERACE_TECHNOLOGICKA = ''
    and P.ZRUSENO = ''
    and cast(P.TERMIN_DOKONCENI as date) between :xdatum_od and :xdatum_do
    group by extract (week from P.TERMIN_DOKONCENI)
    order by extract (week from P.TERMIN_DOKONCENI)
    into :xpocet_fronta_obrobny_t3, :xpocet_fronta_obr_t3_zah_t0, :xpocet_fronta_obr_t3_zah_t1
  do begin
    if(xpocet = 0) then
    begin
      xpocet_fronta_obrobny_t0 = xpocet_fronta_obrobny_t3;
      xpocet_fronta_obr_t0_zah_t0 = xpocet_fronta_obr_t3_zah_t0;
      xpocet_fronta_obr_t0_zah_t1 = xpocet_fronta_obr_t3_zah_t1;
    end
    else if(xpocet = 1) then
    begin
      xpocet_fronta_obrobny_t1 = xpocet_fronta_obrobny_t3;
      xpocet_fronta_obr_t1_zah_t0 = xpocet_fronta_obr_t3_zah_t0;
      xpocet_fronta_obr_t1_zah_t1 = xpocet_fronta_obr_t3_zah_t1;
    end
    else if(xpocet = 2) then
    begin
      xpocet_fronta_obrobny_t2 = xpocet_fronta_obrobny_t3;
      xpocet_fronta_obr_t2_zah_t0 = xpocet_fronta_obr_t3_zah_t0;
      xpocet_fronta_obr_t2_zah_t1 = xpocet_fronta_obr_t3_zah_t1;
    end
    xpocet = xpocet+1;
  end

  xpocet = 0;
  /*Fronta z kooperace*/
  for select count(*) POCET,
      sum(iif(extract (week from NT.TERMIN_ZAHAJENI) = extract(week from VP.TERMIN_POTVRZENY),1,0)) ZAH_AKT_TYDEN,
      sum(iif(extract (week from NT.TERMIN_ZAHAJENI) = extract(week from VP.TERMIN_POTVRZENY) +1,1,0)) ZAH_PRISTI_TYDEN
    from ZAK_ZAKAZKY_AKTIVNI ZA
    join ZAK_POSTUPY p on P.ZAKAZKA = ZA.ZAKAZKA
    left outer join ZAK_VYROBNI_PRIKAZY VP on VP.ZAKAZKA = P.ZAKAZKA and VP.VYROBNI_CISLO = P.VYROBNI_CISLO and VP.CISLO_OPERACE = P.CISLO_OPERACE
    left outer join ZAK_POSTUPY_GET_NASL_TERMINY(P.ZAKAZKA, P.VYROBNI_CISLO, P.CISLO_OPERACE) NT on 1=1
    where P.ZRUSENO = '' and P.STREDISKO in ('4','7') and P.KONTROLOVANO = ''
    and VP.STORNOVANO = ''
    and cast(vp.TERMIN_POTVRZENY as date) between :xdatum_od and :xdatum_do
    /*and not exists(
    select ROD.KOD_SKUPINY
    froM ZAK_ROZPISKY R
    join ZAK_ROZPISKY ROD on ROD.ZAKAZKA = R.ZAKAZKA and ROD.MONTAZNI_CISLO = R.MONTAZNI_CISLO_RODIC
    where R.ZAKAZKA = P.ZAKAZKA and R.VYROBNI_CISLO = P.VYROBNI_CISLO and ROD.KOD_SKUPINY = 'S'
    )*/
    and P.CISLO_OPERACE = (select max(VP2.CISLO_OPERACE)
    from ZAK_VYROBNI_PRIKAZY VP2
    where VP2.OBJEDNAVKA = VP.OBJEDNAVKA and VP2.POLOZKA_OBJEDNAVKY = VP.POLOZKA_OBJEDNAVKY)
    group by extract(week from VP.TERMIN_POTVRZENY)
    order by extract(week from VP.TERMIN_POTVRZENY)
    into :xpocet_fronta_koo_t3, :xpocet_fronta_koo_t3_zah_t0, :xpocet_fronta_koo_t3_zah_t1
  do begin
    if(xpocet = 0) then
    begin
      xpocet_fronta_koo_t0 = xpocet_fronta_koo_t3;
      xpocet_fronta_koo_t0_zah_t0 = xpocet_fronta_koo_t3_zah_t0;
      xpocet_fronta_koo_t0_zah_t1 = xpocet_fronta_koo_t3_zah_t1;
    end
    else if(xpocet = 1) then
    begin
      xpocet_fronta_koo_t1 = xpocet_fronta_koo_t3;
      xpocet_fronta_koo_t1_zah_t0 = xpocet_fronta_koo_t3_zah_t0;
      xpocet_fronta_koo_t1_zah_t1 = xpocet_fronta_koo_t3_zah_t1;
    end
    else if(xpocet = 2) then
    begin
      xpocet_fronta_koo_t2 = xpocet_fronta_koo_t3;
      xpocet_fronta_koo_t2_zah_t0 = xpocet_fronta_koo_t3_zah_t0;
      xpocet_fronta_koo_t2_zah_t1 = xpocet_fronta_koo_t3_zah_t1;
    end
    xpocet = xpocet +1;
  end
  
  /*UCTARNA*/
  select count(*) POCET_PRICHOZI,
    count(iif(cast(ND.DATUM as date) = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD > 0 and ND.KNIHA = 'FD', 1, null)) VYRIZENO_FAKTUROU_DNES,
    count(iif(cast(ND.DATUM as date) = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD > 0 and ND.KNIHA = 'FD' and ND.ISDOC_FORMAT = '*', 1, null)) VYRIZENO_FAKT_ISDOC_DNES,
    count(iif(cast(ND.DATUM as date) = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD > 0 and ND.KNIHA = 'FD' and ND.ISDOC_FORMAT = '', 1, null)) VYRIZENO_FAKT_RUCNE_DNES,
    count(iif(cast(ND.DATUM as date) = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD > 0 and ND.KNIHA = 'ZL', 1, null)) VYRIZENO_ZAL_LIST_DNES,
    count(iif(cast(ND.DATUM as date) = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD = 0, 1, null)) VYRIZENO_POZNAMKOU_DNES,
    count(iif(ND.DATUM_VYRIZENI is null, 1, null)) NEZAPSANO_DNES
    from UCE_NEROZTRIDENE_DOKLADY ND
    where cast(ND.DATUM as date) = :i_datum
    into :xpocet_nd_prich_dnes, :xpocet_nd_prich_fakturou_dnes, :xpocet_nd_prich_fakt_isdoc_dnes, :xpocet_nd_prich_fakt_rucne_dnes, :xpocet_nd_prich_zal_list_dnes,
         :xpocet_nd_prich_poznamkou_dnes, :xpocet_nd_prich_nezapsano_dnes;
  
  select count(iif(ND.DATUM_VYRIZENI is null, 1, null)) NEZAPSANO_CELKEM,
    count(iif(:i_datum = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD > 0 and ND.KNIHA = 'FD', 1, null)) VYRIZENO_CELKEM_FAKTUROU,
    count(iif(:i_datum = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD > 0 and ND.KNIHA = 'FD' and ND.ISDOC_FORMAT = '*', 1, null)) VYRIZENO_CELKEM_FAKT_ISDOC,
    count(iif(:i_datum = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD > 0 and ND.KNIHA = 'FD' and ND.ISDOC_FORMAT = '', 1, null)) VYRIZENO_CELKEM_FAKT_RUCNE,
    count(iif(:i_datum = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD > 0 and ND.KNIHA = 'ZL', 1, null)) VYRIZENO_CELKEM_ZAL_LIST,
    count(iif(:i_datum = cast(ND.DATUM_VYRIZENI as date) and ND.DOKLAD = 0, 1, null)) VYRIZENO_CELKEM_POZNAMKOU
    from UCE_NEROZTRIDENE_DOKLADY ND
    into :xpocet_nd_celk_nezapsano, :xpocet_nd_celk_fakturou_dnes, :xpocet_nd_celk_fakt_isdoc_dnes, :xpocet_nd_celk_fakt_rucne_dnes, 
          :xpocet_nd_celk_zal_list_dnes, :xpocet_nd_celk_poznamkou_dnes;
  
  select count(iif(cast(F.DATUM_ZAUCTOVANI as date) = :i_datum, 1, null)) POCET_ZAUCTOVANYCH_DNES, 
        count(iif(F.LIKVIDACE_FAKTURY not in ('V') and F.DATUM_ZAUCTOVANI is null, 1 , null)) POCET_NEZAUCTOVANYCH_CELKEM
    from UCE_FAKTURY F
    where F.KNIHA = 'FD' 
    into :xpocet_faktur_zauctovane_dnes, :xpocet_faktur_nezauctovane;

  select cast (O_TERMIN_SKUTECNY as date) - current_date  as VZDALENOST from GET_TERMIN_SKUTECNY (current_date, -1)
    into :xvzdalenost_nasl_prac_den;
  select cast (sum(iif (MAIN.TERMIN_ZAHAJENI = current_date,1,0)) as POCET) as DNES, 
       cast (sum(iif (MAIN.TERMIN_ZAHAJENI <> current_date,1,0)) as POCET) as ZITRA
  from ZAK_ZAKAZKY_AKTIVNI Z
  join ZAK_POSTUPY MAIN on MAIN.ZAKAZKA = Z.ZAKAZKA
    join ZAK_PROFESE ZAK_PROFESE on ZAK_PROFESE.PROFESE = MAIN.PROFESE
    join ZAK_VYKRESY V on MAIN.ZAKAZKA = V.ZAKAZKA and MAIN.VYROBNI_CISLO = V.VYROBNI_CISLO
    join ZAR_KODY_SKUPIN KS on KS.KOD = V.KOD_SKUPINY
    left outer join ZAK_VYROBNI_DAVKY D on D.DAVKA = V.DAVKA
  where MAIN.STREDISKO = '4' and MAIN.PROFESE not in ('0629') and MAIN.PRIPRAVENO = '*' and MAIN.TERMIN_ZAHAJENI between current_date and current_date + :xvzdalenost_nasl_prac_den
          and MAIN.KOOPERACE = '' and MAIN.KOOPERACE_TECHNOLOGICKA = '' and ZAK_PROFESE.REZARNA = '' and ZAK_PROFESE.LAKOVNA = ''
          and KS.MONTOVATELNA_SKUPINA = ''
          and minvalue(Z.ZAKAZKA_BLOKOVANA,coalesce (D.DAVKA_BLOKOVANA,'')) = ''
  into :xpocet_pripr_nezad_dnes, :xpocet_pripr_nezad_zitra;

  
  select coalesce (sum (-1 * PN.NORMATIV_PRIPRAVA), 0)
    from UCE_KODY_NAKLADU KN
    join ZAK_POSTUPY_NORMATIVY PN on PN.KOD_NAKLADU = KN.KOD_NAKLADU
    where KN.USPORA_PRIPRAVY_DISPECER = '*' and cast (PN.DATUM_ZAPISU as date) = current_date
    into :xsuma_usporene_pripravy;

  /********************ZAPIS DO TABULKY*************************/
  delete from ZAK_SOMA_UKAZATELE where DATUM = :i_datum;
  
  insert into ZAK_SOMA_UKAZATELE (DATUM,
              NAKUP_POCET_NEPRIJATYCH, KOOP_POCET_PRIJATYCH, KOOP_POCET_VYDANYCH, KOOP_POCET_NEPRIJATYCH, NAKUP_NEOBJEDNANYCH,
              KOOP_POCET_NEOBJEDNANYCH_K, KOOP_POCET_NEOBJEDNANYCH_T, PRUM_DOBA_RESENI_ZMETKU_14, PRUM_DOBA_RESENI_ZMETKU_30, PRUM_DOBA_RESENI_ZMETKU_180,
              POCET_REKLAMACNICH_PROTOKOLU, POCET_NESCHVALENYCH_REKL_PROT, POCET_NEUM_IS_PRED_SKL, POCET_DILU_NEUM_IS_PRED_SKL, POCET_NEUM_IS_PRED_KONTR, 

              POCET_DILU_NEUM_IS_PRED_KONTR, POCET_OP_PRED_KONTR, POCET_PRED_KONTR_DVA_DNY, POCET_PRED_KONTR_ZAH_ZITRA,
              POCET_PRED_KONTR_ZAH_POZIT, POCET_PRED_KONTR_BEZ_DVA_DNY, POCET_PRED_KONTR_MONT, POCET_KONTR_DNES, POCET_KONTR_DNES_ZAH_ZITRA,
              POCET_KONTR_DNES_ZAH_POZIT, POCET_KONTR_DNES_BEZ_DVA_DNY, POCET_MELO_KONTR_DNES, POCET_MELO_KONTR_ZITRA, POCET_MELO_KONTR_POZIT,

              POCET_MELO_KONTR_BEZ_DVA_DNY, POCET_FRONTA_OBROBNY_T0, POCET_FRONTA_OBROBNY_T1, POCET_FRONTA_OBROBNY_T2, POCET_FRONTA_OBROBNY_T3,
              POCET_MONT_ZMETEK, POCET_MONT_ZMETEK_VYRESENO, POCET_MONT_ZMETEK_NEVYRESENO,
              POCET_FRONTA_OBR_T0_ZAH_T0, POCET_FRONTA_OBR_T1_ZAH_T0, POCET_FRONTA_OBR_T2_ZAH_T0, POCET_FRONTA_OBR_T3_ZAH_T0,
              POCET_FRONTA_OBR_T0_ZAH_T1, POCET_FRONTA_OBR_T1_ZAH_T1, POCET_FRONTA_OBR_T2_ZAH_T1, POCET_FRONTA_OBR_T3_ZAH_T1,

              POCET_FRONTA_KOO_T0, POCET_FRONTA_KOO_T1, POCET_FRONTA_KOO_T2, POCET_FRONTA_KOO_T3,
              POCET_FRONTA_KOO_T0_ZAH_T0, POCET_FRONTA_KOO_T1_ZAH_T0, POCET_FRONTA_KOO_T2_ZAH_T0, POCET_FRONTA_KOO_T3_ZAH_T0,
              POCET_FRONTA_KOO_T0_ZAH_T1, POCET_FRONTA_KOO_T1_ZAH_T1, POCET_FRONTA_KOO_T2_ZAH_T1, POCET_FRONTA_KOO_T3_ZAH_T1,
              --ucetnictvi
              POCET_ND_PRICHOZI_DNES, POCET_ND_PRICHOZI_FAKTUROU_DNES, POCET_ND_PRICH_FAKT_ISDOC_DNES, POCET_ND_PRICH_FAKT_RUCNE_DNES, POCET_ND_PRICHOZI_ZAL_LIST_DNES, POCET_ND_PRICHOZI_POZNAM_DNES, POCET_ND_PRICHOZI_NEZAPS_DNES,
              POCET_ND_CELKEM_FAKTUROU_DNES, POCET_ND_CELKEM_FAKT_ISDOC_DNES, POCET_ND_CELKEM_FAKT_RUCNE_DNES, POCET_ND_CELKEM_ZAL_LIST_DNES, POCET_ND_CELKEM_POZNAMKOU_DNES, POCET_ND_CELKEM_NEZAPSANO,
              POCET_FAKTUR_ZAUCTOVANE_DNES, POCET_FAKTUR_NEZAUCTOVANE, POCET_PRIPR_NEZAD_DNES, POCET_PRIPR_NEZAD_ZITRA, SUMA_USPORENE_PRIPRAVY
)
    values (:i_datum,
          :xnakup_pocet_nedodanych, :xkoop_pocet_prijatych, :xkoop_pocet_vydanych, :xkoop_pocet_neprijatych, :xnakup_neobjednanych,
          :xkoop_pocet_neobjednanych_k, :xkoop_pocet_neobjednanych_t, :xprum_doba_reseni_zmetku_14, :xprum_doba_reseni_zmetku_30, :xprum_doba_reseni_zmetku_180,
          :xpocet_reklamacnich_protokolu, :xpocet_neschvalenych_rekl_prot, :xpocet_neum_is_pred_skl, :xpocet_dilu_neum_is_pred_skl, :xpocet_neum_is_pred_kontr,

          :xpocet_dilu_neum_is_pred_kontr, :xpocet_op_pred_kontr, :xpocet_pred_kontr_dva_dny, :xpocet_pred_kontr_zah_zitra,
          :xpocet_pred_kontr_zah_pozit, :xpocet_pred_kontr_bez_dva_dny, :xpocet_pred_kontr_mont, :xpocet_kontr_dnes, :xpocet_kontr_dnes_zah_zitra,
          :xpocet_kontr_dnes_zah_pozit, :xpocet_kontr_dnes_bez_dva_dny, :xpocet_melo_kontr, :xpocet_melo_kontr_zah_zitra, :xpocet_melo_kontr_zah_pozit,

          :xpocet_melo_kontr_bez_dva_dny, :xpocet_fronta_obrobny_t0, :xpocet_fronta_obrobny_t1, :xpocet_fronta_obrobny_t2, :xpocet_fronta_obrobny_t3,
          :xpocet_mont_zmetek, :xpocet_mont_zmetek_vyreseno, :xpocet_mont_zmetek_nevyreseno,
          :xpocet_fronta_obr_t0_zah_t0, :xpocet_fronta_obr_t1_zah_t0, :xpocet_fronta_obr_t2_zah_t0, :xpocet_fronta_obr_t3_zah_t0,
          :xpocet_fronta_obr_t0_zah_t1, :xpocet_fronta_obr_t1_zah_t1, :xpocet_fronta_obr_t2_zah_t1, :xpocet_fronta_obr_t3_zah_t1,

          :xpocet_fronta_koo_t0, :xpocet_fronta_koo_t1, :xpocet_fronta_koo_t2, :xpocet_fronta_koo_t3,
          :xpocet_fronta_koo_t0_zah_t0, :xpocet_fronta_koo_t1_zah_t0, :xpocet_fronta_koo_t2_zah_t0, :xpocet_fronta_koo_t3_zah_t0,
          :xpocet_fronta_koo_t0_zah_t1, :xpocet_fronta_koo_t1_zah_t1, :xpocet_fronta_koo_t2_zah_t1, :xpocet_fronta_koo_t3_zah_t1,
          --ucetnictvi
          :xpocet_nd_prich_dnes, :xpocet_nd_prich_fakturou_dnes, :xpocet_nd_prich_fakt_isdoc_dnes, :xpocet_nd_prich_fakt_rucne_dnes, :xpocet_nd_prich_zal_list_dnes, :xpocet_nd_prich_poznamkou_dnes, :xpocet_nd_prich_nezapsano_dnes,
          :xpocet_nd_celk_fakturou_dnes, :xpocet_nd_celk_fakt_isdoc_dnes, :xpocet_nd_celk_fakt_rucne_dnes, :xpocet_nd_celk_zal_list_dnes, :xpocet_nd_celk_poznamkou_dnes, :xpocet_nd_celk_nezapsano,
          :xpocet_faktur_zauctovane_dnes, :xpocet_faktur_nezauctovane, :xpocet_pripr_nezad_dnes, :xpocet_pripr_nezad_zitra, :xsuma_usporene_pripravy
          );
  
  --TB20191014 V ramci prehlednosti jsou skladove ukazatele ve zvlastni procedure
  execute procedure ZAK_SOMA_UKAZATELE_VYPOCET_SKL(:i_datum);
  execute procedure ZAK_SOMA_UKAZATELE_VYPOCET_MON(:i_datum);
  execute procedure ZAK_SOMA_UKAZATELE_VYPOCET_MTZ(:i_datum);
  execute procedure ZAK_SOMA_UKAZATELE_VYPOCET_TPV(:i_datum);
  --JH20200131 V ramci prehlednosti jsou SGS ukazatele ve zvlastni procedure - nepocitaji se za jeden den, ale 3 mesice dozadu
  execute procedure ZAK_SOMA_UKAZATELE_VYPOCET_SGS(null,null);
end
^
	
set term ; ^
cleverCOmponentsExport.sql (200,358 bytes)   

barry

2021-04-13 14:51

administrator   ~0004493

FB 2.5?

barry

2021-04-13 15:08

administrator   ~0004495

(+) WITH ... SELECT for MERGE statement (1662).

barry

2021-04-13 15:09

administrator   ~0004496

insert into <TABLE> default values

barry

2021-04-13 15:18

administrator   ~0004497

Fixed on Build 2400 (cleverCOmponentsExport.sql).

Issue History

Date Modified Username Field Change
2020-12-04 17:15 shirokov New Issue
2020-12-04 17:15 shirokov Status new => assigned
2020-12-04 17:15 shirokov Assigned To => barry
2020-12-04 17:15 shirokov File Added: cleverCOmponentsExport.sql
2021-04-13 14:48 barry Relationship added related to 0001648
2021-04-13 14:51 barry Note Added: 0004493
2021-04-13 15:08 barry Status assigned => confirmed
2021-04-13 15:08 barry Note Added: 0004495
2021-04-13 15:09 barry Note Added: 0004496
2021-04-13 15:18 barry Note Added: 0004497
2021-04-13 15:22 barry Status confirmed => resolved
2021-04-13 15:22 barry Resolution open => fixed
2021-04-13 15:22 barry Fixed in Version => 8.0.0.2400