SELECT   
 a."Pos+id",
 d."Summary",
 a."EntityIdentifier",
 b."Comments" as txt1,
 k."Comments" as txt2,
 l."Comments" as txt3,
 m."Comments" as txt4,
 n."Comments" as txt5,
 o."Comments" as txt6,
 p."Comments" as txt7,
 q."Comments" as txt8,
 r."Comments" as txt9,
 s."Comments" as txt10,
 d."ContactPerson",
 to_date(f."DateFirstPublished"::text,'YYYYMMDD') as startdate, 
 to_date(f."DatePublishedUntil"::text,'YYYYMMDD') as enddate, e."Biz+InternetEmailAddress",
 a."OrgDat+PlaceOfWork",
 'https://recruitingapp-[CustomerID].umantis.com' as url,
 'https://recruitingapp-[CustomerID].umantis.com/Vacancies/' || a."Pos+id"::text || '/Application/New/"DynJPl"."PublicationLangID" ' as ConnectURL,
 "DynJPl"."PublicationLangID" as Sprache,
 u."Bas+Comments" as OrganisationsID,
 d."Websites" as MainOfficeID,
 a."DescPos+Position",
 a."DescEmp+EmploymentType",
 a."DescFct+Function",
 a."DescEmp+ContractType"   
  
from "Pos" a  
  
join "Pos++ExtPub++JPlS" f on 
	f."Pos+id" = a."Pos+id" and 
	f."Pos++ExtPub+id" = 1 and 
	f."Pos++ExtPub++JPlS+id" = [PlattformID]  

join "DynJPl" on "DynJPl"."DynJPl+id" = [PlattformID]  

left outer join "Pos++ExtPub" d on a."Pos+id" = d."Pos+id" and d."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"  
left outer join "Pos++ExtPub++CommSet" b on a."Pos+id" = b."Pos+id" and b."Pos++ExtPub++CommSet+id" = 1 and b."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   
left outer join "Pos++ExtPub++CommSet" k on a."Pos+id" = k."Pos+id" and k."Pos++ExtPub++CommSet+id" = 2 and k."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"    
left outer join "Pos++ExtPub++CommSet" l on a."Pos+id" = l."Pos+id" and l."Pos++ExtPub++CommSet+id" = 3 and l."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"    
left outer join "Pos++ExtPub++CommSet" m on a."Pos+id" = m."Pos+id" and m."Pos++ExtPub++CommSet+id" = 4 and m."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   
left outer join "Pos++ExtPub++CommSet" n on a."Pos+id" = n."Pos+id" and n."Pos++ExtPub++CommSet+id" = 5 and n."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"    
left outer join "Pos++ExtPub++CommSet" o on a."Pos+id" = o."Pos+id" and o."Pos++ExtPub++CommSet+id" = 6 and o."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"    
left outer join "Pos++ExtPub++CommSet" p on a."Pos+id" = p."Pos+id" and p."Pos++ExtPub++CommSet+id" = 7 and p."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"    
left outer join "Pos++ExtPub++CommSet" q on a."Pos+id" = q."Pos+id" and q."Pos++ExtPub++CommSet+id" = 8 and q."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"    
left outer join "Pos++ExtPub++CommSet" r on a."Pos+id" = r."Pos+id" and r."Pos++ExtPub++CommSet+id" = 9 and r."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"    
left outer join "Pos++ExtPub++CommSet" s on a."Pos+id" = s."Pos+id" and s."Pos++ExtPub++CommSet+id" = 10 and s."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   
left outer join "Pers" e on a."EntResp+PtrCustodianID" = e."Pers+id"  
left outer join "Pos++SklS" j on a."Pos+id" = j."Pos+id" and j."Pos++SklS+id" = 1004  
left outer join "Com" u on a."ComS+PtrLastConnectedEntityID" = u."Com+id"   

where  a."EntitySubtype" = 'PositionType_Vacancy' and
       f."IsPublished" is not null  

order by a."Pos+id", b."Pos++ExtPub++CommSet+id";