UFN_JOB_TRAITS

            CREATE function dbo.UFN_JOB_TRAITS(@JOBID uniqueidentifier)
            returns @TRAITS table 
                (
                    ID uniqueidentifier, 
                    TRAITTYPEID tinyint,
                    TRAITTYPE nvarchar(25),
                    SKILL nvarchar(100),
                    SKILLLEVEL nvarchar(100),
                    SKILLLEVELSEQUENCE int,
                    INTEREST nvarchar(100),
                    SPECIALNEED nvarchar(100),
                    CERTIFICATION nvarchar(100),
                    COURSE nvarchar(100),
                    LICENSE nvarchar(100),
                    MEDICAL nvarchar(100),
                    ADMINISTRATIVE nvarchar(100),
                    ISREQUIRED bit
                ) with execute as caller
            as begin
                --Administrative

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, ADMINISTRATIVE, ISREQUIRED)
                    select    JA.ID, 
                            0,
                            'Administrative',
                            ADMINISTRATIVE.DESCRIPTION,
                            JA.ISREQUIRED
                    from JOBADMINISTRATIVE JA
                    inner join VOLUNTEERADMINISTRATIVECODE ADMINISTRATIVE on ADMINISTRATIVE.ID = JA.ADMINISTRATIVECODEID
                    where JA.JOBID= @JOBID;

                --Certification

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, CERTIFICATION, ISREQUIRED)
                    select    JC.ID, 
                            1,
                            'Certification',
                            CERTIFICATION.DESCRIPTION,
                            JC.ISREQUIRED
                    from JOBCERTIFICATION JC
                    inner join VOLUNTEERCERTIFICATIONCODE CERTIFICATION on CERTIFICATION.ID = JC.CERTIFICATIONCODEID
                    where JC.JOBID= @JOBID;

                --Course

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, COURSE, ISREQUIRED)
                    select    JC.ID, 
                            2,
                            'Course',
                            COURSE.DESCRIPTION,
                            JC.ISREQUIRED
                    from JOBCOURSE JC
                    inner join VOLUNTEERCOURSECODE COURSE on COURSE.ID = JC.COURSECODEID
                    where JC.JOBID = @JOBID;

                --Interest

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, INTEREST, ISREQUIRED)
                    select    JI.ID, 
                            3,
                            'Interest',
                            INTEREST.DESCRIPTION,
                            JI.ISREQUIRED
                    from JOBINTEREST JI
                    inner join VOLUNTEERINTERESTCODE INTEREST on INTEREST.ID = JI.VOLUNTEERINTERESTCODEID
                    where JI.JOBID= @JOBID;

                --License

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, LICENSE, ISREQUIRED)
                    select    JL.ID, 
                            4,
                            'License',
                            LICENSE.DESCRIPTION,
                            JL.ISREQUIRED
                    from JOBLICENSE JL
                    inner join VOLUNTEERLICENSECODE LICENSE on LICENSE.ID = JL.LICENSECODEID
                    where JL.JOBID = @JOBID;

                --Medical

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, MEDICAL, ISREQUIRED)
                    select    JM.ID, 
                            5,
                            'Medical',
                            MEDICAL.DESCRIPTION,
                            JM.ISREQUIRED
                    from JOBMEDICAL JM
                    inner join VOLUNTEERMEDICALCODE MEDICAL on MEDICAL.ID = JM.MEDICALCODEID
                    where JM.JOBID = @JOBID;

                --Skills

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, SKILL, SKILLLEVEL, SKILLLEVELSEQUENCE, ISREQUIRED)
                    select    JS.ID,
                            6,
                            'Skill',
                            SKILL.DESCRIPTION,
                            SKILLLEVEL.DESCRIPTION,
                            SKILLLEVEL.SEQUENCE,
                            JS.ISREQUIRED
                    from JOBSKILL JS
                    inner join VOLUNTEERSKILLCODE SKILL on SKILL.ID = JS.SKILLCODEID
                    inner join VOLUNTEERSKILLLEVEL SKILLLEVEL on SKILLLEVEL.ID = JS.SKILLLEVELID
                    where JS.JOBID=@JOBID;

                --Special need

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, SPECIALNEED, ISREQUIRED)
                    select    JS.ID, 
                            7,
                            'Special Need',
                            SPECIALNEED.DESCRIPTION,
                            0
                    from JOBSPECIALNEED JS
                    inner join VOLUNTEERSPECIALNEEDCODE SPECIALNEED on SPECIALNEED.ID = JS.SPECIALNEEDCODEID
                    where JS.JOBID= @JOBID;

                return;
            end