gistfile1.sql

             SELECT
                            distinct vo.obj_seq,
                            vo.obj_org_seq,
                            vo.obj_navn,
                            vo.obj_fornavn,
                            vo.obj_etternavn,
                            vo.obj_fornavn||' '||vo.obj_etternavn navn,
                            nb.etatsnavn,
                            vo.nlp_bruker,
                            vo.obj_type_id,
                            oe.oep_epostadr,
                            domene_ident,
                            vo.org_navn,
                            vo.org_ident,
                            vo.org_seq,
                            PKG_KONTAKT.F_GET_TLFNR_PRIORITERT(vo.obj_seq) tlf,
                            PKG_KONTAKT.F_GET_IDENT(vo.obj_seq,'oppe') oppe,
                            PKG_KONTAKT.f_get_tlfnr(vo.obj_seq,'mob') mobil,
                            vo.obj_beskrivelse,
                            org.org_inngaar_i_org,
                            initcap(nvl(PKG_STILLING.f_get_tekst(vo.obj_seq, vo.nlp_bruker),vo.obj_type_id)) stilling,
                            vo.gradering,
                            pkg_profil.f_get_ident(vo.gradering) grad_ident,
                            pkg_profil.f_get_verdi(vo.gradering) grad_verdi,
                            decode(PKG_STILLING.f_get_status(vo.obj_seq, vo.nlp_bruker),'M', decode(PKG_STILLING.f_exists_permitering(vo.obj_seq, vo.nlp_bruker, sysdate), '1','Permisjon',''), '') midl_stopp,
                            vo2.obj_org_seq rel_oo_seq,
                            vo2.org_navn rel_o_navn,
                            vo2.org_ident rel_o_ident,
                            vo2.org_seq rel_o_seq,
                            vo.org_02180_faktenhet,
                            vo.obj_org_merknad
					FROM   	vobj_org vo, nlp_bruker nb, obj_epost oe, domene d, organisasjon org, vobj_org vo2
							
					WHERE	
							vo.nlp_bruker		> 0					AND		
					 		vo.org_seq			= org.org_seq		AND
					 		vo.org_type_id		= 'TJENESTE'		AND
                            vo2.org_type_id     = 'ORG'             AND
					 		vo2.obj_seq			= vo.obj_seq		AND
							vo2.nlp_bruker		= vo.nlp_bruker 	AND 
							vo.nlp_bruker		= nb.nlp_bruker 	AND 		
							vo.nlp_bruker 		= d.nlp_bruker 		AND
							vo.obj_seq 			= oe.obj_seq (+) 	AND
              				vo.nlp_bruker       = oe.nlp_bruker (+) AND							
							oe.oep_epost_type (+) = 'M' 			AND
						 	oe.oep_inaktiv (+) is NULL  			AND
                            
            ( vo.obj_type_id <> 'NLPANS'		OR
                (vo.obj_type_id = 'NLPANS' and
                    (
                        ( PKG_STILLING.f_exists(vo.obj_seq, vo.nlp_bruker, sysdate) = 1)
                        or
                        (exists
                            (select null
                            from agrhr_ansettelsesforhold aaf, agrhr_ansatt aa
                            where
                                aaf.agrhr_org_id in (
                                select agrhr_org_id from agrhr_organisasjon ao where ao.org_seq in
                                    (
                                        select org_seq from organisasjon where nlp_bruker = vo.nlp_bruker
                                    )
                                )
                            and aa.agrhr_ansatt_id = aaf.agrhr_ansatt_id
                            and aa.obj_seq = vo.obj_seq
                            and aaf.statuskode = 'N'
                            and nvl(aaf.til_dato,sysdate +1) > sysdate
                            )
                        )
                    ) 
                 )
             )
        
                            and
												
							(vo.obj_inaktiv is null or vo.obj_inaktiv <> 'J')
					AND		vo.org_seq = 11914 
					
					ORDER BY to_number(nvl(vo.obj_org_merknad,99)), vo.obj_navn