SELECT i.idugylet AS id, m.id AS idmunkatars, aj.irhato, aj.szerepkor, NULL AS funkciojogosultsag
FROM munkatars m
         JOIN (SELECT idmunkatars, idszolgaltatas,irhato,szerepkor FROM ta_munkatars_jog WHERE sysdate BETWEEN valid_from AND valid_to AND szerepkor NOT IN ('O', 'X')) aj ON (m.id=aj.idmunkatars)
         JOIN ta_igenyles i ON (i.idszolgaltatas=aj.idszolgaltatas)
UNION ALL
SELECT
    i.idugylet AS id, NULL ,1 AS irhato,NULL, 'IgenylesKorlatlanKereses' AS funkciojogosultsag
FROM
    ta_igenyles i
UNION ALL
SELECT
    i.idugylet AS id, i.ID_SEGITO ,1 AS irhato,'O', NULL
FROM
    ta_igenyles i
WHERE i.id_segito IS NOT NULL
UNION ALL
SELECT u.id,m.id AS idmunkatars,1 AS irhato, 'E', NULL AS funkciojogosultsag
FROM munkatars m
         JOIN (SELECT idmunkatars, idszolgaltatas,irhato,szerepkor FROM ta_munkatars_jog WHERE sysdate BETWEEN valid_from AND valid_to AND szerepkor IN ('E')) aj ON (m.id=aj.idmunkatars)
         JOIN nrszh_szolgaltatas sz ON sz.id = aj.idszolgaltatas
         JOIN nrszh_mukodesihely mh ON mh.id = sz.IDMUKODESIHELY
         JOIN ta_szocialisdiagnozis szd ON szd.CSJGYK_KOZP_ID = mh.id
         JOIN ugylet u ON u.id = szd.IDUGYLET
UNION ALL
SELECT szd.idugylet AS id, NULL AS idmunkatars, 1 AS irhato, NULL, 'DiagnozisKorlatlanSzerkeszt' AS funkciojogosultsag
FROM
    ta_szocialisdiagnozis szd;