UFN_JOBOCCURRENCE_TRAITIDS

            CREATE function dbo.UFN_JOBOCCURRENCE_TRAITIDS(@JOBOCCURRENCEID uniqueidentifier)
            returns @TRAITS table 
                (
                    ID uniqueidentifier, 
                    JOBOCCURRENCEID uniqueidentifier, 
                    TRAITTYPEID tinyint,
                    TRAITTYPE nvarchar(25),
                    SKILLCODEID uniqueidentifier,
                    SKILLLEVEL int,
                    VOLUNTEERINTERESTCODEID uniqueidentifier,
                    SPECIALNEEDCODEID uniqueidentifier,
                    CERTIFICATIONCODEID uniqueidentifier,
                    COURSECODEID uniqueidentifier,
                    LICENSECODEID uniqueidentifier,
                    MEDICALCODEID uniqueidentifier,
                    ADMINISTRATIVECODEID uniqueidentifier,
                    ISREQUIRED bit
                ) with execute as caller
            as begin

                declare @JOBID uniqueidentifier;

                select @JOBID = JOBID
                from JOBOCCURRENCE
                where JOBOCCURRENCE.ID = @JOBOCCURRENCEID;

                --Administrative

                insert into @TRAITS(ID, JOBOCCURRENCEID, TRAITTYPEID, TRAITTYPE, ADMINISTRATIVECODEID, ISREQUIRED)
                    select    JA.ID, 
                            @JOBOCCURRENCEID, 
                            0,
                            'Administrative',
                            JA.ADMINISTRATIVECODEID,
                            JA.ISREQUIRED
                    from dbo.JOBADMINISTRATIVE JA
                    where JA.JOBID= @JOBID;

                --Certification

                insert into @TRAITS(ID, JOBOCCURRENCEID, TRAITTYPEID, TRAITTYPE, CERTIFICATIONCODEID, ISREQUIRED)
                    select    JC.ID, 
                            @JOBOCCURRENCEID, 
                            1,
                            'Certification',
                            JC.CERTIFICATIONCODEID,
                            JC.ISREQUIRED
                    from dbo.JOBCERTIFICATION JC
                    where JC.JOBID= @JOBID;

                --Course

                insert into @TRAITS(ID, JOBOCCURRENCEID, TRAITTYPEID, TRAITTYPE, COURSECODEID, ISREQUIRED)
                    select    JC.ID, 
                            @JOBOCCURRENCEID, 
                            2,
                            'Course',
                            JC.COURSECODEID,
                            JC.ISREQUIRED
                    from dbo.JOBCOURSE JC
                    where JC.JOBID = @JOBID;

                --Interest

                insert into @TRAITS(ID, JOBOCCURRENCEID, TRAITTYPEID, TRAITTYPE, VOLUNTEERINTERESTCODEID, ISREQUIRED)
                    select    JI.ID, 
                            @JOBOCCURRENCEID, 
                            3,
                            'Interest',
                            JI.VOLUNTEERINTERESTCODEID,
                            JI.ISREQUIRED
                    from dbo.JOBINTEREST JI
                    where JI.JOBID= @JOBID;

                --License

                insert into @TRAITS(ID, JOBOCCURRENCEID, TRAITTYPEID, TRAITTYPE, LICENSECODEID, ISREQUIRED)
                    select    JL.ID, 
                            @JOBOCCURRENCEID, 
                            4,
                            'License',
                            JL.LICENSECODEID,
                            JL.ISREQUIRED
                    from dbo.JOBLICENSE JL
                    where JL.JOBID = @JOBID;

                --Medical

                insert into @TRAITS(ID, JOBOCCURRENCEID, TRAITTYPEID, TRAITTYPE, MEDICALCODEID, ISREQUIRED)
                    select    JM.ID, 
                            @JOBOCCURRENCEID, 
                            5,
                            'Medical',
                            JM.MEDICALCODEID,
                            JM.ISREQUIRED
                    from dbo.JOBMEDICAL JM
                    where JM.JOBID = @JOBID;

                --Skills

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

                --Special need

                insert into @TRAITS(ID, JOBOCCURRENCEID, TRAITTYPEID, TRAITTYPE, SPECIALNEEDCODEID, ISREQUIRED)
                    select    JS.ID, 
                            @JOBOCCURRENCEID, 
                            7,
                            'Special Need',
                            JS.SPECIALNEEDCODEID,
                            0
                    from dbo.JOBSPECIALNEED JS
                    where JS.JOBID= @JOBID;

                return;
            end