C11/System/AUTOLINE/IQD/Belege/Belege_Autoline_History.sql

select T1."AccountCode" as "Accountcode",
	   T1."CostCentre" as "Costcentre",
	   T1."ExpenseCode" as "Expensecode",
	   T1."NLPeriod" as "Nlperiod",
	   T1."NLYear" as "Nlyear",
	   T1."CurrencyType" as "Currencytype",
	   T1."BroughtForward" as "Broughtforward",
	   T1."CurrentPostings" as "Currentpostings",
	   T1."FuturePostings" as "Futurepostings",
	   T1."AdjustmentFlag" as "Adjustmentflag",
	   T1."Adjustment" as "Adjustment",
	   T1."GroupDrBroughtFwd" as "Groupdrbroughtfwd",
	   T1."GroupCrBroughtFwd" as "Groupcrbroughtfwd",
	   T1."GroupDrCurrent" as "Groupdrcurrent",
	   T1."GroupCrCurrent" as "Groupcrcurrent",
	   T1."GroupDrFuture" as "Groupdrfuture",
	   T1."GroupCrFuture" as "Groupcrfuture",
	   T1."GroupDrAdjustments" as "Groupdradjustments",
	   T1."GroupCrAdjustments" as "Groupcradjustments",
	   T1."Client_DB" as "Client Db",
	   ((cinteger(nconvert(((rtrim((((T1."NLYear"))))) + (convert(varchar(50), round(T1."NLPeriod",0,0),2)) + '28')),0))) as "Bookkeep Date",
	   T1."Client_DB" as "Rechtseinheit_ID",
	   T2."Description002" as "Description002_Expense_Code_Kto_Bez",
	   T2."AccountType" as "Accounttype_Expense_Code",
	   T3."CostCentre" as "Costcentre_Department",
	   T3."ShortName" as "Shortname_Department",
	   T3."Description001" as "Description001_Department",
	   T3."Description002" as "Description002_Department",
	   T3."Description003" as "Description003_Department",
	   T3."Description004" as "Description004_Department",
	   T3."CostCentreGroup" as "Costcentregroup_Department",
	   T4."CostCentreGroup" as "Costcentregroup_Costcentergr",
	   T4."Description001" as "Description001_Costcentergr",
	   T4."Description002" as "Description002_Costcentergr",
	   T4."Description003" as "Description003_Costcentergr",
	   T4."Description004" as "Description004_Costcentergr",
	   T5."LocationCode" as "Locationcode_GB_company",
	   T5."Description" as "Description_GB_company",
	   T5."LocationName" as "Locationname_GB_company",
	   '' as "Rechtseinheit_Name",
	   T5."LocationCode" as "Standort_ID",
	   T5."LocationCode" + ' - ' + T5."Description" as "Standort_Name",
	   T1."ExpenseCode" as "Acct Nr",
	   '' as "Text",
	   T4."CostCentreGroup" + ' - ' + T4."Description002" as "Kostenstelle",
	   (rtrim((((T3."CostCentre"))))) + ' - ' + T3."Description002" as "KST_Detail",
	   (rtrim((((T1."NLYear"))))) + (convert(varchar(50), round(T1."NLPeriod",0,0),2)) + '28' as "Datum_Zeichen_alt",
	   T1."CurrentPostings" as "Basevalue",
	   (cinteger(nconvert(((rtrim((((T1."NLYear"))))) + (convert(varchar(50), round(T1."NLPeriod",0,0),2)) + '28')),0)) as "Datum_Zahl"
from (((("AUTOLINE"."import"."NL_NominalAccountHistoryFile" T1 left outer join "AUTOLINE"."import"."NL_NominalExpenseCodes" T2 on (T1."ExpenseCode" = T2."ExpenseCode") and (T1."Client_DB" = T2."Client_DB")) left outer join "AUTOLINE"."import"."NL_NominalDepartmentCodes" T3 on (T1."CostCentre" = T3."CostCentre") and (T1."Client_DB" = T3."Client_DB")) left outer join "AUTOLINE"."import"."NL_DepartmentCodeGroups" T4 on (T3."CostCentreGroup" = T4."CostCentreGroup") and (T3."Client_DB" = T4."Client_DB")) left outer join "AUTOLINE"."import"."GB_CompanyLocations" T5 on (left((((T1."CostCentre"))),2)) = T5."LocationCode")
where (((T2."AccountType" = 'P') and (not T3."CostCentreGroup" LIKE '9%')) and (T1."NLYear" > 2018))