C11/System/AUTOLINE/IQD/Belege/Belege_Autoline_Verkauf_Erfolgsrechnung_operativ.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")) <= 90) THEN ((rtrim((((T1."DocumentNumber"))))) + ' - ' + T1."JournalClass" + ' - ' + T1."CustomerReference") ELSE ('Belege älter 90 Tage') END as "Text",
	   T4."CostCentreGroup" + ' - ' + T4."Description002" as "Kostenstelle_alt",
	   (rtrim((((T3."CostCentre"))))) + ' - ' + T3."Description002" as "KST_Detail",
	   T6."KONTO_1" as "Konto 1",
	   T6."EBENE1" as "Ebene1",
	   T6."EBENE2" as "Ebene2",
	   T6."EBENE3" as "Ebene3",
	   T6."EBENE4" as "Ebene4",
	   T6."KONTO_2" as "Konto 2",
	   T1."PostingDate" as "Invoice Date",
	   T1."Client_DB" as "Hauptbetrieb_ID",
	   '' as "Hauptbetrieb_Name",
	   'Serviceberater' as "Zuordnung_Funktion",
	   T6."KONTO_BEZEICHNUNG" as "Konto Bezeichnung",
	   T1."CustomerReference" as "Kunde_FIBU",
	   T7."AccountCode" as "Accountcode_SL_Sales_Ledger_Acct_FIBU",
	   T7."CustomerSName" as "Customersname_SL_Sales_Ledger_Acct_FIBU",
	   CASE WHEN ((T1."Suffix" = '') and (T7."AccountCode" IS NULL)) THEN ('Buchungen ohne Auftrag') WHEN (((T1."Suffix" <> '') and (T7."AccountCode" IS NULL)) and (T1."AccountsModule" <> 'SL')) THEN ('Buchungen ohne Auftrag') ELSE (T7."AccountCode" + ' - ' + T7."CustomerSName") END as "Kunde_Rg",
	   T8."Franchise" as "Franchise_MK_Vehiclerecords",
	   CASE WHEN (T8."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T8."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T8."Franchise" = 'Z') THEN ('Fremd') WHEN (T8."Franchise" = 'S') THEN ('Smart') WHEN (T8."Franchise" = 'F') THEN ('Fremd TRAPO') ELSE null END as "Fabrikat",
	   T8."Description" as "Description_MK_Vehiclerecords",
	   (left((ucase(T8."Description")),4)) as "Model",
	   T3."ShortName" + ' - ' + T3."Description001" as "Kostenstelle",
	   T7."Postcode" as "Postcode_Slsalesledgeraccount",
	   (left(T7."Postcode",1)) as "PLZ_1_Stelle",
	   (left(T7."Postcode",2)) as "PLZ_2_Stelle",
	   (left(T7."Postcode",3)) as "PLZ_3_Stelle",
	   (left(T7."Postcode",4)) as "PLZ_4_Stelle",
	   T7."Postcode" as "PLZ"
from "ims"."Kontenrahmen_Verkauf_Erfolgsrechnung" T6,
	((((((("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") left outer join "AUTOLINE"."import"."SL_SalesLedgerAccounts" T7 on (T1."SLPLAccountCode" = T7."AccountCode") and (T1."Client_DB" = T7."Client_DB")) left outer join "AUTOLINE"."import"."SO_HeaderRecords" T9 on (T1."Suffix" = (((T9."WIPNumber")))) and (T1."Client_DB" = T9."Client_DB")) left outer join "AUTOLINE"."import"."MK_VehicleRecords" T8 on T8."VehicleNumber" = T9."MagicMKVehicle")
where (T1."ExpenseCode" = (cinteger(nconvert(T6."KONTO_NR"),0)))
and (T1."PostingDate" >= convert(datetime, '2024-06-01 00:00:00.000'))
-- order by "Documentnumber" asc,"Suffix" asc