|
|
| Řádek 1: |
Řádek 1: |
| Doplněn '''součet''' kódů 941 a 942 (původně jen 941)
| |
|
| |
|
| '''Čistá mzda z původního a nového systému (kontrolní sestava pro duplicitu)'''
| |
|
| |
| == Předpoklad ==
| |
| Předpokladem je dodání dat z původního systému ve tvaru:
| |
| * os.číslo
| |
| * k výplatě
| |
| * sociální základ
| |
| * měsíc
| |
| * rok
| |
|
| |
| Data mohou být např. v MS Excel.
| |
|
| |
| == Create tabulky ==
| |
|
| |
| create table aa_mzdy (
| |
| os_cislo decimal (10,0),
| |
| k_vyplate decimal (10,2),
| |
| soc_zakl decimal (10,2),
| |
| mesic smallint,
| |
| rok smallint
| |
| );
| |
|
| |
| == Naplnění dat ==
| |
| Výše uvedenou tabulky je potřeba naplnit data. Např. pomocí připravených sql příkazů.
| |
|
| |
| == Kontrolní sestava ==
| |
| [[Image: Cista-mzda-z-původniho-a-noveho-systemu.png]]
| |
|
| |
| ''Pozn. Os.č. 3,5,8 jsou obsaženy v původním i novém systému. Os.č. 10, 11 nejsou obsažena v novém systému. Os.č. 20156 a 21280 nejsou obsažena v původním systému.''
| |
|
| |
| Definice sestavy pro [[Generátor sestav II]]
| |
|
| |
| select
| |
| distinct X.os_cislo as os_cislo_puv,
| |
| A.os_cislo as os_cislo_ks,
| |
| A.prijmeni,
| |
| A.jmeno,
| |
|
| |
| (select sum(k_vyplate) from aa_mzdy X1 where X1.os_cislo = X.os_cislo and
| |
| year(%DATUMOD%) = X1.rok and
| |
| month (%DATUMOD%) = X1.mesic
| |
| ) as k_vypl_puv,
| |
|
| |
| (select sum(castka) from pichacky P1 where P1.os_cislo = A.os_cislo and kod in (941, 942) and P1.datum = %DATUMOD%) as k_941_942_ks,
| |
|
| |
| ((select sum(k_vyplate) from aa_mzdy X1 where X1.os_cislo = X.os_cislo and
| |
| year(%DATUMOD%) = X1.rok and
| |
| month (%DATUMOD%) = X1.mesic
| |
| )
| |
| -
| |
| (select sum(castka) from pichacky P1 where P1.os_cislo = A.os_cislo and kod in (941, 942) and P1.datum = %DATUMOD%)) as rozdil_1,
| |
|
| |
| (select sum(soc_zakl) from aa_mzdy X2 where X2.os_cislo = X.os_cislo and
| |
| year(%DATUMOD%) = X2.rok and
| |
| month (%DATUMOD%) = X2.mesic
| |
| ) as soc_zakl_puv,
| |
|
| |
| (select sum(soc_zakl) from telegram T1 where T1.os_cislo = A.os_cislo and T1.datum = %DATUMOD%) as soc_zakl_ks,
| |
|
| |
| ((select sum(soc_zakl) from aa_mzdy X2 where X2.os_cislo = X.os_cislo and
| |
| year(%DATUMOD%) = X2.rok and
| |
| month (%DATUMOD%) = X2.mesic
| |
| )
| |
| -
| |
| (select sum(soc_zakl) from telegram T1 where T1.os_cislo = A.os_cislo and T1.datum = %DATUMOD%)) as rozdil_2
| |
|
| |
| from pracovnici A full join aa_mzdy X on A.os_cislo = X.os_cislo
| |
|
| |
| <small> Pro Oracle:
| |
| select
| |
| distinct X.os_cislo as os_cislo_puv,
| |
| A.os_cislo as os_cislo_ks,
| |
| A.prijmeni,
| |
| A.jmeno,
| |
|
| |
| (select sum(k_vyplate) from aa_mzdy X1 where X1.os_cislo = X.os_cislo and
| |
| TO_NUMBER(substr (%DATUMOD%, 1,4)) = X1.rok and
| |
| TO_NUMBER(substr (%DATUMOD%, 6,2)) = X1.mesic
| |
| ) as k_vypl_puv,
| |
|
| |
| (select sum(castka) from pichacky P1 where P1.os_cislo = A.os_cislo and kod in (941, 942) and P1.datum = %DATUMOD%) as k_941_942_ks,
| |
|
| |
| ((select sum(k_vyplate) from aa_mzdy X1 where X1.os_cislo = X.os_cislo and
| |
| TO_NUMBER(substr (%DATUMOD%, 1,4)) = X1.rok and
| |
| TO_NUMBER(substr (%DATUMOD%, 6,2)) = X1.mesic
| |
| )
| |
| -
| |
| (select sum(castka) from pichacky P1 where P1.os_cislo = A.os_cislo and kod kod in (941, 942) and P1.datum = %DATUMOD%)) as rozdil_1,
| |
|
| |
| (select sum(soc_zakl) from aa_mzdy X2 where X2.os_cislo = X.os_cislo and
| |
| TO_NUMBER(substr (%DATUMOD%, 1,4)) = X2.rok and
| |
| TO_NUMBER(substr (%DATUMOD%, 6,2)) = X2.mesic
| |
| ) as soc_zakl_puv,
| |
|
| |
| (select sum(soc_zakl) from telegram T1 where T1.os_cislo = A.os_cislo and T1.datum = %DATUMOD%) as soc_zakl_ks,
| |
|
| |
| ((select sum(soc_zakl) from aa_mzdy X2 where X2.os_cislo = X.os_cislo and
| |
| TO_NUMBER(substr (%DATUMOD%, 1,4)) = X2.rok and
| |
| TO_NUMBER(substr (%DATUMOD%, 6,2)) = X2.mesic
| |
| )
| |
| -
| |
| (select sum(soc_zakl) from telegram T1 where T1.os_cislo = A.os_cislo and T1.datum = %DATUMOD%)) as rozdil_2
| |
|
| |
| from pracovnici A full join aa_mzdy X on A.os_cislo = X.os_cislo
| |
| </small>
| |
|
| |
| <small>
| |
| Historická verze:
| |
| select
| |
| A.os_cislo,
| |
| A.prijmeni,
| |
| A.jmeno,
| |
| X.k_vyplate as k_vypl_puv,
| |
| sum(P.castka) as k_vypl_ks,
| |
| (X.k_vyplate - sum(P.castka)) as rozdil,
| |
| X.soc_zakl as soc_zakl_puv,
| |
| T.soc_zakl as soc_zakl_ks,
| |
| (X.soc_zakl - T.soc_zakl) as rozdil
| |
| from pracovnici A, aa_mzdy X, pichacky P, telegram T where
| |
| A.os_cislo = X.os_cislo and
| |
| A.os_cislo = P.os_cislo and
| |
| A.os_cislo = T.os_cislo and
| |
| P.kod = 941 and
| |
| P.datum = %DATUMOD% and
| |
| T.datum = %DATUMOD% and
| |
| year (%DATUMOD%) = X.rok and
| |
| month (%DATUMOD%) = X.mesic
| |
| group by
| |
| A.os_cislo,
| |
| A.prijmeni,
| |
| A.jmeno,
| |
| X.k_vyplate,
| |
| X.soc_zakl,
| |
| T.soc_zakl
| |
| </small>
| |
|
| |
| == Odkazy ==
| |
| * [[Komplexní zkoušky mezd]]
| |
