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"::text 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";