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