SELECT       
   "Pos"."Pos+id" as "pos_id",
   "ExtPub"."JobTitle" as "publication_title",
   "ExtPubCommSet1"."Comments" as "titel_textblock1",
   "ExtPubCommSet2"."Comments" as "textblock1",
   "ExtPubCommSet3"."Comments" as "titel_textblock2",
   "ExtPubCommSet4"."Comments" as "textblock2",
   "ExtPubCommSet5"."Comments" as "titel_textblock3",
   "ExtPubCommSet6"."Comments" as "textblock3",
   "ExtPubCommSet7"."Comments" as "titel_textblock4",
   "ExtPubCommSet8"."Comments" as "textblock4",
   "ExtPubCommSet9"."Comments" as "titel_textblock5",
   "ExtPubCommSet10"."Comments" as "textblock5",
   "ExtPub"."ContactPerson" as "contact",
   "ExtPub"."Summary" as "summary",
   to_date("ExtPubOnJpl"."DateFirstPublished"::text,'YYYYMMDDHH24MISS') as "start",
   to_date("ExtPubOnJpl"."DatePublishedUntil"::text,'YYYYMMDDHH24MISS') as "end",
   "Pers"."Biz+InternetEmailAddress" as "email",
   "Pos"."OrgDat+PlaceOfWork" as "place",
   'https://recruitingapp-' || [CustomerID]::text || '.umantis.com' as "url",
   'https://recruitingapp-' || [CustomerID]::text || '.umantis.com/Vacancies/' || "Pos"."Pos+id"::text || '/Application/CheckLogin/' || "DynJPl"."PublicationLangID"::text || '?lang=' || "DynJPl"."lang" as "apply_url",
   "DynJPl"."PublicationLangID" as "language",
   "Branch"."Cont+OrganizationName" as "branch_name",
   "Branch"."Bas+Comments" as "branch_comment",
   "ExtPub"."Websites" as "main_office_id",
   "Position"."Text" as "entry_as",
   "Beschaeftigungsart"."Text" as "employment_type",
   "Unternehmensbereich"."Text" as "department",
   "Befristung"."Text" as "terminability",
   "Pos"."DescEmp+EmplTypeDesc" as "comment_on_employment_type"       
   
FROM "Pos"   

JOIN "Pos++ExtPub++JPlS" as "ExtPubOnJpl" ON "ExtPubOnJpl"."Pos+id" = "Pos"."Pos+id" AND "ExtPubOnJpl"."Pos++ExtPub+id" = 1 AND "ExtPubOnJpl"."Pos++ExtPub++JPlS+id" = [PlattformID]   

LEFT JOIN ( Select  
	         "DynJPl"."PublicationLangID", 
	         "DynJPl"."DynJPl+id",  
	         CASE "DynJPl"."PublicationLangID"  
		         WHEN 1 THEN 'ger'   
		         WHEN 2 THEN 'eng'   
		         WHEN 3 THEN 'fre'   
		         WHEN 4 THEN 'ita'   
		         WHEN 5 THEN 'spa'   
		         WHEN 6 THEN 'por'   
		         WHEN 7 THEN 'dut'   
		         WHEN 8 THEN 'lux'   
		         WHEN 9 THEN 'dan'   
		         WHEN 10 THEN 'chi'   
		         WHEN 11 THEN 'jpn'   
		         WHEN 12 THEN 'vie'   
		         WHEN 13 THEN 'hin'   
		         WHEN 14 THEN 'ara'   
		         WHEN 15 THEN 'ben'   
		         WHEN 16 THEN 'rus'   
		         WHEN 17 THEN 'tur'   
		         WHEN 18 THEN 'ind'   
		         WHEN 19 THEN 'alb'   
		         WHEN 20 THEN 'bul'   
		         WHEN 21 THEN 'gre'   
		         WHEN 22 THEN 'nor'   
		         WHEN 23 THEN 'swe'   
		         WHEN 24 THEN 'fin'   
		         WHEN 25 THEN 'rum'   
		         WHEN 26 THEN 'slo'   
		         WHEN 27 THEN 'cze'   
		         WHEN 28 THEN 'hun'   
		         WHEN 29 THEN 'kor'   
		         WHEN 30 THEN 'pol'   
		         WHEN 31 THEN 'gerger'   
		         WHEN 32 THEN 'roh'   
		         WHEN 33 THEN 'slv'   
		         WHEN 34 THEN 'hrv'  
		         WHEN 35 THEN 'cht'   
		         WHEN 36 THEN 'gbr'   
		         WHEN 37 THEN 'ptb'  
		         WHEN 38 THEN 'tha'  
		         ELSE 'missing param' 
	         END as "lang"  FROM "DynJPl" 
         ) "DynJPl" ON "DynJPl"."DynJPl+id" = [PlattformID]   


LEFT JOIN "Pos++ExtPub" as "ExtPub" ON "Pos"."Pos+id" = "ExtPub"."Pos+id"  AND "ExtPub"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID" 

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet1" ON "Pos"."Pos+id" = "ExtPubCommSet1"."Pos+id"  AND "ExtPubCommSet1"."Pos++ExtPub++CommSet+id" = 1  AND "ExtPubCommSet1"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet2" ON "Pos"."Pos+id" = "ExtPubCommSet2"."Pos+id"  AND "ExtPubCommSet2"."Pos++ExtPub++CommSet+id" = 2  AND "ExtPubCommSet2"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet3" ON "Pos"."Pos+id" = "ExtPubCommSet3"."Pos+id"  AND "ExtPubCommSet3"."Pos++ExtPub++CommSet+id" = 3  AND "ExtPubCommSet3"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet4" ON "Pos"."Pos+id" = "ExtPubCommSet4"."Pos+id"  AND "ExtPubCommSet4"."Pos++ExtPub++CommSet+id" = 4  AND "ExtPubCommSet4"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet5" ON "Pos"."Pos+id" = "ExtPubCommSet5"."Pos+id"  AND "ExtPubCommSet5"."Pos++ExtPub++CommSet+id" = 5  AND "ExtPubCommSet5"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet6" ON "Pos"."Pos+id" = "ExtPubCommSet6"."Pos+id"  AND "ExtPubCommSet6"."Pos++ExtPub++CommSet+id" = 6  AND "ExtPubCommSet6"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet7" ON "Pos"."Pos+id" = "ExtPubCommSet7"."Pos+id"  AND "ExtPubCommSet7"."Pos++ExtPub++CommSet+id" = 7  AND "ExtPubCommSet7"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet8" ON "Pos"."Pos+id" = "ExtPubCommSet8"."Pos+id"  AND "ExtPubCommSet8"."Pos++ExtPub++CommSet+id" = 8  AND "ExtPubCommSet8"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet9" ON "Pos"."Pos+id" = "ExtPubCommSet9"."Pos+id"  AND "ExtPubCommSet9"."Pos++ExtPub++CommSet+id" = 9  AND "ExtPubCommSet9"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pos++ExtPub++CommSet" as "ExtPubCommSet10" ON "Pos"."Pos+id" = "ExtPubCommSet10"."Pos+id"  AND "ExtPubCommSet10"."Pos++ExtPub++CommSet+id" = 10  AND "ExtPubCommSet10"."Pos++ExtPub+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Pers" ON "Pos"."EntResp+PtrCustodianID" = "Pers"."Pers+id"   

LEFT JOIN "Com" as "Branch" ON "Pos"."ComS+PtrLastConnectedEntityID" = "Branch"."Com+id"   

LEFT JOIN "Dr++DrE++ML" as "Unternehmensbereich" ON "Unternehmensbereich"."Dr++DrE+id" = "Pos"."DescFct+Function"  AND "Unternehmensbereich"."Dr+id" = 10  AND "Unternehmensbereich"."Dr++DrE++ML+id" = "DynJPl"."PublicationLangID"    

LEFT JOIN "Dr++DrE++ML" as "Beschaeftigungsart" ON "Beschaeftigungsart"."Dr++DrE+id" = "Pos"."DescEmp+EmploymentType"  AND "Beschaeftigungsart"."Dr+id" = 13  AND "Beschaeftigungsart"."Dr++DrE++ML+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Dr++DrE++ML" as "Position" ON "Position"."Dr++DrE+id" = "Pos"."DescPos+Position"   AND "Position"."Dr+id" = 15  AND "Position"."Dr++DrE++ML+id" = "DynJPl"."PublicationLangID"   

LEFT JOIN "Dr++DrE++ML" as "Befristung" ON "Befristung"."Dr++DrE+id" = "Pos"."DescEmp+ContractType"  AND "Befristung"."Dr+id" = 27  AND "Befristung"."Dr++DrE++ML+id" = "DynJPl"."PublicationLangID"     

WHERE "Pos"."EntitySubtype" = 'PositionType_Vacancy' AND "ExtPubOnJpl"."IsPublished" is not null   

ORDER BY "Pos"."Pos+id", "ExtPubCommSet1"."Pos++ExtPub++CommSet+id"