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

select T1."CostCentre" as "Costcentre",
	   T1."ExpenseCode" as "Expensecode",
	   T1."Suffix" as "Suffix",
	   T1."AccountsModule" as "Accountsmodule",
	   T1."DaybookNumber" as "Daybooknumber",
	   T1."ItemNumber" as "Itemnumber",
	   T1."PostingDate" as "Postingdate",
	   T1."AccountsCompany" as "Accountscompany",
	   T1."SLPLAccountCode" as "Slplaccountcode",
	   T1."Period" as "Period",
	   T1."JournalReference" as "Journalreference",
	   T1."Narrative" as "Narrative",
	   T1."Year" as "Year",
	   T1."BaseValue" as "Basevalue",
	   T1."CurrencyValue" as "Currencyvalue",
	   T1."SequenceNumber" as "Sequencenumber",
	   T1."Reconciled" as "Reconciled",
	   T1."JournalType" as "Journaltype",
	   T1."TransferBatch" as "Transferbatch",
	   T1."GroupDrValue" as "Groupdrvalue",
	   T1."GroupCrValue" as "Groupcrvalue",
	   T1."CurrencyLogSerial" as "Currencylogserial",
	   T1."SourceCompany" as "Sourcecompany",
	   T1."StatementDate" as "Statementdate",
	   T1."SourceModule" as "Sourcemodule",
	   T1."VATCode" as "Vatcode",
	   T1."LegalAnalytic" as "Legalanalytic",
	   T1."LatinSequence" as "Latinsequence",
	   T1."LatinSequenceLine" as "Latinsequenceline",
	   T1."ReconciledDate" as "Reconcileddate",
	   T1."ReconciledTime" as "Reconciledtime",
	   T1."ReconciledUser" as "Reconcileduser",
	   T1."ChequeValueDate" as "Chequevaluedate",
	   T1."DocumentNumber" as "Documentnumber",
	   T1."JournalClass" as "Journalclass",
	   T1."JournalSeqType" as "Journalseqtype",
	   T1."VATGroup" as "Vatgroup",
	   T1."CustomerReference" as "Customerreference",
	   T1."ReconciledReference" as "Reconciledreference",
	   T1."CashFlowGroup" as "Cashflowgroup",
	   T1."CashFlowCode" as "Cashflowcode",
	   T1."ConsSourceCompany" as "Conssourcecompany",
	   T1."ConsSourceDaybook" as "Conssourcedaybook",
	   T1."BranchCode" as "Branchcode",
	   T1."DocumentDate" as "Documentdate",
	   T1."Client_DB" as "Client Db",
	   T1."AccountCode" as "Accountcode",
	   T1."PostingDate" 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",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."PostingDate")) <= 180) THEN ((rtrim((((T1."DocumentNumber"))))) + ' - ' + T1."JournalClass" + ' - ' + T1."CustomerReference") ELSE ('Belege älter 180 Tage') END as "Text",
	   T4."CostCentreGroup" + ' - ' + T4."Description002" as "Kostenstelle",
	   (rtrim((((T3."CostCentre"))))) + ' - ' + T3."Description002" as "KST_Detail"
from (((("AUTOLINE"."import"."NL_NominalTransactionFile" T1 left outer join "AUTOLINE"."import"."NL_NominalExpenseCodes" T2 on (T2."ExpenseCode" = T1."ExpenseCode") and (T2."Client_DB" = T1."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%'))
-- order by "Suffix" asc