C11/System/AUTOLINE/IQD/Belege/Belege_Autoline_Service_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",
	   T7."WIPNumber" as "Wipnumber_So_Header_Records",
	   CASE WHEN (T8."OperatorName" IS NULL) THEN ('n.N.') ELSE (T8."OperatorName") END as "Serviceberater",
	   T6."KONTO_BEZEICHNUNG" as "Konto Bezeichnung",
	   CASE WHEN (T6."KONTO_BEZEICHNUNG" LIKE '%Gar%') THEN ('Garantie') WHEN (T6."KONTO_BEZEICHNUNG" LIKE '%Int%') THEN ('Intern') ELSE ('Extern') END as "Umsatzart",
	   T1."CustomerReference" as "Kunde_FIBU",
	   CASE WHEN (((T1."Suffix" <> '') and (T1."AccountsModule" = 'SL')) and (T7."AccountNo" IS NOT NULL)) THEN (T7."AccountNo" + ' - ' + T7."CustomerName") WHEN (((T1."Suffix" LIKE '000%') and (T1."AccountsModule" = 'SL')) and (T7."AccountNo" IS NULL)) THEN ('Buchungen ohne Auftrag') ELSE ('Buchungen ohne Auftrag') END as "Kunde",
	   T9."AccountCode" as "Accountcode_SL_Sales_Ledger_Acct_FIBU",
	   T9."CustomerSName" as "Customersname_SL_Sales_Ledger_Acct_FIBU",
	   CASE WHEN ((T1."Suffix" = '') and (T9."AccountCode" IS NULL)) THEN ('Buchungen ohne Auftrag') WHEN (((T1."Suffix" <> '') and (T9."AccountCode" IS NULL)) and (T1."AccountsModule" <> 'SL')) THEN ('Buchungen ohne Auftrag') ELSE (T9."AccountCode" + ' - ' + T9."CustomerSName") END as "Kunde_Rg",
	   CASE WHEN (T1."Suffix" <> '') THEN (T1."Client_DB" + '_' + T1."Suffix") ELSE ('Buchungen ohne Auftrag' + '_' + (rtrim((((T1."DocumentNumber")))))) END as "Order_Number_eindeutig",
	   CASE WHEN ((T1."Suffix" = '') and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" <> '') and (T7."ChassisNumber" IS NULL)) and (T1."AccountsModule" <> 'SL')) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" LIKE '000%') and (T1."AccountsModule" = 'SL')) and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') ELSE (T7."ChassisNumber") END as "Chassisnumber",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."PostingDate")) <= 100) THEN ((CASE WHEN (T1."Suffix" <> '') THEN (T1."Client_DB" + '_' + T1."Suffix") ELSE ('Buchungen ohne Auftrag' + '_' + (rtrim((((T1."DocumentNumber")))))) END) + ' - ' + (CASE WHEN (((T1."Suffix" <> '') and (T1."AccountsModule" = 'SL')) and (T7."AccountNo" IS NOT NULL)) THEN (T7."AccountNo" + ' - ' + T7."CustomerName") WHEN (((T1."Suffix" LIKE '000%') and (T1."AccountsModule" = 'SL')) and (T7."AccountNo" IS NULL)) THEN ('Buchungen ohne Auftrag') ELSE ('Buchungen ohne Auftrag') END) + ' - ' + (CASE WHEN ((T1."Suffix" = '') and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" <> '') and (T7."ChassisNumber" IS NULL)) and (T1."AccountsModule" <> 'SL')) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" LIKE '000%') and (T1."AccountsModule" = 'SL')) and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') ELSE (T7."ChassisNumber") END)) ELSE ('Aufträge älter 100 Tage') END as "Order_Desc_100",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."PostingDate")) <= 100) THEN ((rtrim((((T1."DocumentNumber"))))) + ' - ' + (CASE WHEN ((T1."Suffix" = '') and (T9."AccountCode" IS NULL)) THEN ('Buchungen ohne Auftrag') WHEN (((T1."Suffix" <> '') and (T9."AccountCode" IS NULL)) and (T1."AccountsModule" <> 'SL')) THEN ('Buchungen ohne Auftrag') ELSE (T9."AccountCode" + ' - ' + T9."CustomerSName") END) + ' - ' + (CASE WHEN ((T1."Suffix" = '') and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" <> '') and (T7."ChassisNumber" IS NULL)) and (T1."AccountsModule" <> 'SL')) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" LIKE '000%') and (T1."AccountsModule" = 'SL')) and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') ELSE (T7."ChassisNumber") END)) ELSE ('Rechnungen älter 100 Tage') END as "Invoice_Desc_100",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."PostingDate")) <= 30) THEN ((CASE WHEN (T1."Suffix" <> '') THEN (T1."Client_DB" + '_' + T1."Suffix") ELSE ('Buchungen ohne Auftrag' + '_' + (rtrim((((T1."DocumentNumber")))))) END) + ' - ' + (CASE WHEN (((T1."Suffix" <> '') and (T1."AccountsModule" = 'SL')) and (T7."AccountNo" IS NOT NULL)) THEN (T7."AccountNo" + ' - ' + T7."CustomerName") WHEN (((T1."Suffix" LIKE '000%') and (T1."AccountsModule" = 'SL')) and (T7."AccountNo" IS NULL)) THEN ('Buchungen ohne Auftrag') ELSE ('Buchungen ohne Auftrag') END)) ELSE ('Aufträge älter 30 Tage') END as "Order_Desc_30",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."PostingDate")) <= 30) THEN ((rtrim((((T1."DocumentNumber"))))) + ' - ' + (CASE WHEN (T8."OperatorName" IS NULL) THEN ('n.N.') ELSE (T8."OperatorName") END) + ' - ' + (CASE WHEN ((T1."Suffix" = '') and (T9."AccountCode" IS NULL)) THEN ('Buchungen ohne Auftrag') WHEN (((T1."Suffix" <> '') and (T9."AccountCode" IS NULL)) and (T1."AccountsModule" <> 'SL')) THEN ('Buchungen ohne Auftrag') ELSE (T9."AccountCode" + ' - ' + T9."CustomerSName") END) + ' - ' + (CASE WHEN ((T1."Suffix" = '') and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" <> '') and (T7."ChassisNumber" IS NULL)) and (T1."AccountsModule" <> 'SL')) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" LIKE '000%') and (T1."AccountsModule" = 'SL')) and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') ELSE (T7."ChassisNumber") END)) ELSE ('Rechnungen älter 30 Tage') END as "Invoice_Desc_30",
	   T10."Franchise" as "Franchise_MK_Vehiclerecords",
	   CASE WHEN (T10."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T10."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T10."Franchise" = 'Z') THEN ('Fremd') WHEN (T10."Franchise" = 'S') THEN ('Smart') WHEN (T10."Franchise" = 'F') THEN ('Fremd TRAPO') ELSE null END as "Fabrikat",
	   T10."Description" as "Description_MK_Vehiclerecords",
	   (left((ucase(T10."Description")),4)) as "Model",
	   (CASE WHEN ((T1."Suffix" = '') and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" <> '') and (T7."ChassisNumber" IS NULL)) and (T1."AccountsModule" <> 'SL')) THEN ('ohne Chassis No.') WHEN (((T1."Suffix" LIKE '000%') and (T1."AccountsModule" = 'SL')) and (T7."ChassisNumber" IS NULL)) THEN ('ohne Chassis No.') ELSE (T7."ChassisNumber") END) as "Fahrzeug",
	   T3."ShortName" + ' - ' + T3."Description001" as "Kostenstelle",
	   T7."Department" as "Department_SO_Header_Records",
	   CASE WHEN (T7."Department" = 'P') THEN ('Teile') ELSE ('Service') END as "Auftragsart",
	   T6."EBENE2" as "Zuordnung_Produktbuchungsgruppe",
	   (rtrim((((T1."ExpenseCode"))))) + ' - ' + T6."KONTO_BEZEICHNUNG" as "Produktbuchungsgruppe",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."PostingDate")) <= 30) THEN (((rtrim((((T1."ExpenseCode"))))) + ' - ' + T6."KONTO_BEZEICHNUNG")) ELSE ('Rechnungen älter 30 Tage') END as "Auftragsposition",
	   T1."DocumentNumber" as "Invoicenumber_Positionen",
	   'Rechnung' as "Rechnung_Gutschrift",
	   '' as "FZG-Altersstaffel",
	   '' as "Repair_Group_Desc",
	   'VK > EK' as "DB1_><_EK",
	   '' as "Parts_Focus_Group",
	   '' as "Parts_Make_Desc",
	   '' as "Parts_Group_Desc",
	   T9."Postcode" as "Postcode_Slsalesledgeraccount",
	   (left(T9."Postcode",1)) as "PLZ_1_Stelle",
	   (left(T9."Postcode",2)) as "PLZ_2_Stelle",
	   (left(T9."Postcode",3)) as "PLZ_3_Stelle",
	   (left(T9."Postcode",4)) as "PLZ_4_Stelle",
	   T9."Postcode" as "PLZ",
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Lohnerlös Werkstatt'))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Lohn",
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlös Fremdleistung'))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Fremdl.",
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlös Ersatzteile'))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Teile",
	   CASE WHEN ((T6."KONTO_1" = 'Einsatzwerte') and (T6."EBENE2" IN ('Erlös Ersatzteile'))) THEN (T1."BaseValue") ELSE (0) END as "EW Teile",
	   CASE WHEN (T6."KONTO_2" = 'NL Lohn') THEN (T1."BaseValue" * -1) ELSE (0) END as "NL Lohn",
	   CASE WHEN (T6."KONTO_2" = 'NL Teile') THEN (T1."BaseValue" * -1) ELSE (0) END as "NL Teile",
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Divers Werkstatt'))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Sonst.",
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlös MB-Rent'))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Mietw.",
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlös div. (Ersatzwagen)'))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Ersatzw.",
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlöse Reifen'))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Reifen",
	   CASE WHEN ((T6."KONTO_1" = 'Einsatzwerte') and (T6."EBENE2" IN ('Erlöse Reifen'))) THEN (T1."BaseValue") ELSE (0) END as "EW Reifen",
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlöse Nebenbetriebe (Treibstoff)'))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Treibst.",
	   CASE WHEN (((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlöse Nebenbetriebe (Schmierstoffe/Reifenlagerung)'))) and (not T1."ExpenseCode" IN (374011))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Schmierst.",
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T1."ExpenseCode" IN (374011))) THEN (T1."BaseValue" * -1) ELSE (0) END as "Rädereinl.",
	   T7."SaleType" as "Kundenart"
from "ims"."Kontenrahmen_Service_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"."SO_HeaderRecords" T7 on (T1."Suffix" = (((T7."WIPNumber")))) and (T1."Client_DB" = T7."Client_DB")) left outer join "AUTOLINE"."import"."SO_User_Parameter" T8 on ((convert(float, T7."BookedInOperator")) = T8."OperatorNumber") and (T7."Client_DB" = T8."Client_DB")) left outer join "AUTOLINE"."import"."SL_SalesLedgerAccounts" T9 on (T1."SLPLAccountCode" = T9."AccountCode") and (T1."Client_DB" = T9."Client_DB")) left outer join "AUTOLINE"."import"."MK_VehicleRecords" T10 on T10."VehicleNumber" = T7."MagicMKVehicle")
where (T1."ExpenseCode" = (cinteger(nconvert(T6."KONTO_NR"),0)))
and (((T6."KONTO_1" = 'Umsatzerlöse') or ((T6."KONTO_1" = 'Einsatzwerte') and (T6."EBENE2" IN ('Erlös Ersatzteile','Erlöse Reifen')))))
-- order by "Documentnumber" asc,"Suffix" asc