CREATE OR ALTER PROCEDURE LD0SPSearchEnrollment (@searchCriteria_ xml)
AS
BEGIN
IF(@searchCriteria_ is null) 
BEGIN
       raiserror('xml-string can not be null', 11, 1);
END;

DECLARE @PersonId_ VARCHAR(12);
DECLARE @PeriodFrom_ VARCHAR(12);
DECLARE @PeriodTo_ VARCHAR(12);
DECLARE @ProfessionalGroupAtEnd_  VARCHAR(100);
DECLARE @DirectiveClosureProfession2012_  VARCHAR(100);
DECLARE @NoEducationalBackground_  VARCHAR(100);
DECLARE @NoWorkExperience_ BIT;
DECLARE @NoWorkLikeSkills_  BIT;
DECLARE @YearOfBirthFrom_  VARCHAR(100);
DECLARE @YearOfBirthTo_  VARCHAR(100);
DECLARE @AgeVerification_  VARCHAR(100);
DECLARE @GenderVerification_  VARCHAR(100);
DECLARE @EnrollmentPeriodVerification_  VARCHAR(100);
DECLARE @OnlyLatest_  BIT;
DECLARE @ShowDirectiveDetail_ BIT;
DECLARE @OrganizationId_  VARCHAR(100);
DECLARE @OrganizationUnitId_  VARCHAR(100);
DECLARE @AreaId_  VARCHAR(100);
DECLARE @AreaName_  VARCHAR(100);
DECLARE @UnitId_    VARCHAR(100); 
DECLARE @UnitName_  VARCHAR(100); 
DECLARE @ClosingReasonId_  VARCHAR(100);
DECLARE @EndReasonId_  VARCHAR(100);
DECLARE @ExternalOrganizationId_  VARCHAR(100);
DECLARE @DirectiveOrganizationType_  VARCHAR(100);
DECLARE @DirectiveOrganizationInternalId_  VARCHAR(100);
DECLARE @DirectiveOrganizationInternalUnitId_  VARCHAR(100);

DECLARE @DirectiveCaseworker_  VARCHAR(100);
DECLARE @DirectiveClosureType_  VARCHAR(100);
DECLARE @DirectiveClosureReasonType_  VARCHAR(100);
DECLARE @EnrollmentResponsibilityTypeId_  DECIMAL(10);
DECLARE @EnrollmentResponsibilityOrganizationUnitId_  VARCHAR(100);
DECLARE @EnrollmentResponsibilityOrganizationId_  VARCHAR(100);

DECLARE @ResponsibleCaseworker_ VARCHAR(100);
DECLARE @ResponsibleFromDate_ VARCHAR(12);
DECLARE @ResponsibleToDate_ VARCHAR(12);
DECLARE @SearchGroupId_  VARCHAR(10);

DECLARE @MaxResult_ INT

--Extract data from the XML structure
--@AreaId_ vid läsning mot J21
--@UnitId vid läsning mot Q01 kodtyp OMRÅDE
SELECT
       @PersonId_= ref.value('PersonId[1]/@Value', 'varchar(12)'),
          @PeriodFrom_ = ref.value('PeriodFrom[1]/@Value', 'varchar(12)'),
          @PeriodTo_ = ref.value('PeriodTo[1]/@Value', 'varchar(12)'),
          @ProfessionalGroupAtEnd_ = ref.value('ProfessionalGroupAtEnd[1]/@Value', 'varchar(100)'),
          @DirectiveClosureProfession2012_ = ref.value('DirectiveClosureProfession2012[1]/@Value', 'varchar(100)'),
          @NoEducationalBackground_ = ref.value('NoEducationalBackground[1]/@Value', 'varchar(100)'),
          @NoWorkExperience_ = ref.value('NoWorkExperience[1]/@Value', 'bit'),
          @NoWorkLikeSkills_ = ref.value('NoWorkLikeSkills[1]/@Value', 'bit'),
          @YearOfBirthFrom_ = ref.value('YearOfBirthFrom[1]/@Value', 'varchar(100)'),
          @YearOfBirthTo_ = ref.value('YearOfBirthTo[1]/@Value', 'varchar(100)'),
          @AgeVerification_ = ref.value('AgeVerification[1]/@Value', 'varchar(100)'),
          @GenderVerification_ = ref.value('GenderVerification[1]/@Value', 'varchar(100)'),
          @EnrollmentPeriodVerification_ = ref.value('EnrollmentPeriodVerification[1]/@Value', 'varchar(100)'),
          @OnlyLatest_ = ref.value('OnlyLatest[1]/@Value', 'bit'),
          @ShowDirectiveDetail_ = ref.value('ShowDirectiveDetail[1]/@Value', 'bit'),
          @OrganizationId_ = ref.value('OrganizationId[1]/@Value', 'varchar(100)'),
          @OrganizationUnitId_ = ref.value('OrganizationUnitId[1]/@Value', 'varchar(100)'),
          @AreaId_ = ref.value('AreaId[1]/@Value', 'varchar(100)'),
          @AreaName_ = ref.value('AreaName[1]/@Value', 'varchar(100)'),
          @UnitId_ = ref.value('UnitId[1]/@Value', 'varchar(100)'),
          @UnitName_ = ref.value('UnitName[1]/@Value', 'varchar(100)'),
          @ClosingReasonId_ = ref.value('ClosingReasonId[1]/@Value', 'varchar(100)'),
          @EndReasonId_ = ref.value('EndReasonId[1]/@Value', 'varchar(100)'),
          @ExternalOrganizationId_ = ref.value('ExternalOrganizationId[1]/@Value', 'varchar(100)'),
		      @DirectiveOrganizationType_ = ref.value('DirectiveOrganizationType[1]/@Value', 'varchar(100)'),
          @DirectiveOrganizationInternalId_ = ref.value('DirectiveOrganizationInternalId[1]/@Value', 'varchar(100)'),
          @DirectiveOrganizationInternalUnitId_ = ref.value('DirectiveOrganizationInternalUnitId[1]/@Value', 'varchar(100)'),
          @DirectiveCaseworker_ = ref.value('DirectiveCaseworker[1]/@Value', 'varchar(100)'),
          @DirectiveClosureType_ = ref.value('DirectiveClosureType[1]/@Value', 'varchar(100)'),
          @DirectiveClosureReasonType_ = ref.value('DirectiveClosureReasonType[1]/@Value', 'varchar(100)'),
          @EnrollmentResponsibilityTypeId_ = ref.value('EnrollmentResponsibilityTypeId[1]/@Value', 'decimal(10)'),
          @EnrollmentResponsibilityOrganizationUnitId_ = ref.value('EnrollmentResponsibilityOrganizationUnitId[1]/@Value', 'varchar(100)'),
          @EnrollmentResponsibilityOrganizationId_ = ref.value('EnrollmentResponsibilityOrganizationId[1]/@Value', 'varchar(100)'),
          @ResponsibleCaseworker_ = ref.value('ResponsibleCaseworker[1]/@Value', 'varchar(100)'),
 		      @ResponsibleFromDate_ = ref.value('ResponsibleFromDate[1]/@Value', 'varchar(12)'),
 		      @ResponsibleToDate_ = ref.value('ResponsibleToDate[1]/@Value', 'varchar(12)'),
          @SearchGroupId_ = ref.value('SearchGroupId[1]/@Value', 'varchar(10)'),
          @MaxResult_ = ref.value('MaxResult[1]/@Value', 'varchar(100)')
FROM @searchCriteria_.nodes( '/SearchEnrollmentSPCriteria' ) as node(ref);


IF (@maxResult_ is Null)
BEGIN
       SET @maxResult_=100
END
                                                                                                                                            

IF OBJECT_ID('tempdb.. #TEMPRESULT') IS NOT NULL
    DROP TABLE #TEMPRESULT																					

CREATE TABLE #TEMPRESULT(
INSKRIVNINGSNR decimal(10,0),
PERSONNR varchar(100),
INLEDDAT varchar(100),
AVSLUTDAT varchar(100),
UPDDAT varchar(100),
UPDSIGN varchar(100),
FORLANGDINTRO varchar(100),
TYPAVSLUT decimal(10,0),
AVSLUTORS decimal(10,0),
AVSLUTKOMMUN decimal(10,0),
AVSLUTLAND decimal(10,0),
KOMMUNUTLAND varchar(100),
ORGID decimal(10,0),
ORGENHNR varchar(100),
YRKEAVSLUT varchar(100),
INSKRTYP decimal(10,0),
INGENUTBILDN decimal(10,0),
INGENARBLIVERF decimal(10,0),
INGENFARDIGHET decimal(10,0),
UTANARB decimal(10,0),
YRKEAVSLUT_2012 varchar(100),
NAMN varchar(180),
ADRESS varchar(100),
COADR varchar(100),
POSTNR varchar(100),
ORT varchar(100),
HEMTEL varchar(100),
ARBTEL varchar(100),
KADRESS varchar(100),
KCOADR varchar(100),
KPOSTNR varchar(100),
KORT varchar(100),
KHEMTEL varchar(100),
KUATDAT varchar(100),
KFLYKTING decimal(10,0),
CIVKOD decimal(10,0),
CIVDAT varchar(100),
MEDBSKAP decimal(10,0),
MEDBDAT varchar(100),
FODLAN decimal(10,0),
FODLAND decimal(10,0),
OMRADE decimal(10,0),
FOLKFORS decimal(10,0),
FOLKLAN decimal(10,0),
FOLKKOM decimal(10,0),
FOLKDAT varchar(100),
AVREGORS decimal(10,0),
AVREGDAT varchar(100),
ADRSKYDD decimal(10,0),
HEMLIGTFN decimal(10,0),
PUPDDAT varchar(100),
PUPDSIGN varchar(100),
KUPDDAT varchar(100),
KUPDSIGN varchar(100),
KOMDEL decimal(10,0),
BETJOMR decimal(10,0),
NYCKKOD varchar(100),
FODDAT varchar(100),
KON decimal(10,0),
PERSTEL varchar(100),
MOBIL varchar(100),
FAX varchar(100),
EPOSTHEM varchar(100),
EPOSTARB varchar(100),
MODMAL decimal(10,0),
KOMSPRAK decimal(10,0),
KOMSPRAK2 decimal(10,0),
KOMSPRAK3 decimal(10,0),
TOLKBEHOV decimal(10,0),
INVANDAT varchar(100),
KUNDNUMMER decimal(10,0),
SKYDDADFOLKBOKF decimal(10,0),
MINSIDA_ARBETSMARKNAD decimal(10,0),
PRIMART_SPRAK varchar(100),
U11ANVISORGTYP int,
U11ANVISORGINTERN varchar(100),
U11ORGEXTERN int,
U11HANDLAGGARE varchar(100),
U11FROMDAT varchar(100),
U11TOMDAT varchar(100),
U11TYPAVSLUT int,
U11AVSLUTORS int,
U11AVSLUTKOMMUN INT,
U11AVSLUTLAND int,
U11KOMMUNUTLAND varchar(100),
U11YRKEAVSLUT varchar(100),
U11YRKEAVSLUT_2012 varchar(100),
OMRENHNR VARCHAR(20),
OVRIG1 decimal(10,0),
OVRIG2 decimal(10,0),
OVRIG3 decimal(10,0)
)                                                                                                                                            

DECLARE @ResponsibleCaseWorkers TABLE (HANDLAGGARE VARCHAR(100))
IF(@searchGroupId_ IS NOT NULL)
BEGIN
  INSERT INTO @ResponsibleCaseWorkers
  SELECT DISTINCT HAND
	FROM LD0V0I41 
	WHERE SOKGRUPPNR = @SearchGroupId_
END
IF NOT EXISTS(SELECT * FROM @ResponsibleCaseWorkers) AND @ResponsibleCaseworker_ IS NOT NULL
BEGIN
  INSERT INTO @ResponsibleCaseWorkers
  SELECT @ResponsibleCaseworker_
END
DECLARE @SkipCaseWorkerFilter BIT = 0 
IF NOT EXISTS(SELECT * FROM @ResponsibleCaseWorkers) SET @SkipCaseWorkerFilter = 1

DECLARE @ResponsibleOrganizations TABLE (ORGID VARCHAR(100))
IF(@searchGroupId_ IS NOT NULL)
BEGIN
  INSERT INTO @ResponsibleOrganizations
  SELECT DISTINCT ORGID
  FROM LD0V0I42 
  WHERE SOKGRUPPNR = @SearchGroupId_
END
IF NOT EXISTS(SELECT * FROM @ResponsibleOrganizations) AND @EnrollmentResponsibilityOrganizationUnitId_ IS NOT NULL
BEGIN
  INSERT INTO @ResponsibleOrganizations
  SELECT @EnrollmentResponsibilityOrganizationUnitId_
END
DECLARE @UseOrganizationFilter BIT = 0
IF EXISTS(SELECT * FROM @ResponsibleOrganizations) SET @UseOrganizationFilter = 1

IF @ShowDirectiveDetail_ = 0
BEGIN
    INSERT INTO #TEMPRESULT(INSKRIVNINGSNR,	PERSONNR,	INLEDDAT,	AVSLUTDAT,	UPDDAT,	UPDSIGN,	FORLANGDINTRO,	TYPAVSLUT,	AVSLUTORS,	AVSLUTKOMMUN,	AVSLUTLAND,	KOMMUNUTLAND,	ORGID,	ORGENHNR,	YRKEAVSLUT,	INSKRTYP,	INGENUTBILDN,	INGENARBLIVERF,	INGENFARDIGHET,	UTANARB,	YRKEAVSLUT_2012,	NAMN,	ADRESS,	COADR,	POSTNR,	ORT,	HEMTEL,	ARBTEL,	KADRESS,	KCOADR,	KPOSTNR,	KORT,	KHEMTEL,	KUATDAT,	KFLYKTING,	CIVKOD,	CIVDAT,	MEDBSKAP,	MEDBDAT,	FODLAN,	FODLAND,	OMRADE,	FOLKFORS,	FOLKLAN,	FOLKKOM,	FOLKDAT,	AVREGORS,	AVREGDAT,	ADRSKYDD,	HEMLIGTFN,	PUPDDAT,	PUPDSIGN,	KUPDDAT,	KUPDSIGN,	KOMDEL,	BETJOMR,	NYCKKOD,	FODDAT,	KON,	PERSTEL,	MOBIL,	FAX,	EPOSTHEM,	EPOSTARB,	MODMAL,	KOMSPRAK,	KOMSPRAK2,	KOMSPRAK3,	TOLKBEHOV,	INVANDAT,	KUNDNUMMER,	SKYDDADFOLKBOKF,	MINSIDA_ARBETSMARKNAD,	PRIMART_SPRAK,	U11ANVISORGTYP,	U11ANVISORGINTERN,	U11ORGEXTERN,	U11HANDLAGGARE,	U11FROMDAT,	U11TOMDAT,	U11TYPAVSLUT,	U11AVSLUTORS,	U11AVSLUTKOMMUN,	U11AVSLUTLAND,	U11KOMMUNUTLAND,	U11YRKEAVSLUT,	U11YRKEAVSLUT_2012,OMRENHNR, OVRIG1, OVRIG2, OVRIG3)
    SELECT INSKRIVNINGSNR,	PERSONNR,	INLEDDAT,	AVSLUTDAT,	LD0V1U10Ext.UPDDAT,	LD0V1U10Ext.UPDSIGN,	FORLANGDINTRO,	TYPAVSLUT,	AVSLUTORS,	AVSLUTKOMMUN,	AVSLUTLAND,	KOMMUNUTLAND,	ORGID,	ORGENHNR,	YRKEAVSLUT,	INSKRTYP,	INGENUTBILDN,	INGENARBLIVERF,	INGENFARDIGHET,	UTANARB,	YRKEAVSLUT_2012,	LD0V1U10Ext.NAMN,	ADRESS,	COADR,	POSTNR,	ORT,	HEMTEL,	ARBTEL,	KADRESS,	KCOADR,	KPOSTNR,	KORT,	KHEMTEL,	KUATDAT,	KFLYKTING,	CIVKOD,	CIVDAT,	MEDBSKAP,	MEDBDAT,	FODLAN,	FODLAND,	OMRADE,	FOLKFORS,	FOLKLAN,	FOLKKOM,	FOLKDAT,	AVREGORS,	AVREGDAT,	ADRSKYDD,	HEMLIGTFN,	PUPDDAT,	PUPDSIGN,	KUPDDAT,	KUPDSIGN,	KOMDEL,	BETJOMR,	LD0V1U10Ext.NYCKKOD,	FODDAT,	KON,	PERSTEL,	MOBIL,	FAX,	EPOSTHEM,	EPOSTARB,	MODMAL,	KOMSPRAK,	KOMSPRAK2,	KOMSPRAK3,	TOLKBEHOV,	INVANDAT,	KUNDNUMMER,	SKYDDADFOLKBOKF,	MINSIDA_ARBETSMARKNAD,	PRIMART_SPRAK,	U11ANVISORGTYP,	U11ANVISORGINTERN,	U11ORGEXTERN,	U11HANDLAGGARE,	U11FROMDAT,	U11TOMDAT,	U11TYPAVSLUT,	U11AVSLUTORS,	U11AVSLUTKOMMUN,	U11AVSLUTLAND,	U11KOMMUNUTLAND,	U11YRKEAVSLUT,	U11YRKEAVSLUT_2012, OMR.OMRENHNR, OVRIG1, OVRIG2, OVRIG3
    FROM LD0V1U10Ext 
    LEFT JOIN LD0P0J21 J21 ON
    J21.NYCKKOD=LD0V1U10Ext.NYCKKOD
    LEFT JOIN LD0VAA0OMR OMR ON
    OMR.OMRENHNR=J21.OMRENHNR AND OMR.OMRID = J21.OMRID
    WHERE PERSONNR LIKE '%'+ @PersonId_ +'%'
END
ELSE IF @ShowDirectiveDetail_ = 1
BEGIN
    INSERT INTO #TEMPRESULT(INSKRIVNINGSNR,	PERSONNR,	INLEDDAT,	AVSLUTDAT,	UPDDAT,	UPDSIGN,	FORLANGDINTRO,	TYPAVSLUT,	AVSLUTORS,	AVSLUTKOMMUN,	AVSLUTLAND,	KOMMUNUTLAND,	ORGID,	ORGENHNR,	YRKEAVSLUT,	INSKRTYP,	INGENUTBILDN,	INGENARBLIVERF,	INGENFARDIGHET,	UTANARB,	YRKEAVSLUT_2012,	NAMN,	ADRESS,	COADR,	POSTNR,	ORT,	HEMTEL,	ARBTEL,	KADRESS,	KCOADR,	KPOSTNR,	KORT,	KHEMTEL,	KUATDAT,	KFLYKTING,	CIVKOD,	CIVDAT,	MEDBSKAP,	MEDBDAT,	FODLAN,	FODLAND,	OMRADE,	FOLKFORS,	FOLKLAN,	FOLKKOM,	FOLKDAT,	AVREGORS,	AVREGDAT,	ADRSKYDD,	HEMLIGTFN,	PUPDDAT,	PUPDSIGN,	KUPDDAT,	KUPDSIGN,	KOMDEL,	BETJOMR,	NYCKKOD,	FODDAT,	KON,	PERSTEL,	MOBIL,	FAX,	EPOSTHEM,	EPOSTARB,	MODMAL,	KOMSPRAK,	KOMSPRAK2,	KOMSPRAK3,	TOLKBEHOV,	INVANDAT,	KUNDNUMMER,	SKYDDADFOLKBOKF,	MINSIDA_ARBETSMARKNAD,	PRIMART_SPRAK,	U11ANVISORGTYP,	U11ANVISORGINTERN,	U11ORGEXTERN,	U11HANDLAGGARE,	U11FROMDAT,	U11TOMDAT,	U11TYPAVSLUT,	U11AVSLUTORS,	U11AVSLUTKOMMUN,	U11AVSLUTLAND,	U11KOMMUNUTLAND,	U11YRKEAVSLUT,	U11YRKEAVSLUT_2012,OMRENHNR, OVRIG1, OVRIG2, OVRIG3)
    SELECT INSKRIVNINGSNR,	PERSONNR,	INLEDDAT,	AVSLUTDAT,	LD0V2U10Ext.UPDDAT,	LD0V2U10Ext.UPDSIGN,	FORLANGDINTRO,	TYPAVSLUT,	AVSLUTORS,	AVSLUTKOMMUN,	AVSLUTLAND,	KOMMUNUTLAND,	ORGID,	ORGENHNR,	YRKEAVSLUT,	INSKRTYP,	INGENUTBILDN,	INGENARBLIVERF,	INGENFARDIGHET,	UTANARB,	YRKEAVSLUT_2012,	LD0V2U10Ext.NAMN,	ADRESS,	COADR,	POSTNR,	ORT,	HEMTEL,	ARBTEL,	KADRESS,	KCOADR,	KPOSTNR,	KORT,	KHEMTEL,	KUATDAT,	KFLYKTING,	CIVKOD,	CIVDAT,	MEDBSKAP,	MEDBDAT,	FODLAN,	FODLAND,	OMRADE,	FOLKFORS,	FOLKLAN,	FOLKKOM,	FOLKDAT,	AVREGORS,	AVREGDAT,	ADRSKYDD,	HEMLIGTFN,	PUPDDAT,	PUPDSIGN,	KUPDDAT,	KUPDSIGN,	KOMDEL,	BETJOMR,	LD0V2U10Ext.NYCKKOD,	FODDAT,	KON,	PERSTEL,	MOBIL,	FAX,	EPOSTHEM,	EPOSTARB,	MODMAL,	KOMSPRAK,	KOMSPRAK2,	KOMSPRAK3,	TOLKBEHOV,	INVANDAT,	KUNDNUMMER,	SKYDDADFOLKBOKF,	MINSIDA_ARBETSMARKNAD,	PRIMART_SPRAK,	U11ANVISORGTYP,	U11ANVISORGINTERN,	U11ORGEXTERN,	U11HANDLAGGARE,	U11FROMDAT,	U11TOMDAT,	U11TYPAVSLUT,	U11AVSLUTORS,	U11AVSLUTKOMMUN,	U11AVSLUTLAND,	U11KOMMUNUTLAND,	U11YRKEAVSLUT,	U11YRKEAVSLUT_2012, OMR.OMRENHNR, OVRIG1, OVRIG2, OVRIG3
    FROM LD0V2U10Ext
    LEFT JOIN LD0P0J21 J21 ON
    J21.NYCKKOD=LD0V2U10Ext.NYCKKOD
    LEFT JOIN LD0VAA0OMR OMR ON
    OMR.OMRENHNR=J21.OMRENHNR AND OMR.OMRID = J21.OMRID
    WHERE PERSONNR LIKE '%'+ @PersonId_ +'%'
END

SELECT DISTINCT TOP(@MaxResult_) 
U10.PERSONNR,U10.KUNDNUMMER	,U10.INSKRIVNINGSNR,U10.NAMN,	U10.ADRESS,	U10.COADR,	U10.ORT,U10.KON, 	U10.POSTNR,	U10.ARBTEL, U10.HEMTEL, U10.MOBIL,	U10.EPOSTHEM,		U10.FODDAT,PER.FODLAND_T AS FODLAND_T,	U10.INVANDAT,	U10.INLEDDAT,	U10.AVSLUTDAT,	U10.UPDDAT,	U10.UPDSIGN,	U10.FORLANGDINTRO,	U10.KOMMUNUTLAND,			
Q1.KLARTEXT AS TYPAVSLUT_T,Q2.KLARTEXT AS AVSLUTORS_T,Q3.KLARTEXT AS AVSLUTKOMMUN_T ,Q4.KLARTEXT AS AVSLUTLAND_T ,Q5.KLARTEXT AS INSKRTYP_T ,J01.NAMN AS ORGENH_T, Q6.Klartext AS UTANARB_T,U99_1.EXTTEXT AS YRKEAVSLUT_2012_T

,U99_1.EXTKOD AS YRKEAVSLUT_2012

,Q12.KLARTEXT AS U11ANVISORGTYP_T, J02.NAMN AS U11ANVISORGINTERN_T ,Q11.KLARTEXT AS U11ORGEXTERN_T,Q7.KLARTEXT AS U11TYPAVSLUT_T,Q8.KLARTEXT AS U11AVSLUTORS_T , U10.U11FROMDAT,	U10.U11TOMDAT ,I.NAMN AS U11HANDLAGGARE_T
,Q9.KLARTEXT AS U11AVSLUTKOMMUN_T,Q10.KLARTEXT AS U11AVSLUTLAND_T ,U10.U11KOMMUNUTLAND,U99_2.EXTKOD AS U11YRKEAVSLUT,U99_2.EXTTEXT AS U11YRKEAVSLUT_2012_T

,U10.INGENUTBILDN,	U10.INGENARBLIVERF,	U10.INGENFARDIGHET,
J21.NYCKKOD, J21.OMRENHNR,  J21.OMRID,
Q14.KOD AS Q14_KOD,
Q14.KLARTEXT AS Q14_KLARTEXT,
OMR.FOMDAT AS OMR_FOMDAT,
OMR.TOMDAT AS OMR_TOMDAT,
OMR.OMRID AS OMR_OMRID,
OMR.OMRENHNR AS OMR_OMRENHNR,
OMR.NAMN AS OMR_OMRADENAMN,
U10.OVRIG1, U10.OVRIG2, U10.OVRIG3,
Q15.KLARTEXT AS OVRIG1_T, Q16.KLARTEXT AS OVRIG2_T, Q17.KLARTEXT AS OVRIG3_T

FROM  #TEMPRESULT U10 

--??JOIN LD0VYA A ON U10.PERSONNR = A.PNR 
--LEFT OUTER JOIN LD0VAA0OMRADENKO OMRNKO ON OMRNKO.NYCKKOD = A.NYCKKOD
LEFT OUTER JOIN LD0V0J21 J21 ON J21.NYCKKOD=U10.NYCKKOD 
AND EXISTS (SELECT 'X' FROM LD0V0J10 J10 WHERE J10.OMRID = J21.OMRID AND J10.FOMDAT <= CONVERT(datetime, GETDATE(), 112) AND J10.TOMDAT >= CONVERT(VARCHAR, GETDATE(), 112) )

LEFT OUTER JOIN LD0VAA0OMR OMR 
ON OMR.OMRENHNR = J21.OMRENHNR AND OMR.OMRID = J21.OMRID 



LEFT JOIN LD0VAA0PER PER ON U10.PERSONNR = PER.PNR 

LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = U10.ORGID AND J01.ORGENHNR = U10.ORGENHNR
LEFT OUTER JOIN LD0V0J01 AS J02 ON J02.ORGID = U10.ORGID AND J02.ORGENHNR = U10.U11ANVISORGINTERN AND J02.ANVISORGAM = 1
--LEFT OUTER JOIN LD0V0J01 AS J03 ON J03.ORGID = U10.ORGID AND J03.ORGENHNR = U10.U12ANSVARIGORG 


LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNTYPAVSL' AND U10.TYPAVSLUT = Q1.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q2 ON Q2.KODTYP = 'LNAVSLORS' AND U10.AVSLUTORS = Q2.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q3 ON Q3.KODTYP = 'KOMU' AND U10.AVSLUTKOMMUN = Q3.KOD 
LEFT OUTER JOIN LD0V0Q01 AS Q4 ON Q4.KODTYP = 'LAND' AND U10.AVSLUTLAND = Q4.KOD 

LEFT OUTER JOIN LD0V0Q01 AS Q5 ON Q5.KODTYP = 'LNINSKTYP' AND U10.INSKRTYP = Q5.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q6 ON Q6.KODTYP = 'LNUTANARB' AND Q6.KOD = U10.UTANARB 

LEFT OUTER JOIN LD0V0Q01 AS Q7 ON Q7.KODTYP = 'LNTYPAVSL' AND U10.U11TYPAVSLUT = Q7.KOD 
LEFT OUTER JOIN LD0V0Q01 AS Q8 ON Q8.KODTYP = 'LNAVSLORS' AND U10.U11AVSLUTORS = Q8.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q9 ON Q9.KODTYP = 'KOMU' AND U10.U11AVSLUTKOMMUN = Q9.KOD 
LEFT OUTER JOIN LD0V0Q01 AS Q10 ON Q10.KODTYP = 'LAND' AND U10.U11AVSLUTLAND = Q10.KOD 

LEFT OUTER JOIN LD0V0Q01 AS Q11 ON Q11.KODTYP = 'LNANVISORG' AND Q11.KOD = U10.U11ORGEXTERN 
LEFT OUTER JOIN LD0V0Q01 AS Q12 ON Q12.KODTYP = 'LNANVISTYP' AND Q12.KOD = U10.U11ANVISORGTYP 
LEFT OUTER JOIN LD0V0Q01 AS Q14 ON Q14.KODTYP = 'OMRÅDE' AND Q14.KOD = U10.OMRADE

LEFT OUTER JOIN LD0V0Q01 AS Q15 ON Q15.KODTYP = 'LNOVRIG1' AND Q15.KOD = U10.OVRIG1
LEFT OUTER JOIN LD0V0Q01 AS Q16 ON Q16.KODTYP = 'LNOVRIG2' AND Q16.KOD = U10.OVRIG2
LEFT OUTER JOIN LD0V0Q01 AS Q17 ON Q17.KODTYP = 'LNOVRIG3' AND Q17.KOD = U10.OVRIG3

LEFT OUTER JOIN LD0V0U99 AS U99_1 ON U99_1.EXTKODTYP = 4 AND U99_1.EXTKOD = U10.YRKEAVSLUT_2012 AND U99_1.NIVA = 4 
LEFT OUTER JOIN LD0V0U99 AS U99_2 ON U99_2.EXTKODTYP = 4 AND U99_2.EXTKOD = U10.U11YRKEAVSLUT_2012 AND U99_2.NIVA = 4 
LEFT OUTER JOIN LD0V0I AS I ON I.HAND = U10.U11HANDLAGGARE	
--LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = U10.U12HANDLAGGARE
LEFT OUTER JOIN LD0V0U140 AS U140 ON U140.INSKRIVNINGSNR = U10.INSKRIVNINGSNR AND U140.AGARTYP = 12
LEFT OUTER JOIN LD0V0U12 AS U12 ON U12.ANSVARIGNR = U140.AGARNR


WHERE
 (@EnrollmentPeriodVerification_ IS NULL OR (@PeriodFrom_ IS NULL AND @PeriodTo_ IS NULL) OR (@EnrollmentPeriodVerification_ = 4
                                              OR U10.[INLEDDAT] <= CASE WHEN @EnrollmentPeriodVerification_ IN (1,2,3) 
                                                                         THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @PeriodTo_)), 112)  
                                                                         ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                               END)  

AND (@EnrollmentPeriodVerification_ IN (3) 
                                              OR U10.[AVSLUTDAT] >= CASE WHEN @EnrollmentPeriodVerification_ IN (1,2,4) 
                                                                          THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @PeriodFrom_)), 112) 
                                                                           ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)

AND (@EnrollmentPeriodVerification_ IN (1,2,4) 
                                              OR U10.[INLEDDAT] >= CASE WHEN @EnrollmentPeriodVerification_ = 3 
                                              THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @PeriodFrom_)), 112)  
                                              ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)

AND (@EnrollmentPeriodVerification_ IN (1,2,3) 
                                              OR U10.[AVSLUTDAT] <= CASE WHEN @EnrollmentPeriodVerification_ = 4 
                                              THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @PeriodTo_)), 112)  
                                              ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)

AND (@EnrollmentPeriodVerification_ IN (1,3,4)
											  OR U10.[AVSLUTDAT] = CASE WHEN @EnrollmentPeriodVerification_ IN (2) 
                                              THEN '99999999'  
                                              ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END))  
																																				  
AND

(@OnlyLatest_ IS NULL OR @OnlyLatest_ = 0 OR U10.AVSLUTDAT + U10.INSKRIVNINGSNR = (SELECT MAX(U10X.AVSLUTDAT + U10X.INSKRIVNINGSNR) 
                                                                     FROM LD0V1U10 U10X  
                                                                     WHERE U10X.PERSONNR = U10.PERSONNR 
                                                                     AND ( (@PeriodFrom_ IS NULL AND @PeriodTo_ IS NULL) OR (@EnrollmentPeriodVerification_ = 4
                                              OR U10X.[INLEDDAT] <= CASE WHEN @EnrollmentPeriodVerification_ IN (1,2,3) 
                                                                         THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @PeriodTo_)), 112)  
                                                                         ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                               END)  

AND (@EnrollmentPeriodVerification_ IN (3) 
                                              OR U10X.[AVSLUTDAT] >= CASE WHEN @EnrollmentPeriodVerification_ IN (1,2,4) 
                                                                          THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @PeriodFrom_)), 112) 
                                                                           ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)

AND (@EnrollmentPeriodVerification_ IN (1,2,4) 
                                              OR U10X.[INLEDDAT] >= CASE WHEN @EnrollmentPeriodVerification_ = 3 
                                              THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @PeriodFrom_)), 112)  
                                              ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)

AND (@EnrollmentPeriodVerification_ IN (1,2,3) 
                                              OR U10X.[AVSLUTDAT] <= CASE WHEN @EnrollmentPeriodVerification_ = 4 
                                              THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @PeriodTo_)), 112)  
                                              ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)

AND (@EnrollmentPeriodVerification_ IN (1,3,4)
											  OR U10X.[AVSLUTDAT] = CASE WHEN @EnrollmentPeriodVerification_ IN (2) 
                                              THEN '99999999'  
                                              ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)))) 

																				

AND 
(@PersonId_ IS NULL OR U10.PERSONNR LIKE '%'+ @PersonId_ +'%') 
AND (@OrganizationId_ IS NULL OR U10.ORGID  = @OrganizationId_ )
AND (@OrganizationUnitId_ IS NULL OR U10.ORGENHNR = @OrganizationUnitId_ ) 
 
AND ((@DirectiveOrganizationInternalUnitId_ IS NULL AND @DirectiveCaseworker_ IS NULL AND @ExternalOrganizationId_ IS NULL AND @DirectiveOrganizationType_ IS NULL 
             AND @DirectiveClosureType_ IS NULL AND @DirectiveClosureReasonType_ IS NULL AND @DirectiveClosureProfession2012_ IS NULL) 
             OR (EXISTS (SELECT 'X' FROM LD0V0U11 U11 WHERE U11.INSKRIVNINGSNR = U10.INSKRIVNINGSNR AND 
			                        (@ShowDirectiveDetail_ = 0 OR
			                        (U11.ANVISORGTYP = U10.U11ANVISORGTYP  AND
                                    U11.ORGEXTERN = U10.U11ORGEXTERN AND U11.HANDLAGGARE = U10.U11HANDLAGGARE AND U11.ANVISORGINTERN = U10.U11ANVISORGINTERN AND
                                    U11.TYPAVSLUT = U10.U11TYPAVSLUT AND U11.YRKEAVSLUT_2012 = U10.U11YRKEAVSLUT_2012))
                                    AND (@DirectiveOrganizationType_ IS NULL OR U11.ANVISORGTYP = @DirectiveOrganizationType_)
                                    AND (@DirectiveOrganizationInternalUnitId_ IS NULL OR U11.ANVISORGINTERN = @DirectiveOrganizationInternalUnitId_  AND U11.ORGID = @DirectiveOrganizationInternalId_)
                                    AND (@DirectiveCaseworker_ IS NULL OR U11.HANDLAGGARE = @DirectiveCaseworker_)
                                    AND (@ExternalOrganizationId_ IS NULL OR U11.ORGEXTERN = @ExternalOrganizationId_)
                                    AND (@DirectiveClosureReasonType_ IS NULL OR U11.AVSLUTORS = @DirectiveClosureReasonType_)  --mapping to confirm
                                    AND (@DirectiveClosureType_ IS NULL OR U11.TYPAVSLUT = @DirectiveClosureType_) 
                                    AND (@DirectiveClosureProfession2012_ IS NULL OR U11.YRKEAVSLUT_2012 LIKE @DirectiveClosureProfession2012_+'%'))) 
                    )
AND (@ClosingReasonId_ IS NULL OR U10.TYPAVSLUT = @ClosingReasonId_)
AND (@EndReasonId_ IS NULL OR U10.[AVSLUTORS] = @EndReasonId_ )
                  
AND (@ProfessionalGroupAtEnd_ IS NULL OR U10.YRKEAVSLUT_2012 LIKE @ProfessionalGroupAtEnd_+'%')
AND (@NoEducationalBackground_ IS NULL OR U10.INGENUTBILDN = @NoEducationalBackground_ ) 
AND (@NoWorkExperience_ IS NULL OR U10.INGENARBLIVERF = @NoWorkExperience_ ) 
AND (@NoWorkLikeSkills_ IS NULL OR U10.INGENFARDIGHET = @NoWorkLikeSkills_ ) 
AND (@GenderVerification_ IS NULL OR U10.KON = (SELECT CAST(@GenderVerification_ AS DECIMAL(2,0))) ) 
AND ((@YearOfBirthFrom_ IS NULL AND @YearOfBirthTo_ IS NULL) OR (U10.FODDAT BETWEEN @YearOfBirthFrom_ AND @YearOfBirthTo_ ))

AND (@EnrollmentResponsibilityTypeId_ IS NULL OR U140.ANSVARIGFOR = @EnrollmentResponsibilityTypeId_ )

-- Ansvarig handläggare, explicit eller läst från sökgrupp      
-- U12.ANSVARIGNR IS NOT NULL is required because u140 history rows are only valid if there is a matching u12 row (responsible caseworking)
AND (@SkipCaseWorkerFilter = 1 OR (U140.HAND IN (SELECT HANDLAGGARE FROM @ResponsibleCaseworkers) AND U12.ANSVARIGNR IS NOT NULL))
AND (@ResponsibleToDate_ IS NULL OR (U140.FROMDAT <= @ResponsibleToDate_ AND U12.ANSVARIGNR IS NOT NULL) )
AND (@ResponsibleFromDate_ IS NULL OR (U140.TOMDAT >= @ResponsibleFromDate_ AND U12.ENDDATE >= @ResponsibleFromDate_ AND U12.ANSVARIGNR IS NOT NULL) )
AND (@EnrollmentResponsibilityOrganizationUnitId_ IS NULL OR (U140.ORGENHNR = @EnrollmentResponsibilityOrganizationUnitId_ AND U140.ORGID = @EnrollmentResponsibilityOrganizationId_ AND U12.ANSVARIGNR IS NOT NULL))

AND (@SearchGroupId_ IS NULL OR 
         (EXISTS (SELECT 'X' FROM LD0V0I42 I42 JOIN LD0V0U10 U10X ON U10X.ORGENHNR like I42.ORGENHNR + '%' AND U10X.ORGID = I42.ORGID AND U10X.INSKRIVNINGSNR = U10.INSKRIVNINGSNR AND 
                  I42.SOKGRUPPNR = @SearchGroupId_ ) OR NOT EXISTS (SELECT 'X' FROM LD0V0I42 I42B WHERE I42B.SOKGRUPPNR = @SearchGroupId_)))
	AND ((@AreaId_ IS NULL) 
		OR (@UnitID_ IS NULL AND EXISTS (SELECT * FROM LD0VAA0OMR OMR JOIN #TEMPRESULT ON #TEMPRESULT.OMRENHNR = OMR.OMRENHNR AND J21.OMRENHNR = @AreaId_ 
									AND #TEMPRESULT.PERSONNR LIKE '%'+ @PersonId_ +'%')
									AND @AreaId_ = OMR.OMRENHNR))								
AND (@UnitID_ IS NULL OR EXISTS (SELECT 'x' FROM LD0V0Q01 Q01 JOIN LD0V0A A ON Q01.KOD = U10.OMRADE AND Q01.KODTYP = 'OMRÅDE' 
JOIN #TEMPRESULT ON #TEMPRESULT.PERSONNR =  U10.PERSONNR AND Q01.KOD = Q14.KOD AND  Q01.KOD =  @UnitId_  AND #TEMPRESULT.PERSONNR  LIKE '%'+ @PersonId_ +'%'))

DROP TABLE #TEMPRESULT

END

GO
GRANT ALL ON LD0SPSearchEnrollment TO LD0ROANV
GO
