IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPStatisticsResourceAllocation')
	DROP PROCEDURE LD0SPStatisticsResourceAllocation
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE LD0SPStatisticsResourceAllocation (@searchCriteria_ xml)                   
AS                      
                      
                      
BEGIN                      
  IF(@searchCriteria_ is null)                       
 BEGIN                      
     raiserror('xml-string can not be null', 11, 1);                      
 END;                      
                      
 DECLARE @PersonId_ VARCHAR(12);                      
 DECLARE @FromDate_ VARCHAR(12);                      
 DECLARE @ToDate_ VARCHAR(12);                      
 DECLARE @Status_ VARCHAR(200);                      
 DECLARE @GenderVerification_  VARCHAR(20);                      
 DECLARE @AgeFrom_  VARCHAR(12);                      
 DECLARE @AgeTo_  VARCHAR(12);                      
 DECLARE @BirthFromDate_ VARCHAR(12);                      
 DECLARE @BirthToDate_ VARCHAR(12);                      
 DECLARE @ResourceTypeList_  VARCHAR(500);                      
 DECLARE @ResourceCategoryType_ INT;                      
 DECLARE @OrganizationUnitId_  VARCHAR(500);                      
 DECLARE @IsSearchOnSubLevels_  BIT;                      
 DECLARE @CaseworkerId_  VARCHAR(10);                      
 DECLARE @StatisticsGroup1_ INT;                      
 DECLARE @StatisticsGroup2_ INT;                      
 DECLARE @StatisticsGroup3_ INT;                      
 DECLARE @StatisticsGroup4_ INT;                      
 DECLARE @StatisticsGroup5_ INT;                      
 DECLARE @CostRevenueVerification_ VARCHAR(100);                      
 DECLARE @CostRevenueFromDate_ VARCHAR(12);                      
 DECLARE @CostRevenueToDate_ VARCHAR(12);                      
 DECLARE @MaxResult_ INT;                      
 DECLARE @MaxDate VARCHAR(12) = '99999999';                      
                      
 --Extract data from the XML structure                      
                      
 SELECT                      
          @PersonId_= ref.value('PersonId[1]/@Value', 'varchar(12)'),                      
          @FromDate_ = ref.value('FromDate[1]/@Value', 'varchar(10)'),                      
          @ToDate_ = ref.value('ToDate[1]/@Value', 'varchar(10)'),                      
          @Status_ = ref.value('Status[1]/@Value', 'varchar(200)'),                      
          @GenderVerification_ = ref.value('GenderVerification[1]/@Value', 'varchar(20)'),                      
          @AgeFrom_ = ref.value('AgeFrom[1]/@Value', 'varchar(12)'),                      
          @AgeTo_ = ref.value('AgeTo[1]/@Value', 'varchar(12)'),                      
          @BirthFromDate_ = ref.value('BirthDateFrom[1]/@Value', 'varchar(12)'),                      
          @BirthToDate_ = ref.value('BirthDateTo[1]/@Value', 'varchar(12)'),                      
          @ResourceTypeList_ = ref.value('ResourceType[1]/@Value', 'varchar(500)'),                      
          @ResourceCategoryType_ = ref.value('ResourceCategoryType[1]/@Value', 'INT'),                      
          @OrganizationUnitId_ = ref.value('OrganizationUnitId[1]/@Value', 'varchar(500)'),                      
    @IsSearchOnSubLevels_ = ref.value('IsSearchOnSubLevels[1]/@Value', 'BIT'),                      
    @CaseworkerId_ = ref.value('CaseworkerId[1]/@Value', 'varchar(10)'),                      
    @StatisticsGroup1_= ref.value('StatisticsGroup1[1]/@Value', 'INT'),                      
    @StatisticsGroup2_= ref.value('StatisticsGroup2[1]/@Value', 'INT'),                      
    @StatisticsGroup3_= ref.value('StatisticsGroup3[1]/@Value', 'INT'),                      
    @StatisticsGroup4_= ref.value('StatisticsGroup4[1]/@Value', 'INT'),                      
    @StatisticsGroup5_= ref.value('StatisticsGroup5[1]/@Value', 'INT'),                      
    @CostRevenueVerification_= ref.value('CostRevenueVerification[1]/@Value', 'varchar(100)'),                      
    @CostRevenueFromDate_ = ref.value('CostRevenueFromDate[1]/@Value', 'varchar(12)'),                      
          @CostRevenueToDate_ = ref.value('CostRevenueToDate[1]/@Value', 'varchar(12)'),                      
    @MaxResult_ = ref.value('MaxResult[1]/@Value', 'INT')                      
 FROM @searchCriteria_.nodes( '/StatisticsRASPCriteria' ) as node(ref);           
  --Get standard result without pricing                      
 IF OBJECT_ID('tempdb..#DefaultResult') IS NOT NULL DROP TABLE #DefaultResult                      
 SELECT * INTO #DefaultResult FROM (                      
  SELECT                       
   V0L.ANNANKOMU,           
   V0L.AVTAL,                      
   N70.FOMDAT,                      
   V0I.HAND AS CASEWORKERID,                      
   V0I.NAMN AS CASEWORKER,                   
   V0I.FOMDAT AS CASEWORKER_FOMDAT,                      
   V0I.TOMDAT AS CASEWORKER_TOMDAT,                      
   V0L.HUVUDMAN,                      
   Q01_INSATS.KLARTEXT AS INSATS,                      
   C20.INSNR,                      
   Q01_KOMU.KLARTEXT AS KOMMUN,                      
   V0A.KON,                      
   Q01_LAGRUMTYP.KLARTEXT AS LAGRUM,                      
   Q01_LAN.KLARTEXT AS LAN,                      
   CAST(LEFT(Q01_KOMU.EXTKOD, 2) AS int) AS LANSKOD,
                      
   ISNULL(N93.AMOUNT,0) AS Vardfakturabelopp                    
  ,0 AS IntEgenavgBel                      
  ,0 AS LoneBel                      
  ,0 AS PrelIntEgenavgBel                      
  ,0 AS PreLoneBel                      
  ,0 AS PrelKostnadBel                      
  ,0 AS OvrigtForloradArbfortjanst                      
  ,0 AS OvrigtMilersattning                      
  ,N93.FROMDATE AS LonIntEgFaktKostnFrom                      
  ,N93.TODATE AS LonIntEgFaktKostnTom                      
  ,N93.PAYMENTDATE AS LonIntEgFaktDat        
  ,N30.FOMDAT AS FakturaFran        
  ,N30.TOMDAT AS FakturaTill        
  ,N30.VARDDAGAR AS FakturaVarddagar        
  ,ISNULL(N30.BELOPP,0) AS FakturaBelopp        
  ,N30.BETDAG AS FakturaBetaldatum,        
                       
   N70.PRELTOMDAT AS Preltill, --confirm mapping                      
   V0A.NAMN,                      
   OMR.NAMN AS OMRADE,                      
   C20.ORGID,                      
   C20.ORGENHNR,                      
   V0A.PNR,                      
   Q01_PLFORM.KLARTEXT AS Placeringsform,                      
   N70.PROCENT,                      
   V0L.RESURSBEN,                      
   --L01.RESURSTYP,                      
   Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,                      
   Q01_RESURSKAT.AKTIV AS RESURSKAT_STATUS,                      
   Q01_RESURSKAT.KOD AS RESURSKAT,                      
   V0L.PNR AS RESURSPNR,                      
   N70.RESTILLDNR,                      
   Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,                      
   Q01_RESURSTYP.AKTIV AS RESURSTYP_STATUS,                      
   Q01_RESURSTYP.KOD AS RESURSTYP,                      
   Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,                      
   Q01_StatistikGroup1.AKTIV AS StatistikGroup1_Status,                      
      Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,                      
   Q01_StatistikGroup2.AKTIV AS StatistikGroup2_Status,                      
   Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,                      
   Q01_StatistikGroup3.AKTIV AS StatistikGroup3_Status,                      
      Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,                      
      Q01_StatistikGroup4.AKTIV AS StatistikGroup4_Status,                      
   Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,                      
   Q01_StatistikGroup5.AKTIV AS StatistikGroup5_Status,                      
   Q01_StatistikGroup1.KOD AS StatistikGroup1,                      
   Q01_StatistikGroup2.KOD AS StatistikGroup2,                      
   Q01_StatistikGroup3.KOD AS StatistikGroup3,                      
   Q01_StatistikGroup4.KOD AS StatistikGroup4,                      
   Q01_StatistikGroup5.KOD AS StatistikGroup5,                      
   Q01_RESURSSTAT.KLARTEXT AS STATUS,                      
   N70.TOMDAT,                      
   Q01_HEMTYP.KLARTEXT AS HEMTYP,                      
   J01.NAMN AS ORGNAMN,                      
   J01.INAKTIV AS ORG_STATUS,                      
   V0A.FODDAT,                      
   V0A.KUNDNUMMER,                    
   V0L.COMPANY,
   V0A.ADRSKYDD,
   V0A.SKYDDADFOLKBOKF
   FROM LD0V1N70 N70                      
 left join LD0VYC20  C20 on N70.INSNR=C20.INSNR                       
 left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR                      
 left join LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND  V0A.NYCKKOD=NKO.NYCKKOD                      
 left join LD0VAA0OMR OMR on  NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR                      
 left join LD0V0I  V0I on N70.HAND = V0I.HAND                
 left join LD0V0L01  L01 on N70.RESURSSOMNR=L01.RESURSSOMNR                       
 left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR                      
 left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR                       
 left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP                      
 left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP                      
 left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'RESURSTYP' AND  Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1                      
 left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'RESURSTYP' AND  Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2                      
 left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'RESURSTYP' AND  Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3                      
 left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'RESURSTYP' AND  Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4                      
 left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'RESURSTYP' AND  Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5                      
 left join LD0V0Q01 Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT                      
 left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP                      
 left join LD0V0Q01 Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN                      
 left join LD0V0Q01 Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP                      
 left join LD0V0Q01 Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN                      
 left join LD0V0Q01 Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT                      
 left join LD0V0Q01 Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT                      
 left join LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR                      
 left join LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL                      
 left join LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP                      
 left join LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5                        
 left join LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD = Q01_LAGRUM.XNUM1                      
 left join LD0P0N93 N93 on N70.RESTILLDNR = N93.RESOURCEALLOCATIONID               
 left join LD0V0N30 N30 on N70.RESTILLDNR = N30.RESTILLDNR        
 WHERE                      
 (@PersonId_ IS NULL OR @PersonId_ = V0A.PNR)                      
 AND 1 = CASE @Status_ WHEN 'OngoingInPeriod' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT >= @FromDate_ THEN 1 ELSE 0 END)                        
                 WHEN 'OngoingInPeriodAndStillOngoingToday' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT = @MaxDate THEN 1 ELSE 0 END)                      
        WHEN 'InitiatedDuringThePeriod' THEN (CASE WHEN N70.FOMDAT >= @FromDate_ AND N70.FOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                      
        WHEN 'EndedDuringThePeriod' THEN (CASE WHEN N70.TOMDAT >= @FromDate_ AND N70.TOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                       
        ELSE 0 END                      
 AND 1 = CASE @GenderVerification_ WHEN 'Females' THEN (CASE WHEN V0A.KON = 0 THEN 1 END)                      
           WHEN 'Males' THEN (CASE WHEN V0A.KON = 1 THEN 1 END)                      
           ELSE 1 END                      
 AND ((@AgeFrom_ IS NULL AND @AgeTo_ IS NULL) OR (V0A.FODDAT >= @AgeFrom_ AND V0A.FODDAT <= @AgeTo_))                      
 AND ((@BirthFromDate_ IS NULL) OR (V0A.FODDAT >= @BirthFromDate_))                      
 AND ((@BirthToDate_ IS NULL) OR (V0A.FODDAT <= @BirthToDate_))                      
 AND ((@ResourceTypeList_ IS NULL) OR (1=CASE WHEN @ResourceTypeList_ IS NOT NULL AND Q01_RESURSTYP.KOD IN (select value from STRING_SPLIT(@ResourceTypeList_,','))                   
THEN 1 ELSE 0 END) )                      
 AND ((@ResourceCategoryType_ IS NULL) OR (Q01_RESURSKAT.KOD = @ResourceCategoryType_))                      
 AND ((@OrganizationUnitId_ IS NULL) OR (1 = CASE @IsSearchOnSubLevels_ WHEN 0 THEN (CASE WHEN @OrganizationUnitId_ IS NOT NULL AND C20.ORGENHNR IN (select value from STRING_SPLIT(@OrganizationUnitId_,',')) THEN 1 ELSE 0 END)                      
                     WHEN 1 THEN (CASE WHEN EXISTS (SELECT 1 FROM STRING_SPLIT(@OrganizationUnitId_, ',') s WHERE C20.ORGENHNR LIKE s.value + '%') THEN 1 ELSE 0 END)                      
                     ELSE 0 END))                      
 AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= V0I.HAND)                      
 AND (@StatisticsGroup1_  = 0 OR @StatisticsGroup1_= Q01_StatistikGroup1.KOD)                      
 AND (@StatisticsGroup2_  = 0 OR @StatisticsGroup2_= Q01_StatistikGroup2.KOD)                      
 AND (@StatisticsGroup3_  = 0 OR @StatisticsGroup3_= Q01_StatistikGroup3.KOD)                      
 AND (@StatisticsGroup4_  = 0 OR @StatisticsGroup4_= Q01_StatistikGroup4.KOD)                      
 AND (@StatisticsGroup5_  = 0 OR @StatisticsGroup5_= Q01_StatistikGroup5.KOD)                      
 )result                      
                       
 --Get result with pricing                      
 IF OBJECT_ID('tempdb..#ResultWithPrice') IS NOT NULL DROP TABLE #ResultWithPrice                      
 IF(@CostRevenueVerification_ IS NOT NULL AND @CostRevenueFromDate_ != '00010101')                      
  BEGIN                      
  SELECT * INTO #ResultWithPrice FROM (                      
  SELECT                        
  V0L.ANNANKOMU,                      
  V0L.AVTAL,                      
  N70.FOMDAT,                      
  V0I.HAND AS CASEWORKERID,                      
  V0I.NAMN AS CASEWORKER,                      
  V0I.FOMDAT AS CASEWORKER_FOMDAT,                      
     V0I.TOMDAT AS CASEWORKER_TOMDAT,                      
  V0L.HUVUDMAN,                      
  Q01_INSATS.KLARTEXT AS INSATS,                      
  C20.INSNR,                      
Q01_KOMU.KLARTEXT AS KOMMUN,                      
  V0A.KON,                      
  Q01_LAGRUMTYP.KLARTEXT AS LAGRUM,                      
  Q01_LAN.KLARTEXT AS LAN,                      
    CAST(LEFT(Q01_KOMU.EXTKOD, 2) AS int) AS LANSKOD,                         
                      
   ISNULL(N93.AMOUNT,0) AS Vardfakturabelopp                      
  ,0 AS IntEgenavgBel                      
  ,0 AS LoneBel                      
  ,0 AS PrelIntEgenavgBel                      
  ,0 AS PreLoneBel                      
  ,0 AS PrelKostnadBel                      
  ,0 AS OvrigtForloradArbfortjanst                      
  ,0 AS OvrigtMilersattning                      
  ,N93.FROMDATE AS LonIntEgFaktKostnFrom                      
  ,N93.TODATE AS LonIntEgFaktKostnTom                      
  ,N93.PAYMENTDATE AS LonIntEgFaktDat         
  ,N30.FOMDAT AS FakturaFran        
  ,N30.TOMDAT AS FakturaTill        
  ,N30.VARDDAGAR AS FakturaVarddagar        
  ,ISNULL(N30.BELOPP,0) AS FakturaBelopp        
  ,N30.BETDAG AS FakturaBetaldatum,        
                      
  N70.PRELTOMDAT AS Preltill, --confirm mapping                      
  V0A.NAMN,                      
  OMR.NAMN AS OMRADE,                      
  C20.ORGID,                      
  C20.ORGENHNR,                      
  V0A.PNR,                      
  Q01_PLFORM.KLARTEXT AS Placeringsform,                      
  N70.PROCENT,                      
  V0L.RESURSBEN,                
  Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,                      
  Q01_RESURSKAT.AKTIV AS RESURSKAT_STATUS,                      
  Q01_RESURSKAT.KOD AS RESURSKAT,                      
  V0L.PNR AS RESURSPNR,                      
  N70.RESTILLDNR,                      
  Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,                      
  Q01_RESURSTYP.AKTIV AS RESURSTYP_STATUS,                      
  Q01_RESURSTYP.KOD AS RESURSTYP,                      
  Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,                      
  Q01_StatistikGroup1.AKTIV AS StatistikGroup1_Status,                      
  Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,                      
  Q01_StatistikGroup2.AKTIV AS StatistikGroup2_Status,                      
  Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,                      
  Q01_StatistikGroup3.AKTIV AS StatistikGroup3_Status,                      
  Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,                      
  Q01_StatistikGroup4.AKTIV AS StatistikGroup4_Status,                      
  Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,                      
  Q01_StatistikGroup5.AKTIV AS StatistikGroup5_Status,                      
  Q01_StatistikGroup1.KOD AS StatistikGroup1,                      
  Q01_StatistikGroup2.KOD AS StatistikGroup2,                      
  Q01_StatistikGroup3.KOD AS StatistikGroup3,                      
  Q01_StatistikGroup4.KOD AS StatistikGroup4,                      
  Q01_StatistikGroup5.KOD AS StatistikGroup5,                      
  Q01_RESURSSTAT.KLARTEXT AS STATUS,                      
  N70.TOMDAT,                      
  Q01_HEMTYP.KLARTEXT AS HEMTYP,                      
  J01.NAMN AS ORGNAMN,                      
  J01.INAKTIV AS ORG_STATUS,                      
  V0A.FODDAT,                      
  V0A.KUNDNUMMER,                    
  V0L.COMPANY,
  V0A.ADRSKYDD,
  V0A.SKYDDADFOLKBOKF
  FROM LD0V1N70 N70                      
  JOIN LD0V0N30 N30 ON N30.RESTILLDNR = N70.RESTILLDNR AND N30.BETALNR <> 0 AND N30.TOMDAT >= @CostRevenueFromDate_ AND N30.FOMDAT <= @CostRevenueToDate_  --FakturaBel                      
  JOIN LD0V0F FN30 ON  FN30.BETALNR = N30.BETALNR--FakturaBel                      
                      
  left join LD0VYC20  C20 on N70.INSNR=C20.INSNR                       
  left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR                      
  left join LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND V0A.NYCKKOD=NKO.NYCKKOD                      
  left join LD0VAA0OMR OMR on  NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR                      
  left join LD0V0I  V0I on N70.HAND = V0I.HAND                      
  left join LD0V0L01  L01 on N70.RESURSSOMNR=L01.RESURSSOMNR                       
  left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR                      
  left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR                       
  left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP                      
  left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1                      
  left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2                   
  left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3                      
  left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4                      
  left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5                      
  left join LD0V0Q01 Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT                      
  left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN                      
  left join LD0V0Q01 Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP                      
  left join LD0V0Q01 Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN                      
  left join LD0V0Q01 Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT                      
  left join LD0V0Q01 Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT                      
  left join LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR                      
  left join LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL                      
  left join LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP                      
  left join LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5                        
  left join LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD = Q01_LAGRUM.XNUM1                      
  left join LD0P0N93 N93 on N70.RESTILLDNR = N93.RESOURCEALLOCATIONID           
                      
 WHERE                      
   (@PersonId_ IS NULL OR @PersonId_ = V0A.PNR)                      
  AND 1 = CASE @Status_ WHEN 'OngoingInPeriod' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT >= @FromDate_ THEN 1 ELSE 0 END)                        
                 WHEN 'OngoingInPeriodAndStillOngoingToday' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT = @MaxDate THEN 1 ELSE 0 END)                      
        WHEN 'InitiatedDuringThePeriod' THEN (CASE WHEN N70.FOMDAT >= @FromDate_ AND N70.FOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                      
        WHEN 'EndedDuringThePeriod' THEN (CASE WHEN N70.TOMDAT >= @FromDate_ AND N70.TOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                       
        ELSE 0 END                      
  AND 1 = CASE @GenderVerification_ WHEN 'Females' THEN (CASE WHEN V0A.KON = 0 THEN 1 ELSE 0 END)                      
            WHEN 'Males' THEN (CASE WHEN V0A.KON = 1 THEN 1 ELSE 0 END)                      
            ELSE 1 END                      
  AND ((@AgeFrom_ IS NULL AND @AgeTo_ IS NULL) OR (V0A.FODDAT >= @AgeFrom_ AND V0A.FODDAT <= @AgeTo_))                      
  AND ((@BirthFromDate_ IS NULL) OR (V0A.FODDAT >= @BirthFromDate_))              
  AND ((@BirthToDate_ IS NULL) OR (V0A.FODDAT <= @BirthToDate_))                      
  AND ((@ResourceTypeList_ IS NULL) OR (1 = CASE WHEN @ResourceTypeList_ IS NOT NULL AND Q01_RESURSTYP.KOD IN (select value from STRING_SPLIT(@ResourceTypeList_,','))                   
THEN 1 ELSE 0 END))                 
  AND ((@ResourceCategoryType_ IS NULL) OR (Q01_RESURSKAT.KOD = @ResourceCategoryType_))                      
  AND ((@OrganizationUnitId_ IS NULL) OR (1 = CASE @IsSearchOnSubLevels_ WHEN 0 THEN (CASE WHEN C20.ORGENHNR IN(select value from STRING_SPLIT(@OrganizationUnitId_,',')) THEN 1 ELSE 0 END)                      
            WHEN 1 THEN (CASE WHEN EXISTS (SELECT 1 FROM STRING_SPLIT(@OrganizationUnitId_, ',') s WHERE C20.ORGENHNR LIKE s.value + '%') THEN 1 ELSE 0 END)                      
                      ELSE 0 END))                      
  AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= V0I.HAND)                      
  AND (@StatisticsGroup1_  = 0 OR @StatisticsGroup1_= Q01_StatistikGroup1.KOD)                      
  AND (@StatisticsGroup2_  = 0 OR @StatisticsGroup2_= Q01_StatistikGroup2.KOD)                      
  AND (@StatisticsGroup3_  = 0 OR @StatisticsGroup3_= Q01_StatistikGroup3.KOD)                      
  AND (@StatisticsGroup4_  = 0 OR @StatisticsGroup4_= Q01_StatistikGroup4.KOD)                      
  AND (@StatisticsGroup5_  = 0 OR @StatisticsGroup5_= Q01_StatistikGroup5.KOD)                      
                      
  UNION ALL                      
                      
  --IntEgenavgBel                      
   SELECT                        
    V0L.ANNANKOMU,                      
    V0L.AVTAL,                      
    N70.FOMDAT,                      
    V0I.HAND AS CASEWORKERID,                      
    V0I.NAMN AS CASEWORKER,                      
    V0I.FOMDAT AS CASEWORKER_FOMDAT,                      
    V0I.TOMDAT AS CASEWORKER_TOMDAT,                      
    V0L.HUVUDMAN,                      
    Q01_INSATS.KLARTEXT AS INSATS,                      
    C20.INSNR,                      
    Q01_KOMU.KLARTEXT AS KOMMUN,                      
    V0A.KON,                      
    Q01_LAGRUMTYP.KLARTEXT AS LAGRUM,                      
    Q01_LAN.KLARTEXT AS LAN,                      
    CAST(LEFT(Q01_KOMU.EXTKOD, 2) AS int) AS LANSKOD,                       
                      
     ISNULL(N93.AMOUNT,0) AS Vardfakturabelopp                      
    ,ISNULL(FN40.Belopp,0) AS IntEgenavgBel                      
    ,0 AS LoneBel                      
    ,0 AS PrelIntEgenavgBel                      
    ,0 AS PreLoneBel                      
    ,0 AS PrelKostnadBel                      
    ,0 AS OvrigtForloradArbfortjanst                      
    ,0 AS OvrigtMilersattning                      
    ,N93.FROMDATE AS LonIntEgFaktKostnFrom                      
       ,N93.TODATE AS LonIntEgFaktKostnTom                      
       ,N93.PAYMENTDATE AS LonIntEgFaktDat         
    ,N30.FOMDAT AS FakturaFran        
    ,N30.TOMDAT AS FakturaTill        
    ,N30.VARDDAGAR AS FakturaVarddagar        
    ,ISNULL(N30.BELOPP,0) AS FakturaBelopp        
    ,N30.BETDAG AS FakturaBetaldatum,        
                      
    N70.PRELTOMDAT AS Preltill, --confirm mapping                      
    V0A.NAMN,                      
    OMR.NAMN AS OMRADE,                      
    C20.ORGID,                      
    C20.ORGENHNR,                      
    V0A.PNR,                      
    Q01_PLFORM.KLARTEXT AS Placeringsform,                      
    N70.PROCENT,                      
    V0L.RESURSBEN,                      
    Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,                      
    Q01_RESURSKAT.AKTIV AS RESURSKAT_STATUS,                      
    Q01_RESURSKAT.KOD AS RESURSKAT,                      
    V0L.PNR AS RESURSPNR,                      
    N70.RESTILLDNR,                      
    Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,                      
    Q01_RESURSTYP.AKTIV AS RESURSTYP_STATUS,                      
    Q01_RESURSTYP.KOD AS RESURSTYP,                      
    Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,                      
    Q01_StatistikGroup1.AKTIV AS StatistikGroup1_Status,                      
    Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,                      
    Q01_StatistikGroup2.AKTIV AS StatistikGroup2_Status,                      
    Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,                      
    Q01_StatistikGroup3.AKTIV AS StatistikGroup3_Status,                      
    Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,                      
    Q01_StatistikGroup4.AKTIV AS StatistikGroup4_Status,                      
    Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,                      
    Q01_StatistikGroup5.AKTIV AS StatistikGroup5_Status,                      
    Q01_StatistikGroup1.KOD AS StatistikGroup1,                      
    Q01_StatistikGroup2.KOD AS StatistikGroup2,                      
    Q01_StatistikGroup3.KOD AS StatistikGroup3,                      
    Q01_StatistikGroup4.KOD AS StatistikGroup4,                      
    Q01_StatistikGroup5.KOD AS StatistikGroup5,                      
    Q01_RESURSSTAT.KLARTEXT AS STATUS,                      
    N70.TOMDAT,                      
 Q01_HEMTYP.KLARTEXT AS HEMTYP,                      
    J01.NAMN AS ORGNAMN,                      
    J01.INAKTIV AS ORG_STATUS,                      
    V0A.FODDAT,                      
    V0A.KUNDNUMMER  ,                    
    V0L.COMPANY,
    V0A.ADRSKYDD,
    V0A.SKYDDADFOLKBOKF
    FROM LD0V1N70 N70                      
    JOIN LD0V0N40 N40 ON N40.RESTILLDNR = N70.RESTILLDNR  --IntEgenavgBel                      
    JOIN LD0V0F FN40 ON FN40.BETALNR = N40.BETALNR AND FN40.BETDAT >= @CostRevenueFromDate_ AND FN40.BETDAT <= @CostRevenueToDate_ --AND FN40.BOKDAT >= @CostRevenueFromDate_ AND FN40.BETDAT <= @CostRevenueToDate_--IntEgenavgBel                      
  left join LD0VYC20  C20 on N70.INSNR=C20.INSNR                       
  left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR                      
  left join LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND V0A.NYCKKOD=NKO.NYCKKOD                      
 left join LD0VAA0OMR OMR on  NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR                      
  left join LD0V0I  V0I on N70.HAND = V0I.HAND                      
  left join LD0V0L01  L01 on N70.RESURSSOMNR=L01.RESURSSOMNR                       
  left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR                      
  left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR                       
  left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP                      
  left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1                      
  left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2                      
  left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3                      
  left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4                      
  left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5                      
  left join LD0V0Q01 Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT                      
  left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN                      
  left join LD0V0Q01 Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP                      
  left join LD0V0Q01 Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN                      
  left join LD0V0Q01 Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT                      
  left join LD0V0Q01 Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT                      
  left join LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR                      
  left join LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL                      
  left join LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP                      
  left join LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5                        
  left join LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD = Q01_LAGRUM.XNUM1                      
  left join LD0P0N93 N93 on N70.RESTILLDNR = N93.RESOURCEALLOCATIONID            
  left join LD0V0N30 N30 on N70.RESTILLDNR = N30.RESTILLDNR        
  WHERE                      
  (@PersonId_ IS NULL OR @PersonId_ = V0A.PNR)                      
  AND 1 = CASE @Status_ WHEN 'OngoingInPeriod' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT >= @FromDate_ THEN 1 ELSE 0 END)                     
                 WHEN 'OngoingInPeriodAndStillOngoingToday' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT = @MaxDate THEN 1 ELSE 0 END)                      
        WHEN 'InitiatedDuringThePeriod' THEN (CASE WHEN N70.FOMDAT >= @FromDate_ AND N70.FOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                      
        WHEN 'EndedDuringThePeriod' THEN (CASE WHEN N70.TOMDAT >= @FromDate_ AND N70.TOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                       
        ELSE 0 END                      
  AND 1 = CASE @GenderVerification_ WHEN 'Females' THEN (CASE WHEN V0A.KON = 0 THEN 1 ELSE 0 END)                      
            WHEN 'Males' THEN (CASE WHEN V0A.KON = 1 THEN 1 ELSE 0 END)                      
            ELSE 1 END                      
  AND ((@AgeFrom_ IS NULL AND @AgeTo_ IS NULL) OR (V0A.FODDAT >= @AgeFrom_ AND V0A.FODDAT <= @AgeTo_))                      
  AND ((@BirthFromDate_ IS NULL) OR (V0A.FODDAT >= @BirthFromDate_))                      
  AND ((@BirthToDate_ IS NULL) OR (V0A.FODDAT <= @BirthToDate_))                      
  AND ((@ResourceTypeList_ IS NULL) OR (1 = CASE WHEN @ResourceTypeList_ IS NOT NULL AND Q01_RESURSTYP.KOD IN (select value from STRING_SPLIT(@ResourceTypeList_,','))                   
THEN 1 ELSE 0 END) )                 
  AND ((@ResourceCategoryType_ IS NULL) OR (Q01_RESURSKAT.KOD = @ResourceCategoryType_))                      
  AND ((@OrganizationUnitId_ IS NULL) OR (1 = CASE @IsSearchOnSubLevels_ WHEN 0 THEN (CASE WHEN C20.ORGENHNR IN(select value from STRING_SPLIT(@OrganizationUnitId_,',')) THEN 1 ELSE 0 END)                      
                      WHEN 1 THEN (CASE WHEN EXISTS (SELECT 1 FROM STRING_SPLIT(@OrganizationUnitId_, ',') s WHERE C20.ORGENHNR LIKE s.value + '%') THEN 1 ELSE 0 END)                      
                      ELSE 0 END))                      
  AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= V0I.HAND)                      
  AND (@StatisticsGroup1_  = 0 OR @StatisticsGroup1_= Q01_StatistikGroup1.KOD)                      
  AND (@StatisticsGroup2_  = 0 OR @StatisticsGroup2_= Q01_StatistikGroup2.KOD)                      
  AND (@StatisticsGroup3_  = 0 OR @StatisticsGroup3_= Q01_StatistikGroup3.KOD)                      
  AND (@StatisticsGroup4_  = 0 OR @StatisticsGroup4_= Q01_StatistikGroup4.KOD)                      
  AND (@StatisticsGroup5_  = 0 OR @StatisticsGroup5_= Q01_StatistikGroup5.KOD)                      
                      
  UNION ALL                      
            
  --LoneBel                      
   SELECT                        
    V0L.ANNANKOMU,                      
    V0L.AVTAL,                      
    N70.FOMDAT,                      
    V0I.HAND AS CASEWORKERID,                      
    V0I.NAMN AS CASEWORKER,                      
    V0I.FOMDAT AS CASEWORKER_FOMDAT,                      
    V0I.TOMDAT AS CASEWORKER_TOMDAT,                      
    V0L.HUVUDMAN,                      
    Q01_INSATS.KLARTEXT AS INSATS,                      
    C20.INSNR,                      
    Q01_KOMU.KLARTEXT AS KOMMUN,                      
    V0A.KON,                      
    Q01_LAGRUMTYP.KLARTEXT AS LAGRUM,     
    Q01_LAN.KLARTEXT AS LAN,                      
    CAST(LEFT(Q01_KOMU.EXTKOD, 2) AS int) AS LANSKOD,                         
                      
    ISNULL(N93.AMOUNT,0) AS Vardfakturabelopp                      
   ,0 AS IntEgenavgBel                      
   ,ISNULL(L50.LONBEL,0) AS LoneBel                      
   ,0 AS PrelIntEgenavgBel                      
   ,0 AS PreLoneBel                      
   ,0 AS PrelKostnadBel                      
   ,0 AS OvrigtForloradArbfortjanst                  
   ,0 AS OvrigtMilersattning                      
   ,N93.FROMDATE AS LonIntEgFaktKostnFrom                      
      ,N93.TODATE AS LonIntEgFaktKostnTom                      
      ,N93.PAYMENTDATE AS LonIntEgFaktDat        
           
 ,N30.FOMDAT AS FakturaFran        
    ,N30.TOMDAT AS FakturaTill        
 ,N30.VARDDAGAR AS FakturaVarddagar        
 ,ISNULL(N30.BELOPP,0) AS FakturaBelopp        
 ,N30.BETDAG AS FakturaBetaldatum,        
                      
    N70.PRELTOMDAT AS Preltill, --confirm mapping                      
    V0A.NAMN,           
    OMR.NAMN AS OMRADE,                      
    C20.ORGID,                      
    C20.ORGENHNR,                      
    V0A.PNR,                      
    Q01_PLFORM.KLARTEXT AS Placeringsform,                      
    N70.PROCENT,                      
    V0L.RESURSBEN,                      
    Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,                      
    Q01_RESURSKAT.AKTIV AS RESURSKAT_STATUS,                      
    Q01_RESURSKAT.KOD AS RESURSKAT,                      
    V0L.PNR AS RESURSPNR,                      
    N70.RESTILLDNR,                      
    Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,                      
    Q01_RESURSTYP.AKTIV AS RESURSTYP_STATUS,                      
    Q01_RESURSTYP.KOD AS RESURSTYP,                      
    Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,                      
    Q01_StatistikGroup1.AKTIV AS StatistikGroup1_Status,                      
    Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,                      
    Q01_StatistikGroup2.AKTIV AS StatistikGroup2_Status,                      
    Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,                      
    Q01_StatistikGroup3.AKTIV AS StatistikGroup3_Status,                      
    Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,                      
    Q01_StatistikGroup4.AKTIV AS StatistikGroup4_Status,                      
    Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,                      
    Q01_StatistikGroup5.AKTIV AS StatistikGroup5_Status,                      
    Q01_StatistikGroup1.KOD AS StatistikGroup1,                      
    Q01_StatistikGroup2.KOD AS StatistikGroup2,                      
    Q01_StatistikGroup3.KOD AS StatistikGroup3,                      
    Q01_StatistikGroup4.KOD AS StatistikGroup4,                      
    Q01_StatistikGroup5.KOD AS StatistikGroup5,                      
    Q01_RESURSSTAT.KLARTEXT AS STATUS,                      
    N70.TOMDAT,                      
    Q01_HEMTYP.KLARTEXT AS HEMTYP,                      
    J01.NAMN AS ORGNAMN,                      
    J01.INAKTIV AS ORG_STATUS,                      
    V0A.FODDAT,                      
    V0A.KUNDNUMMER ,                    
    V0L.COMPANY,
    V0A.ADRSKYDD,
    V0A.SKYDDADFOLKBOKF
    FROM LD0V1N70 N70                      
    JOIN LD0V0L50 L50 ON L50.RESTILLDNR = N70.RESTILLDNR AND  L50.GTOM >= @CostRevenueFromDate_ AND L50.GFROM <= @CostRevenueToDate_ --AND L50.RESURSSOMNR = N70.RESURSSOMNR--Salary sum, date not present --LoneBel                      
  left join LD0VYC20  C20 on N70.INSNR=C20.INSNR                       
  left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR                      
  left join LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND V0A.NYCKKOD=NKO.NYCKKOD                      
  left join LD0VAA0OMR OMR on  NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR                      
  left join LD0V0I  V0I on N70.HAND = V0I.HAND                      
  left join LD0V0L01  L01 on N70.RESURSSOMNR=L01.RESURSSOMNR                    
  left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR                      
  left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR                       
  left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP                      
  left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1                      
  left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2                      
  left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3                      
  left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4                      
  left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5                      
  left join LD0V0Q01 Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT                      
  left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN                      
  left join LD0V0Q01 Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP                      
  left join LD0V0Q01 Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN                      
  left join LD0V0Q01 Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT                      
  left join LD0V0Q01 Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT                      
  left join LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR                      
  left join LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL                      
  left join LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP                      
  left join LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5                        
  left join LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD = Q01_LAGRUM.XNUM1                 
  left join LD0P0N93 N93 on N70.RESTILLDNR = N93.RESOURCEALLOCATIONID        
  left join LD0V0N30 N30 on N70.RESTILLDNR = N30.RESTILLDNR        
  WHERE                      
  (@PersonId_ IS NULL OR @PersonId_ = V0A.PNR)                      
  AND 1 = CASE @Status_ WHEN 'OngoingInPeriod' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT >= @FromDate_ THEN 1 ELSE 0 END)                        
                 WHEN 'OngoingInPeriodAndStillOngoingToday' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT = @MaxDate THEN 1 ELSE 0 END)                      
        WHEN 'InitiatedDuringThePeriod' THEN (CASE WHEN N70.FOMDAT >= @FromDate_ AND N70.FOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                      
        WHEN 'EndedDuringThePeriod' THEN (CASE WHEN N70.TOMDAT >= @FromDate_ AND N70.TOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                       
        ELSE 0 END                      
  AND 1 = CASE @GenderVerification_ WHEN 'Females' THEN (CASE WHEN V0A.KON = 0 THEN 1 ELSE 0 END)                      
            WHEN 'Males' THEN (CASE WHEN V0A.KON = 1 THEN 1 ELSE 0 END)                      
            ELSE 1 END           
  AND ((@AgeFrom_ IS NULL AND @AgeTo_ IS NULL) OR (V0A.FODDAT >= @AgeFrom_ AND V0A.FODDAT <= @AgeTo_))                      
  AND ((@BirthFromDate_ IS NULL) OR (V0A.FODDAT >= @BirthFromDate_))                      
  AND ((@BirthToDate_ IS NULL) OR (V0A.FODDAT <= @BirthToDate_))                      
  AND ((@ResourceTypeList_ IS NULL) OR (1 = CASE WHEN @ResourceTypeList_ IS NOT NULL AND Q01_RESURSTYP.KOD IN (select value from STRING_SPLIT(@ResourceTypeList_,','))                   
THEN 1 ELSE 0 END) )                 
  AND ((@ResourceCategoryType_ IS NULL) OR (Q01_RESURSKAT.KOD = @ResourceCategoryType_))                      
  AND ((@OrganizationUnitId_ IS NULL) OR (1 = CASE @IsSearchOnSubLevels_ WHEN 0 THEN (CASE WHEN C20.ORGENHNR IN(select value from STRING_SPLIT(@OrganizationUnitId_,',')) THEN 1 ELSE 0 END)                      
                      WHEN 1 THEN (CASE WHEN EXISTS (SELECT 1 FROM STRING_SPLIT(@OrganizationUnitId_, ',') s WHERE C20.ORGENHNR LIKE s.value + '%') THEN 1 ELSE 0 END)                      
                      ELSE 0 END))                      
  AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= V0I.HAND)                      
  AND (@StatisticsGroup1_  = 0 OR @StatisticsGroup1_= Q01_StatistikGroup1.KOD)                      
  AND (@StatisticsGroup2_  = 0 OR @StatisticsGroup2_= Q01_StatistikGroup2.KOD)                      
  AND (@StatisticsGroup3_  = 0 OR @StatisticsGroup3_= Q01_StatistikGroup3.KOD)                      
  AND (@StatisticsGroup4_  = 0 OR @StatisticsGroup4_= Q01_StatistikGroup4.KOD)                      
  AND (@StatisticsGroup5_  = 0 OR @StatisticsGroup5_= Q01_StatistikGroup5.KOD)                      
                      
  UNION ALL                      
  --PrelIntEgenavgBel                      
   SELECT                        
    V0L.ANNANKOMU,                      
    V0L.AVTAL,                      
    N70.FOMDAT,                      
    V0I.HAND AS CASEWORKERID,                      
    V0I.NAMN AS CASEWORKER,                      
    V0I.FOMDAT AS CASEWORKER_FOMDAT,                      
    V0I.TOMDAT AS CASEWORKER_TOMDAT,                      
    V0L.HUVUDMAN,                      
    Q01_INSATS.KLARTEXT AS INSATS,                      
    C20.INSNR,                      
    Q01_KOMU.KLARTEXT AS KOMMUN,                      
    V0A.KON,                      
    Q01_LAGRUMTYP.KLARTEXT AS LAGRUM,                      
    Q01_LAN.KLARTEXT AS LAN,                      
       CAST(LEFT(Q01_KOMU.EXTKOD, 2) AS int) AS LANSKOD,                        
                      
    ISNULL(N93.AMOUNT,0) AS Vardfakturabelopp                      
   ,0 AS IntEgenavgBel                      
   ,0 AS LoneBel                      
   ,ISNULL(N20.SUMMA,0) AS PrelIntEgenavgBel                      
   ,0 AS PreLoneBel                      
   ,0 AS PrelKostnadBel                      
   ,0 AS OvrigtForloradArbfortjanst                      
   ,0 AS OvrigtMilersattning                      
   ,N93.FROMDATE AS LonIntEgFaktKostnFrom                      
   ,N93.TODATE AS LonIntEgFaktKostnTom                      
   ,N93.PAYMENTDATE AS LonIntEgFaktDat        
   ,N30.FOMDAT AS FakturaFran        
   ,N30.TOMDAT AS FakturaTill        
   ,N30.VARDDAGAR AS FakturaVarddagar        
   ,ISNULL(N30.BELOPP,0) AS FakturaBelopp        
   ,N30.BETDAG AS FakturaBetaldatum,        
                      
    N70.PRELTOMDAT AS Preltill, --confirm mapping                      
    V0A.NAMN,                      
    OMR.NAMN AS OMRADE,                      
    C20.ORGID,                      
    C20.ORGENHNR,                      
    V0A.PNR,                      
    Q01_PLFORM.KLARTEXT AS Placeringsform,                      
    N70.PROCENT,                      
    V0L.RESURSBEN,                      
    Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,                      
    Q01_RESURSKAT.AKTIV AS RESURSKAT_STATUS,                      
    Q01_RESURSKAT.KOD AS RESURSKAT,                      
    V0L.PNR AS RESURSPNR,                      
    N70.RESTILLDNR,                      
    Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,                      
    Q01_RESURSTYP.AKTIV AS RESURSTYP_STATUS,                     
    Q01_RESURSTYP.KOD AS RESURSTYP,                      
    Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,                      
    Q01_StatistikGroup1.AKTIV AS StatistikGroup1_Status,                      
    Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,                      
    Q01_StatistikGroup2.AKTIV AS StatistikGroup2_Status,                      
    Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,                      
    Q01_StatistikGroup3.AKTIV AS StatistikGroup3_Status,                      
    Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,                      
    Q01_StatistikGroup4.AKTIV AS StatistikGroup4_Status,                      
    Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,                      
    Q01_StatistikGroup5.AKTIV AS StatistikGroup5_Status,                      
    Q01_StatistikGroup1.KOD AS StatistikGroup1,                      
    Q01_StatistikGroup2.KOD AS StatistikGroup2,                      
    Q01_StatistikGroup3.KOD AS StatistikGroup3,                      
    Q01_StatistikGroup4.KOD AS StatistikGroup4,                      
    Q01_StatistikGroup5.KOD AS StatistikGroup5,                      
    Q01_RESURSSTAT.KLARTEXT AS STATUS,                      
    N70.TOMDAT,                      
    Q01_HEMTYP.KLARTEXT AS HEMTYP,             
    J01.NAMN AS ORGNAMN,                      
    J01.INAKTIV AS ORG_STATUS,                      
    V0A.FODDAT,                      
    V0A.KUNDNUMMER,                    
    V0L.COMPANY,
    V0A.ADRSKYDD,
    V0A.SKYDDADFOLKBOKF
    FROM LD0V1N70 N70                      
    JOIN LD0V0N20 N20 ON  N20.INSATSNR   = N70.INSNR AND N20.TOMDAT >= @CostRevenueFromDate_ AND N20.FOMDAT <= @CostRevenueToDate_-- own fee --PrelIntEgenavgBel--N20.RESTILLDNR   = N70.RESTILLDNR AND                      
  left join LD0VYC20  C20 on N70.INSNR=C20.INSNR                       
  left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR                      
  left join LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND V0A.NYCKKOD=NKO.NYCKKOD                      
  left join LD0VAA0OMR OMR on  NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR                      
  left join LD0V0I  V0I on N70.HAND = V0I.HAND                   
  left join LD0V0L01 L01 on N70.RESURSSOMNR=L01.RESURSSOMNR                       
  left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR                      
  left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR                       
  left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP                      
  left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP             
  left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1                      
  left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2                      
  left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3                      
  left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4                      
  left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5                      
  left join LD0V0Q01 Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT                      
  left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN                      
  left join LD0V0Q01 Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP                      
  left join LD0V0Q01 Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN                      
  left join LD0V0Q01 Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT                      
  left join LD0V0Q01 Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT                      
  left join LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR                      
  left join LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL                      
  left join LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP                      
  left join LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5                        
  left join LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD = Q01_LAGRUM.XNUM1                      
  left join LD0P0N93 N93 on N70.RESTILLDNR = N93.RESOURCEALLOCATIONID         
  left join LD0V0N30 N30 on N70.RESTILLDNR = N30.RESTILLDNR        
  WHERE                      
  (@PersonId_ IS NULL OR @PersonId_ = V0A.PNR)                      
  AND 1 = CASE @Status_ WHEN 'OngoingInPeriod' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT >= @FromDate_ THEN 1 ELSE 0 END)                        
                 WHEN 'OngoingInPeriodAndStillOngoingToday' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT = @MaxDate THEN 1 ELSE 0 END)                      
        WHEN 'InitiatedDuringThePeriod' THEN (CASE WHEN N70.FOMDAT >= @FromDate_ AND N70.FOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                      
        WHEN 'EndedDuringThePeriod' THEN (CASE WHEN N70.TOMDAT >= @FromDate_ AND N70.TOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                       
        ELSE 0 END                      
  AND 1 = CASE @GenderVerification_ WHEN 'Females' THEN (CASE WHEN V0A.KON = 0 THEN 1 ELSE 0 END)                      
            WHEN 'Males' THEN (CASE WHEN V0A.KON = 1 THEN 1 ELSE 0 END)                      
            ELSE 1 END                      
  AND ((@AgeFrom_ IS NULL AND @AgeTo_ IS NULL) OR (V0A.FODDAT >= @AgeFrom_ AND V0A.FODDAT <= @AgeTo_))                      
  AND ((@BirthFromDate_ IS NULL) OR (V0A.FODDAT >= @BirthFromDate_))                      
  AND ((@BirthToDate_ IS NULL) OR (V0A.FODDAT <= @BirthToDate_))                
  AND ((@ResourceTypeList_ IS NULL) OR (1 = CASE WHEN @ResourceTypeList_ IS NOT NULL AND Q01_RESURSTYP.KOD IN (select value from STRING_SPLIT(@ResourceTypeList_,','))                   
THEN 1 ELSE 0 END) )                
  AND ((@ResourceCategoryType_ IS NULL) OR (Q01_RESURSKAT.KOD = @ResourceCategoryType_))                      
  AND ((@OrganizationUnitId_ IS NULL) OR (1 = CASE @IsSearchOnSubLevels_ WHEN 0 THEN (CASE WHEN C20.ORGENHNR IN(select value from STRING_SPLIT(@OrganizationUnitId_,','))THEN 1 ELSE 0 END)                      
                      WHEN 1 THEN (CASE WHEN EXISTS (SELECT 1 FROM STRING_SPLIT(@OrganizationUnitId_, ',') s WHERE C20.ORGENHNR LIKE s.value + '%') THEN 1 ELSE 0 END)                      
                      ELSE 0 END))                      
  AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= V0I.HAND)                      
  AND (@StatisticsGroup1_  = 0 OR @StatisticsGroup1_= Q01_StatistikGroup1.KOD)                      
  AND (@StatisticsGroup2_  = 0 OR @StatisticsGroup2_= Q01_StatistikGroup2.KOD)                      
  AND (@StatisticsGroup3_  = 0 OR @StatisticsGroup3_= Q01_StatistikGroup3.KOD)                      
  AND (@StatisticsGroup4_  = 0 OR @StatisticsGroup4_= Q01_StatistikGroup4.KOD)                      
  AND (@StatisticsGroup5_  = 0 OR @StatisticsGroup5_= Q01_StatistikGroup5.KOD)                      
                           
  UNION ALL               
  --PreLoneBel                      
  SELECT                        
    V0L.ANNANKOMU,                      
    V0L.AVTAL,                      
    N70.FOMDAT,              V0I.HAND AS CASEWORKERID,                      
    V0I.NAMN AS CASEWORKER,                      
    V0I.FOMDAT AS CASEWORKER_FOMDAT,                      
    V0I.TOMDAT AS CASEWORKER_TOMDAT,                      
    V0L.HUVUDMAN,                      
    Q01_INSATS.KLARTEXT AS INSATS,                      
    C20.INSNR,                      
    Q01_KOMU.KLARTEXT AS KOMMUN,                      
    V0A.KON,                      
    Q01_LAGRUMTYP.KLARTEXT AS LAGRUM,                      
    Q01_LAN.KLARTEXT AS LAN,                      
    CAST(LEFT(Q01_KOMU.EXTKOD, 2) AS int) AS LANSKOD,                       
                      
    ISNULL(N93.AMOUNT,0) AS Vardfakturabelopp                      
   ,0 AS IntEgenavgBel                      
   ,0 AS LoneBel                      
   ,0 AS PrelIntEgenavgBel                      
   ,ISNULL(N50_1.SUMMA,0) AS PreLoneBel                      
   ,0 AS PrelKostnadBel                      
   ,0 AS OvrigtForloradArbfortjanst                      
   ,0 AS OvrigtMilersattning                      
   ,N93.FROMDATE AS LonIntEgFaktKostnFrom                      
      ,N93.TODATE AS LonIntEgFaktKostnTom                      
      ,N93.PAYMENTDATE AS LonIntEgFaktDat         
  ,N30.FOMDAT AS FakturaFran        
  ,N30.TOMDAT AS FakturaTill        
  ,N30.VARDDAGAR AS FakturaVarddagar        
  ,ISNULL(N30.BELOPP,0) AS FakturaBelopp        
  ,N30.BETDAG AS FakturaBetaldatum,        
                      
    N70.PRELTOMDAT AS Preltill, --confirm mapping                      
    V0A.NAMN,                      
    OMR.NAMN AS OMRADE,                      
    C20.ORGID,                 
    C20.ORGENHNR,                      
    V0A.PNR,                      
    Q01_PLFORM.KLARTEXT AS Placeringsform,                      
    N70.PROCENT,                      
    V0L.RESURSBEN,                      
    Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,                      
    Q01_RESURSKAT.AKTIV AS RESURSKAT_STATUS,                      
    Q01_RESURSKAT.KOD AS RESURSKAT,                      
    V0L.PNR AS RESURSPNR,                      
    N70.RESTILLDNR,                      
    Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,                      
    Q01_RESURSTYP.AKTIV AS RESURSTYP_STATUS,                      
    Q01_RESURSTYP.KOD AS RESURSTYP,                      
    Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,       
    Q01_StatistikGroup1.AKTIV AS StatistikGroup1_Status,                      
    Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,                      
    Q01_StatistikGroup2.AKTIV AS StatistikGroup2_Status,                      
    Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,                      
    Q01_StatistikGroup3.AKTIV AS StatistikGroup3_Status,                      
    Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,                      
    Q01_StatistikGroup4.AKTIV AS StatistikGroup4_Status,                      
    Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,                      
    Q01_StatistikGroup5.AKTIV AS StatistikGroup5_Status,                      
    Q01_StatistikGroup1.KOD AS StatistikGroup1,                      
    Q01_StatistikGroup2.KOD AS StatistikGroup2,                      
    Q01_StatistikGroup3.KOD AS StatistikGroup3,                      
    Q01_StatistikGroup4.KOD AS StatistikGroup4,                      
    Q01_StatistikGroup5.KOD AS StatistikGroup5,                      
    Q01_RESURSSTAT.KLARTEXT AS STATUS,                      
    N70.TOMDAT,                      
    Q01_HEMTYP.KLARTEXT AS HEMTYP,                      
    J01.NAMN AS ORGNAMN,                      
    J01.INAKTIV AS ORG_STATUS,                      
    V0A.FODDAT,                      
    V0A.KUNDNUMMER,                    
    V0L.COMPANY,
    V0A.ADRSKYDD,
    V0A.SKYDDADFOLKBOKF
    FROM LD0V1N70 N70                      
    JOIN LD0V0N50 N50_1 ON N50_1.RESTILLDNR = N70.RESTILLDNR AND N50_1.KILOMETER=0 AND N50_1.FORLARBFORTJ = 0 AND N50_1.TOMDAT >= @CostRevenueFromDate_ AND N50_1.FOMDAT <= @CostRevenueToDate_--PreLoneBel                      
    AND N50_1.TOMDAT <= @CostRevenueToDate_--PreLoneBel                      
  left join LD0VYC20  C20 on N70.INSNR=C20.INSNR                       
  left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR                      
  left join LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND V0A.NYCKKOD=NKO.NYCKKOD                      
  left join LD0VAA0OMR OMR on  NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR                      
  left join LD0V0I  V0I on N70.HAND = V0I.HAND                      
  left join LD0V0L01  L01 on N70.RESURSSOMNR=L01.RESURSSOMNR                       
  left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR                      
  left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR                       
  left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP                      
  left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1                      
  left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2                      
  left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3                      
  left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4                      
  left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5                      
  left join LD0V0Q01 Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT                      
  left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN                      
  left join LD0V0Q01 Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP                      
  left join LD0V0Q01 Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN                      
  left join LD0V0Q01 Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT                      
  left join LD0V0Q01 Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT                      
  left join LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR                      
  left join LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL                      
  left join LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP             
  left join LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5                        
  left join LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD = Q01_LAGRUM.XNUM1                      
  left join LD0P0N93 N93 on N70.RESTILLDNR = N93.RESOURCEALLOCATIONID           
  left join LD0V0N30 N30 on N70.RESTILLDNR = N30.RESTILLDNR        
  WHERE               
  (@PersonId_ IS NULL OR @PersonId_ = V0A.PNR)                      
  AND 1 = CASE @Status_ WHEN 'OngoingInPeriod' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT >= @FromDate_ THEN 1 ELSE 0 END)                        
                 WHEN 'OngoingInPeriodAndStillOngoingToday' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT = @MaxDate THEN 1 ELSE 0 END)                      
        WHEN 'InitiatedDuringThePeriod' THEN (CASE WHEN N70.FOMDAT >= @FromDate_ AND N70.FOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                      
        WHEN 'EndedDuringThePeriod' THEN (CASE WHEN N70.TOMDAT >= @FromDate_ AND N70.TOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                       
        ELSE 0 END                      
  AND 1 = CASE @GenderVerification_ WHEN 'Females' THEN (CASE WHEN V0A.KON = 0 THEN 1 ELSE 0 END)                      
            WHEN 'Males' THEN (CASE WHEN V0A.KON = 1 THEN 1 ELSE 0 END)                      
            ELSE 1 END                      
  AND ((@AgeFrom_ IS NULL AND @AgeTo_ IS NULL) OR (V0A.FODDAT >= @AgeFrom_ AND V0A.FODDAT <= @AgeTo_))                      
  AND ((@BirthFromDate_ IS NULL) OR (V0A.FODDAT >= @BirthFromDate_))                      
  AND ((@BirthToDate_ IS NULL) OR (V0A.FODDAT <= @BirthToDate_))                      
  AND ((@ResourceTypeList_ IS NULL) OR (1 = CASE WHEN @ResourceTypeList_ IS NOT NULL AND Q01_RESURSTYP.KOD IN (select value from STRING_SPLIT(@ResourceTypeList_,','))                   
THEN 1 ELSE 0 END) )                
  AND ((@ResourceCategoryType_ IS NULL) OR (Q01_RESURSKAT.KOD = @ResourceCategoryType_))                      
  AND ((@OrganizationUnitId_ IS NULL) OR (1 = CASE @IsSearchOnSubLevels_ WHEN 0 THEN (CASE WHEN C20.ORGENHNR IN(select value from STRING_SPLIT(@OrganizationUnitId_,',')) THEN 1 ELSE 0 END)                      
                      WHEN 1 THEN (CASE WHEN EXISTS (SELECT 1 FROM STRING_SPLIT(@OrganizationUnitId_, ',') s WHERE C20.ORGENHNR LIKE s.value + '%') THEN 1 ELSE 0 END)                      
                      ELSE 0 END))                      
  AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= V0I.HAND)                      
  AND (@StatisticsGroup1_  = 0 OR @StatisticsGroup1_= Q01_StatistikGroup1.KOD)                      
  AND (@StatisticsGroup2_  = 0 OR @StatisticsGroup2_= Q01_StatistikGroup2.KOD)                      
  AND (@StatisticsGroup3_  = 0 OR @StatisticsGroup3_= Q01_StatistikGroup3.KOD)                      
  AND (@StatisticsGroup4_  = 0 OR @StatisticsGroup4_= Q01_StatistikGroup4.KOD)                      
  AND (@StatisticsGroup5_  = 0 OR @StatisticsGroup5_= Q01_StatistikGroup5.KOD)                      
                           
  UNION ALL                      
  --PrelKostnadBel                      
   SELECT                        
    V0L.ANNANKOMU,                      
    V0L.AVTAL,                      
    N70.FOMDAT,                      
    V0I.HAND AS CASEWORKERID,                      
    V0I.NAMN AS CASEWORKER,                      
    V0I.FOMDAT AS CASEWORKER_FOMDAT,                      
    V0I.TOMDAT AS CASEWORKER_TOMDAT,                      
    V0L.HUVUDMAN,                      
    Q01_INSATS.KLARTEXT AS INSATS,                      
    C20.INSNR,                      
    Q01_KOMU.KLARTEXT AS KOMMUN,                      
    V0A.KON,                      
    Q01_LAGRUMTYP.KLARTEXT AS LAGRUM,                      
    Q01_LAN.KLARTEXT AS LAN,                      
    CAST(LEFT(Q01_KOMU.EXTKOD, 2) AS int) AS LANSKOD,                         
                      
     ISNULL(N93.AMOUNT,0) AS Vardfakturabelopp                      
    ,0 AS IntEgenavgBel                      
    ,0 AS LoneBel                      
    ,0 AS PrelIntEgenavgBel                      
    ,0 AS PreLoneBel                      
    ,ISNULL(N71.SUMMA,0) AS PrelKostnadBel                      
    ,0 AS OvrigtForloradArbfortjanst                      
    ,0 AS OvrigtMilersattning                   
    ,N93.FROMDATE AS LonIntEgFaktKostnFrom                      
       ,N93.TODATE AS LonIntEgFaktKostnTom                      
       ,N93.PAYMENTDATE AS LonIntEgFaktDat        
  ,N30.FOMDAT AS FakturaFran        
  ,N30.TOMDAT AS FakturaTill        
  ,N30.VARDDAGAR AS FakturaVarddagar        
  ,ISNULL(N30.BELOPP,0) AS FakturaBelopp        
  ,N30.BETDAG AS FakturaBetaldatum,        
                      
    N70.PRELTOMDAT AS Preltill, --confirm mapping                      
    V0A.NAMN,                      
    OMR.NAMN AS OMRADE,                      
    C20.ORGID,                      
    C20.ORGENHNR,                      
    V0A.PNR,                      
    Q01_PLFORM.KLARTEXT AS Placeringsform,                      
    N70.PROCENT,                      
    V0L.RESURSBEN,                      
    Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,                      
    Q01_RESURSKAT.AKTIV AS RESURSKAT_STATUS,             
    Q01_RESURSKAT.KOD AS RESURSKAT,                      
    V0L.PNR AS RESURSPNR,                      
    N70.RESTILLDNR,                      
    Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,                      
    Q01_RESURSTYP.AKTIV AS RESURSTYP_STATUS,                      
    Q01_RESURSTYP.KOD AS RESURSTYP,                      
    Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,                      
    Q01_StatistikGroup1.AKTIV AS StatistikGroup1_Status,                      
    Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,                      
    Q01_StatistikGroup2.AKTIV AS StatistikGroup2_Status,                      
    Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,                      
    Q01_StatistikGroup3.AKTIV AS StatistikGroup3_Status,                      
    Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,                      
    Q01_StatistikGroup4.AKTIV AS StatistikGroup4_Status,                      
    Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,                      
    Q01_StatistikGroup5.AKTIV AS StatistikGroup5_Status,                      
    Q01_StatistikGroup1.KOD AS StatistikGroup1,                      
    Q01_StatistikGroup2.KOD AS StatistikGroup2,                      
    Q01_StatistikGroup3.KOD AS StatistikGroup3,                      
    Q01_StatistikGroup4.KOD AS StatistikGroup4,                      
    Q01_StatistikGroup5.KOD AS StatistikGroup5,                      
    Q01_RESURSSTAT.KLARTEXT AS STATUS,                      
    N70.TOMDAT,                      
    Q01_HEMTYP.KLARTEXT AS HEMTYP,                      
    J01.NAMN AS ORGNAMN,                      
    J01.INAKTIV AS ORG_STATUS,                      
    V0A.FODDAT,                      
    V0A.KUNDNUMMER ,                    
    V0L.COMPANY,
    V0A.ADRSKYDD,
    V0A.SKYDDADFOLKBOKF
    FROM LD0V1N70 N70                      
    JOIN LD0V0N71 N71 ON N71.RESTILLDNR = N70.RESTILLDNR AND N71.TOMDAT >= @CostRevenueFromDate_ AND N71.FOMDAT <= @CostRevenueToDate_ --pricelist--PrelKostnadBel                      
  left join LD0VYC20  C20 on N70.INSNR=C20.INSNR                       
left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR                      
  left join LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND V0A.NYCKKOD=NKO.NYCKKOD                      
  left join LD0VAA0OMR OMR on  NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR                      
  left join LD0V0I  V0I on N70.HAND = V0I.HAND                      
  left join LD0V0L01  L01 on N70.RESURSSOMNR=L01.RESURSSOMNR                       
  left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR                      
  left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR                       
  left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP                      
  left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1                      
  left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2                      
  left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3                      
  left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4                      
  left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5                      
  left join LD0V0Q01 Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT                      
  left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN                      
  left join LD0V0Q01 Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP                      
  left join LD0V0Q01 Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN                      
  left join LD0V0Q01 Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT                      
  left join LD0V0Q01 Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT                      
  left join LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR                      
  left join LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL                      
  left join LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP                      
  left join LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5                        
  left join LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD = Q01_LAGRUM.XNUM1                      
  left join LD0P0N93 N93 on N70.RESTILLDNR = N93.RESOURCEALLOCATIONID              
  left join LD0V0N30 N30 on N70.RESTILLDNR = N30.RESTILLDNR        
  WHERE                      
  (@PersonId_ IS NULL OR @PersonId_ = V0A.PNR)                      
  AND 1 = CASE @Status_ WHEN 'OngoingInPeriod' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT >= @FromDate_ THEN 1 ELSE 0 END)                        
                 WHEN 'OngoingInPeriodAndStillOngoingToday' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT = @MaxDate THEN 1 ELSE 0 END)                      
        WHEN 'InitiatedDuringThePeriod' THEN (CASE WHEN N70.FOMDAT >= @FromDate_ AND N70.FOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                      
        WHEN 'EndedDuringThePeriod' THEN (CASE WHEN N70.TOMDAT >= @FromDate_ AND N70.TOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                       
        ELSE 0 END                      
  AND 1 = CASE @GenderVerification_ WHEN 'Females' THEN (CASE WHEN V0A.KON = 0 THEN 1 ELSE 0 END)                      
            WHEN 'Males' THEN (CASE WHEN V0A.KON = 1 THEN 1 ELSE 0 END)                      
            ELSE 1 END                      
  AND ((@AgeFrom_ IS NULL AND @AgeTo_ IS NULL) OR (V0A.FODDAT >= @AgeFrom_ AND V0A.FODDAT <= @AgeTo_))                      
  AND ((@BirthFromDate_ IS NULL) OR (V0A.FODDAT >= @BirthFromDate_))                
  AND ((@BirthToDate_ IS NULL) OR (V0A.FODDAT <= @BirthToDate_))                      
  AND ((@ResourceTypeList_ IS NULL) OR (1 = CASE WHEN @ResourceTypeList_ IS NOT NULL AND Q01_RESURSTYP.KOD IN (select value from STRING_SPLIT(@ResourceTypeList_,','))                   
THEN 1 ELSE 0 END) )                
  AND ((@ResourceCategoryType_ IS NULL) OR (Q01_RESURSKAT.KOD = @ResourceCategoryType_))                      
  AND ((@OrganizationUnitId_ IS NULL) OR (1 = CASE @IsSearchOnSubLevels_ WHEN 0 THEN (CASE WHEN @OrganizationUnitId_ IS NOT NULL AND C20.ORGENHNR IN(select value from STRING_SPLIT(@OrganizationUnitId_,',')) THEN 1 ELSE 0 END)                      
                      WHEN 1 THEN (CASE WHEN EXISTS (SELECT 1 FROM STRING_SPLIT(@OrganizationUnitId_, ',') s WHERE C20.ORGENHNR LIKE s.value + '%') THEN 1 ELSE 0 END)                      
                      ELSE 0 END))                      
  AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= V0I.HAND)                      
  AND (@StatisticsGroup1_  = 0 OR @StatisticsGroup1_= Q01_StatistikGroup1.KOD)                      
  AND (@StatisticsGroup2_  = 0 OR @StatisticsGroup2_= Q01_StatistikGroup2.KOD)                      
  AND (@StatisticsGroup3_  = 0 OR @StatisticsGroup3_= Q01_StatistikGroup3.KOD)                      
  AND (@StatisticsGroup4_  = 0 OR @StatisticsGroup4_= Q01_StatistikGroup4.KOD)                      
  AND (@StatisticsGroup5_  = 0 OR @StatisticsGroup5_= Q01_StatistikGroup5.KOD)                      
                      
    UNION ALL                      
                      
  --ovrigt Forlorad Arbfortjanst                      
  SELECT                         
  V0L.ANNANKOMU,                      
  V0L.AVTAL,                      
  N70.FOMDAT,                      
  V0I.HAND AS CASEWORKERID,                      
  V0I.NAMN AS CASEWORKER,                      
  V0I.FOMDAT AS CASEWORKER_FOMDAT,                      
  V0I.TOMDAT AS CASEWORKER_TOMDAT,                      
  V0L.HUVUDMAN,                      
  Q01_INSATS.KLARTEXT AS INSATS,                      
  C20.INSNR,                      
  Q01_KOMU.KLARTEXT AS KOMMUN,                    
  V0A.KON,                      
  Q01_LAGRUMTYP.KLARTEXT AS LAGRUM,                      
  Q01_LAN.KLARTEXT AS LAN,                      
  CAST(LEFT(Q01_KOMU.EXTKOD, 2) AS int) AS LANSKOD,                         
                      
   ISNULL(N93.AMOUNT,0) AS Vardfakturabelopp                      
  ,0 AS IntEgenavgBel                      
  ,0 AS LoneBel                      
  ,0 AS PrelIntEgenavgBel                      
  ,0 AS PreLoneBel                      
  ,0 AS PrelKostnadBel                      
  ,N50_2.SUMMA AS OvrigtForloradArbfortjanst                      
  ,0 AS OvrigtMilersattning                      
  ,N93.FROMDATE AS LonIntEgFaktKostnFrom                      
,N93.TODATE AS LonIntEgFaktKostnTom                      
     ,N93.PAYMENTDATE AS LonIntEgFaktDat        
  ,N30.FOMDAT AS FakturaFran        
  ,N30.TOMDAT AS FakturaTill        
  ,N30.VARDDAGAR AS FakturaVarddagar        
  ,ISNULL(N30.BELOPP,0) AS FakturaBelopp        
  ,N30.BETDAG AS FakturaBetaldatum,        
                      
  N70.PRELTOMDAT AS Preltill, --confirm mapping                      
  V0A.NAMN,                      
  OMR.NAMN AS OMRADE,                      
  C20.ORGID,                     
  C20.ORGENHNR,                      
  V0A.PNR,                      
  Q01_PLFORM.KLARTEXT AS Placeringsform,                      
  N70.PROCENT,                      
  V0L.RESURSBEN,                      
  Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,                      
  Q01_RESURSKAT.AKTIV AS RESURSKAT_STATUS,                      
  Q01_RESURSKAT.KOD AS RESURSKAT,                      
  V0L.PNR AS RESURSPNR,                      
  N70.RESTILLDNR,                      
  Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,                      
  Q01_RESURSTYP.AKTIV AS RESURSTYP_STATUS,                      
  Q01_RESURSTYP.KOD AS RESURSTYP,                      
  Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,                      
  Q01_StatistikGroup1.AKTIV AS StatistikGroup1_Status,                      
  Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,                      
  Q01_StatistikGroup2.AKTIV AS StatistikGroup2_Status,                      
  Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,                      
  Q01_StatistikGroup3.AKTIV AS StatistikGroup3_Status,                      
  Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,                      
  Q01_StatistikGroup4.AKTIV AS StatistikGroup4_Status,                      
  Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,                      
  Q01_StatistikGroup5.AKTIV AS StatistikGroup5_Status,                      
  Q01_StatistikGroup1.KOD AS StatistikGroup1,                      
  Q01_StatistikGroup2.KOD AS StatistikGroup2,                      
  Q01_StatistikGroup3.KOD AS StatistikGroup3,     
  Q01_StatistikGroup4.KOD AS StatistikGroup4,                      
  Q01_StatistikGroup5.KOD AS StatistikGroup5,                      
  Q01_RESURSSTAT.KLARTEXT AS STATUS,                      
  N70.TOMDAT,                      
  Q01_HEMTYP.KLARTEXT AS HEMTYP,               
  J01.NAMN AS ORGNAMN,                      
  J01.INAKTIV AS ORG_STATUS,                      
  V0A.FODDAT,                      
  V0A.KUNDNUMMER ,                    
  V0L.COMPANY,
  V0A.ADRSKYDD,
  V0A.SKYDDADFOLKBOKF
  FROM LD0V1N70 N70                      
  JOIN LD0V0N50 N50_2 ON N50_2.RESTILLDNR = N70.RESTILLDNR AND N50_2.FORLARBFORTJ > 0 AND N50_2.TOMDAT >= @CostRevenueFromDate_ AND N50_2.FOMDAT <= @CostRevenueToDate_----ovrigt Forlorad Arbfortjanst                      
  left join LD0VYC20  C20 on N70.INSNR=C20.INSNR                       
  left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR                      
  left join LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND V0A.NYCKKOD=NKO.NYCKKOD                      
  left join LD0VAA0OMR OMR on  NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR                      
  left join LD0V0I  V0I on N70.HAND = V0I.HAND                      
  left join LD0V0L01  L01 on N70.RESURSSOMNR=L01.RESURSSOMNR                       
  left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR                      
  left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR                       
  left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP                      
  left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1                      
  left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2                      
  left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3                      
  left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4                      
  left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5                      
  left join LD0V0Q01 Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT                      
  left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN                      
  left join LD0V0Q01 Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP                      
  left join LD0V0Q01 Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN                      
  left join LD0V0Q01 Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT                      
  left join LD0V0Q01 Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT                      
  left join LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR                      
  left join LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL                      
  left join LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP                      
  left join LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5                        
  left join LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD = Q01_LAGRUM.XNUM1                      
  left join LD0P0N93 N93 on N70.RESTILLDNR = N93.RESOURCEALLOCATIONID          
  left join LD0V0N30 N30 on N70.RESTILLDNR = N30.RESTILLDNR        
  WHERE                      
  (@PersonId_ IS NULL OR @PersonId_ = V0A.PNR)                      
  AND 1 = CASE @Status_ WHEN 'OngoingInPeriod' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT >= @FromDate_ THEN 1 ELSE 0 END)                        
                 WHEN 'OngoingInPeriodAndStillOngoingToday' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT = @MaxDate THEN 1 ELSE 0 END)                      
        WHEN 'InitiatedDuringThePeriod' THEN (CASE WHEN N70.FOMDAT >= @FromDate_ AND N70.FOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                      
        WHEN 'EndedDuringThePeriod' THEN (CASE WHEN N70.TOMDAT >= @FromDate_ AND N70.TOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                       
        ELSE 0 END                      
  AND 1 = CASE @GenderVerification_ WHEN 'Females' THEN (CASE WHEN V0A.KON = 0 THEN 1 ELSE 0 END)                      
            WHEN 'Males' THEN (CASE WHEN V0A.KON = 1 THEN 1 ELSE 0 END)                      
            ELSE 1 END                      
  AND ((@AgeFrom_ IS NULL AND @AgeTo_ IS NULL) OR (V0A.FODDAT >= @AgeFrom_ AND V0A.FODDAT <= @AgeTo_))                      
  AND ((@BirthFromDate_ IS NULL) OR (V0A.FODDAT >= @BirthFromDate_))                      
  AND ((@BirthToDate_ IS NULL) OR (V0A.FODDAT <= @BirthToDate_))                      
  AND ((@ResourceTypeList_ IS NULL) OR (1 = CASE WHEN @ResourceTypeList_ IS NOT NULL AND Q01_RESURSTYP.KOD IN (select value from STRING_SPLIT(@ResourceTypeList_,','))                   
THEN 1 ELSE 0 END) )                 
  AND ((@ResourceCategoryType_ IS NULL) OR (Q01_RESURSKAT.KOD = @ResourceCategoryType_))                      
  AND ((@OrganizationUnitId_ IS NULL) OR (1 = CASE @IsSearchOnSubLevels_ WHEN 0 THEN (CASE WHEN @OrganizationUnitId_ IS NOT NULL AND C20.ORGENHNR IN(select value from STRING_SPLIT(@OrganizationUnitId_,',')) THEN 1 ELSE 0 END)                      
                      WHEN 1 THEN (CASE WHEN EXISTS (SELECT 1 FROM STRING_SPLIT(@OrganizationUnitId_, ',') s WHERE C20.ORGENHNR LIKE s.value + '%') THEN 1 ELSE 0 END)                      
                      ELSE 0 END))                      
  AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= V0I.HAND)                      
  AND (@StatisticsGroup1_  = 0 OR @StatisticsGroup1_= Q01_StatistikGroup1.KOD)                      
  AND (@StatisticsGroup2_  = 0 OR @StatisticsGroup2_= Q01_StatistikGroup2.KOD)                      
  AND (@StatisticsGroup3_  = 0 OR @StatisticsGroup3_= Q01_StatistikGroup3.KOD)                      
  AND (@StatisticsGroup4_  = 0 OR @StatisticsGroup4_= Q01_StatistikGroup4.KOD)                      
  AND (@StatisticsGroup5_  = 0 OR @StatisticsGroup5_= Q01_StatistikGroup5.KOD)                      
                        
  UNION ALL                      
                      
  -- ovrigt Milersattning                      
  SELECT                       
  V0L.ANNANKOMU,                      
  V0L.AVTAL,                      
  N70.FOMDAT,                      
  V0I.HAND AS CASEWORKERID,                      
  V0I.NAMN AS CASEWORKER,                      
  V0I.FOMDAT AS CASEWORKER_FOMDAT,                      
  V0I.TOMDAT AS CASEWORKER_TOMDAT,                      
  V0L.HUVUDMAN,                      
  Q01_INSATS.KLARTEXT AS INSATS,                      
  C20.INSNR,                      
  Q01_KOMU.KLARTEXT AS KOMMUN,                      
  V0A.KON,                      
  Q01_LAGRUMTYP.KLARTEXT AS LAGRUM,                      
  Q01_LAN.KLARTEXT AS LAN,                      
  CAST(LEFT(Q01_KOMU.EXTKOD, 2) AS int) AS LANSKOD,                         
                        
   ISNULL(N93.AMOUNT,0) AS Vardfakturabelopp                      
  ,0 AS IntEgenavgBel                      
  ,0 AS LoneBel                      
  ,0 AS PrelIntEgenavgBel                      
  ,0 AS PreLoneBel                      
  ,0 AS PrelKostnadBel                      
  ,0 AS OvrigtForloradArbfortjanst                      
  ,N50_3.SUMMA AS OvrigtMilersattning                      
  ,N93.FROMDATE AS LonIntEgFaktKostnFrom                      
     ,N93.TODATE AS LonIntEgFaktKostnTom                      
     ,N93.PAYMENTDATE AS LonIntEgFaktDat        
  ,N30.FOMDAT AS FakturaFran        
  ,N30.TOMDAT AS FakturaTill        
  ,N30.VARDDAGAR AS FakturaVarddagar        
  ,ISNULL(N30.BELOPP,0) AS FakturaBelopp        
  ,N30.BETDAG AS FakturaBetaldatum,        
                      
  N70.PRELTOMDAT AS Preltill, --confirm mapping                      
  V0A.NAMN,                      
  OMR.NAMN AS OMRADE,                  C20.ORGID,                
  C20.ORGENHNR,                      
  V0A.PNR,                      
  Q01_PLFORM.KLARTEXT AS Placeringsform,                      
  N70.PROCENT,                      
  V0L.RESURSBEN,      Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,                      
  Q01_RESURSKAT.AKTIV AS RESURSKAT_STATUS,                      
  Q01_RESURSKAT.KOD AS RESURSKAT,                      
  V0L.PNR AS RESURSPNR,                      
  N70.RESTILLDNR,                      
  Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,                      
  Q01_RESURSTYP.AKTIV AS RESURSTYP_STATUS,                     
  Q01_RESURSTYP.KOD AS RESURSTYP,                      
  Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,                      
  Q01_StatistikGroup1.AKTIV AS StatistikGroup1_Status,                      
  Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,                      
  Q01_StatistikGroup2.AKTIV AS StatistikGroup2_Status,                      
  Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,                      
  Q01_StatistikGroup3.AKTIV AS StatistikGroup3_Status,                      
  Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,                      
  Q01_StatistikGroup4.AKTIV AS StatistikGroup4_Status,                      
  Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,                      
 Q01_StatistikGroup5.AKTIV AS StatistikGroup5_Status,                      
  Q01_StatistikGroup1.KOD AS StatistikGroup1,                      
  Q01_StatistikGroup2.KOD AS StatistikGroup2,                      
  Q01_StatistikGroup3.KOD AS StatistikGroup3,                      
  Q01_StatistikGroup4.KOD AS StatistikGroup4,                      
  Q01_StatistikGroup5.KOD AS StatistikGroup5,                  
  Q01_RESURSSTAT.KLARTEXT AS STATUS,                      
  N70.TOMDAT,                      
  Q01_HEMTYP.KLARTEXT AS HEMTYP,                      
  J01.NAMN AS ORGNAMN,                      
  J01.INAKTIV AS ORG_STATUS,                      
  V0A.FODDAT,                      
  V0A.KUNDNUMMER ,                    
  V0L.COMPANY,
  V0A.ADRSKYDD,
  V0A.SKYDDADFOLKBOKF
  FROM LD0V1N70 N70                      
  JOIN LD0V0N50 N50_3 ON N50_3.RESTILLDNR = N70.RESTILLDNR AND N50_3.KILOMETER > 0 AND N50_3.TOMDAT >= @CostRevenueFromDate_ AND N50_3.FOMDAT <= @CostRevenueToDate_--ovrigt Milersattning                      
     left join LD0VYC20  C20 on N70.INSNR=C20.INSNR                       
  left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR                      
  left join LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND V0A.NYCKKOD=NKO.NYCKKOD                      
  left join LD0VAA0OMR OMR on  NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR                      
  left join LD0V0I  V0I on N70.HAND = V0I.HAND                      
  left join LD0V0L01  L01 on N70.RESURSSOMNR=L01.RESURSSOMNR                       
  left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR                      
  left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR                       
  left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP                      
  left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1                      
  left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2                      
  left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3                      
  left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4                      
  left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'RESURSTYP' AND Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5                      
  left join LD0V0Q01 Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT                      
  left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP                      
  left join LD0V0Q01 Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN                      
  left join LD0V0Q01 Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP                      
  left join LD0V0Q01 Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN                      
  left join LD0V0Q01 Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT                      
  left join LD0V0Q01 Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT                      
  left join LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR                      
  left join LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL                      
  left join LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP                      
  left join LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5                        
  left join LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD = Q01_LAGRUM.XNUM1                      
  left join LD0P0N93 N93 on N70.RESTILLDNR = N93.RESOURCEALLOCATIONID             
  left join LD0V0N30 N30 on N70.RESTILLDNR = N30.RESTILLDNR        
  WHERE                      
  (@PersonId_ IS NULL OR @PersonId_ = V0A.PNR)                      
  AND 1 = CASE @Status_ WHEN 'OngoingInPeriod' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT >= @FromDate_ THEN 1 ELSE 0 END)                        
                 WHEN 'OngoingInPeriodAndStillOngoingToday' THEN (CASE WHEN N70.FOMDAT <= @ToDate_ AND N70.TOMDAT = @MaxDate THEN 1 ELSE 0 END)                      
        WHEN 'InitiatedDuringThePeriod' THEN (CASE WHEN N70.FOMDAT >= @FromDate_ AND N70.FOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                      
        WHEN 'EndedDuringThePeriod' THEN (CASE WHEN N70.TOMDAT >= @FromDate_ AND N70.TOMDAT <= @ToDate_ THEN 1 ELSE 0 END)                       
        ELSE 0 END                      
  AND 1 = CASE @GenderVerification_ WHEN 'Females' THEN (CASE WHEN V0A.KON = 0 THEN 1 ELSE 0 END)                      
            WHEN 'Males' THEN (CASE WHEN V0A.KON = 1 THEN 1 ELSE 0 END)                      
            ELSE 1 END                      
  AND ((@AgeFrom_ IS NULL AND @AgeTo_ IS NULL) OR (V0A.FODDAT >= @AgeFrom_ AND V0A.FODDAT <= @AgeTo_))                      
  AND ((@BirthFromDate_ IS NULL) OR (V0A.FODDAT >= @BirthFromDate_))                      
  AND ((@BirthToDate_ IS NULL) OR (V0A.FODDAT <= @BirthToDate_))                      
  AND ((@ResourceTypeList_ IS NULL) OR (1 = CASE WHEN @ResourceTypeList_ IS NOT NULL AND Q01_RESURSTYP.KOD IN (select value from STRING_SPLIT(@ResourceTypeList_,','))                 THEN 1 ELSE 0 END) )                
  AND ((@ResourceCategoryType_ IS NULL) OR (Q01_RESURSKAT.KOD = @ResourceCategoryType_))                      
  AND ((@OrganizationUnitId_ IS NULL) OR (1 = CASE @IsSearchOnSubLevels_ WHEN 0 THEN (CASE WHEN @OrganizationUnitId_ IS NOT NULL AND C20.ORGENHNR IN(select value from STRING_SPLIT(@OrganizationUnitId_,',')) THEN 1 ELSE 0 END)                      
                      WHEN 1 THEN (CASE WHEN EXISTS (SELECT 1 FROM STRING_SPLIT(@OrganizationUnitId_, ',') s WHERE C20.ORGENHNR LIKE s.value + '%') THEN 1 ELSE 0 END)                      
                      ELSE 0 END))                      
  AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= V0I.HAND)                      
  AND (@StatisticsGroup1_  = 0 OR @StatisticsGroup1_= Q01_StatistikGroup1.KOD)                      
  AND (@StatisticsGroup2_  = 0 OR @StatisticsGroup2_= Q01_StatistikGroup2.KOD)                      
  AND (@StatisticsGroup3_  = 0 OR @StatisticsGroup3_= Q01_StatistikGroup3.KOD)                      
  AND (@StatisticsGroup4_  = 0 OR @StatisticsGroup4_= Q01_StatistikGroup4.KOD)                      
  AND (@StatisticsGroup5_  = 0 OR @StatisticsGroup5_= Q01_StatistikGroup5.KOD)                      
  )priceTable                      
                       
                      
  SELECT * FROM #DefaultResult R                      
  WHERE R.RESTILLDNR NOT IN (select RESTILLDNR FROM #ResultWithPrice )                      
  UNION ALL                      
  SELECT * FROM #ResultWithPrice                      
                        
 END                      
 ELSE                      
  BEGIN                      
   SELECT * FROM #DefaultResult R                      
  END                      
END   
GO
GRANT ALL ON LD0SPStatisticsResourceAllocation TO LD0ROANV
GO

