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 ; ^