C11/System/AUTOLINE/IQD/Belege/Belege_Autoline_Service_Erfolgsrechnung_operativ.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_AUTOLINE
DATASOURCENAME,C:\GlobalCube\System\Autoline\IQD\belege\Belege_Autoline_Service_Erfolgsrechnung_operativ.imr
TITLE,Belege_Autoline_Service_Erfolgsrechnung_operativ.imr
BEGIN SQL
select T1."CostCentre" as c1,
	   T1."ExpenseCode" as c2,
	   T1."Suffix" as c3,
	   T1."AccountsModule" as c4,
	   T1."DaybookNumber" as c5,
	   T1."ItemNumber" as c6,
	   T1."PostingDate" as c7,
	   T1."AccountsCompany" as c8,
	   T1."SLPLAccountCode" as c9,
	   T1."Period" as c10,
	   T1."JournalReference" as c11,
	   T1."Narrative" as c12,
	   T1."Year" as c13,
	   T1."BaseValue" as c14,
	   T1."CurrencyValue" as c15,
	   T1."SequenceNumber" as c16,
	   T1."Reconciled" as c17,
	   T1."JournalType" as c18,
	   T1."TransferBatch" as c19,
	   T1."GroupDrValue" as c20,
	   T1."GroupCrValue" as c21,
	   T1."CurrencyLogSerial" as c22,
	   T1."SourceCompany" as c23,
	   T1."StatementDate" as c24,
	   T1."SourceModule" as c25,
	   T1."VATCode" as c26,
	   T1."LegalAnalytic" as c27,
	   T1."LatinSequence" as c28,
	   T1."LatinSequenceLine" as c29,
	   T1."ReconciledDate" as c30,
	   T1."ReconciledTime" as c31,
	   T1."ReconciledUser" as c32,
	   T1."ChequeValueDate" as c33,
	   T1."DocumentNumber" as c34,
	   T1."JournalClass" as c35,
	   T1."JournalSeqType" as c36,
	   T1."VATGroup" as c37,
	   T1."CustomerReference" as c38,
	   T1."ReconciledReference" as c39,
	   T1."CashFlowGroup" as c40,
	   T1."CashFlowCode" as c41,
	   T1."ConsSourceCompany" as c42,
	   T1."ConsSourceDaybook" as c43,
	   T1."BranchCode" as c44,
	   T1."DocumentDate" as c45,
	   T1."Client_DB" as c46,
	   T1."AccountCode" as c47,
	   T1."PostingDate" as c48,
	   T1."Client_DB" as c49,
	   T2."Description002" as c50,
	   T2."AccountType" as c51,
	   T3."CostCentre" as c52,
	   T3."ShortName" as c53,
	   T3."Description001" as c54,
	   T3."Description002" as c55,
	   T3."Description003" as c56,
	   T3."Description004" as c57,
	   T3."CostCentreGroup" as c58,
	   T4."CostCentreGroup" as c59,
	   T4."Description001" as c60,
	   T4."Description002" as c61,
	   T4."Description003" as c62,
	   T4."Description004" as c63,
	   T5."LocationCode" as c64,
	   T5."Description" as c65,
	   T5."LocationName" as c66,
	   '' as c67,
	   T5."LocationCode" as c68,
	   T5."LocationCode" || ' - ' || T5."Description" as c69,
	   T1."ExpenseCode" as c70,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."PostingDate")) <= 90) THEN ((rtrim((cast_numberToString(cast_integer(T1."DocumentNumber"))))) || ' - ' || T1."JournalClass" || ' - ' || T1."CustomerReference") ELSE ('Belege älter 90 Tage') END as c71,
	   T4."CostCentreGroup" || ' - ' || T4."Description002" as c72,
	   (rtrim((cast_numberToString(cast_integer(T3."CostCentre"))))) || ' - ' || T3."Description002" as c73,
	   T6."KONTO_1" as c74,
	   T6."EBENE1" as c75,
	   T6."EBENE2" as c76,
	   T6."EBENE3" as c77,
	   T6."EBENE4" as c78,
	   T6."KONTO_2" as c79,
	   T1."PostingDate" as c80,
	   T1."Client_DB" as c81,
	   '' as c82,
	   'Serviceberater' as c83,
	   T7."WIPNumber" as c84,
	   CASE WHEN (T8."OperatorName" IS NULL) THEN ('n.N.') ELSE (T8."OperatorName") END as c85,
	   T6."KONTO_BEZEICHNUNG" as c86,
	   CASE WHEN (T6."KONTO_BEZEICHNUNG" LIKE '%Gar%') THEN ('Garantie') WHEN (T6."KONTO_BEZEICHNUNG" LIKE '%Int%') THEN ('Intern') ELSE ('Extern') END as c87,
	   T1."CustomerReference" as c88,
	   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 c89,
	   T9."AccountCode" as c90,
	   T9."CustomerSName" as c91,
	   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 c92,
	   CASE WHEN (T1."Suffix" <> '') THEN (T1."Client_DB" || '_' || T1."Suffix") ELSE ('Buchungen ohne Auftrag' || '_' || (rtrim((cast_numberToString(cast_integer(T1."DocumentNumber")))))) END as c93,
	   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 c94,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."PostingDate")) <= 100) THEN ((CASE WHEN (T1."Suffix" <> '') THEN (T1."Client_DB" || '_' || T1."Suffix") ELSE ('Buchungen ohne Auftrag' || '_' || (rtrim((cast_numberToString(cast_integer(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 c95,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."PostingDate")) <= 100) THEN ((rtrim((cast_numberToString(cast_integer(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 c96,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."PostingDate")) <= 30) THEN ((CASE WHEN (T1."Suffix" <> '') THEN (T1."Client_DB" || '_' || T1."Suffix") ELSE ('Buchungen ohne Auftrag' || '_' || (rtrim((cast_numberToString(cast_integer(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 c97,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."PostingDate")) <= 30) THEN ((rtrim((cast_numberToString(cast_integer(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 c98,
	   T10."Franchise" as c99,
	   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 c100,
	   T10."Description" as c101,
	   (od_left((ucase(T10."Description")),4)) as c102,
	   (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 c103,
	   T3."ShortName" || ' - ' || T3."Description001" as c104,
	   T7."Department" as c105,
	   CASE WHEN (T7."Department" = 'P') THEN ('Teile') ELSE ('Service') END as c106,
	   T6."EBENE2" as c107,
	   (rtrim((cast_numberToString(cast_integer(T1."ExpenseCode"))))) || ' - ' || T6."KONTO_BEZEICHNUNG" as c108,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."PostingDate")) <= 30) THEN (((rtrim((cast_numberToString(cast_integer(T1."ExpenseCode"))))) || ' - ' || T6."KONTO_BEZEICHNUNG")) ELSE ('Rechnungen älter 30 Tage') END as c109,
	   T1."DocumentNumber" as c110,
	   'Rechnung' as c111,
	   '' as c112,
	   '' as c113,
	   'VK > EK' as c114,
	   '' as c115,
	   '' as c116,
	   '' as c117,
	   T9."Postcode" as c118,
	   (od_left(T9."Postcode",1)) as c119,
	   (od_left(T9."Postcode",2)) as c120,
	   (od_left(T9."Postcode",3)) as c121,
	   (od_left(T9."Postcode",4)) as c122,
	   T9."Postcode" as c123,
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Lohnerlös Werkstatt'))) THEN (T1."BaseValue" * -1) ELSE (0) END as c124,
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlös Fremdleistung'))) THEN (T1."BaseValue" * -1) ELSE (0) END as c125,
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlös Ersatzteile'))) THEN (T1."BaseValue" * -1) ELSE (0) END as c126,
	   CASE WHEN ((T6."KONTO_1" = 'Einsatzwerte') and (T6."EBENE2" IN ('Erlös Ersatzteile'))) THEN (T1."BaseValue") ELSE (0) END as c127,
	   CASE WHEN (T6."KONTO_2" = 'NL Lohn') THEN (T1."BaseValue" * -1) ELSE (0) END as c128,
	   CASE WHEN (T6."KONTO_2" = 'NL Teile') THEN (T1."BaseValue" * -1) ELSE (0) END as c129,
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Divers Werkstatt'))) THEN (T1."BaseValue" * -1) ELSE (0) END as c130,
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlös MB-Rent'))) THEN (T1."BaseValue" * -1) ELSE (0) END as c131,
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlös div. (Ersatzwagen)'))) THEN (T1."BaseValue" * -1) ELSE (0) END as c132,
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlöse Reifen'))) THEN (T1."BaseValue" * -1) ELSE (0) END as c133,
	   CASE WHEN ((T6."KONTO_1" = 'Einsatzwerte') and (T6."EBENE2" IN ('Erlöse Reifen'))) THEN (T1."BaseValue") ELSE (0) END as c134,
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T6."EBENE2" IN ('Erlöse Nebenbetriebe (Treibstoff)'))) THEN (T1."BaseValue" * -1) ELSE (0) END as c135,
	   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 c136,
	   CASE WHEN ((T6."KONTO_1" = 'Umsatzerlöse') and (T1."ExpenseCode" IN (374011))) THEN (T1."BaseValue" * -1) ELSE (0) END as c137,
	   T7."SaleType" as c138
from QSS."C:\GlobalCube\System\AUTOLINE\IQD\Belege\Kontenrahmen_Service_Erfolgsrechnung.ims" 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 (od_left((cast_numberToString(cast_integer(T1."CostCentre"))),2)) = T5."LocationCode") left outer join "AUTOLINE"."import"."SO_HeaderRecords" T7 on (T1."Suffix" = (cast_numberToString(cast_integer(T7."WIPNumber")))) and (T1."Client_DB" = T7."Client_DB")) left outer join "AUTOLINE"."import"."SO_User_Parameter" T8 on ((cast_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 c34 asc,c3 asc

END SQL
COLUMN,0,Costcentre
COLUMN,1,Expensecode
COLUMN,2,Suffix
COLUMN,3,Accountsmodule
COLUMN,4,Daybooknumber
COLUMN,5,Itemnumber
COLUMN,6,Postingdate
COLUMN,7,Accountscompany
COLUMN,8,Slplaccountcode
COLUMN,9,Period
COLUMN,10,Journalreference
COLUMN,11,Narrative
COLUMN,12,Year
COLUMN,13,Basevalue
COLUMN,14,Currencyvalue
COLUMN,15,Sequencenumber
COLUMN,16,Reconciled
COLUMN,17,Journaltype
COLUMN,18,Transferbatch
COLUMN,19,Groupdrvalue
COLUMN,20,Groupcrvalue
COLUMN,21,Currencylogserial
COLUMN,22,Sourcecompany
COLUMN,23,Statementdate
COLUMN,24,Sourcemodule
COLUMN,25,Vatcode
COLUMN,26,Legalanalytic
COLUMN,27,Latinsequence
COLUMN,28,Latinsequenceline
COLUMN,29,Reconcileddate
COLUMN,30,Reconciledtime
COLUMN,31,Reconcileduser
COLUMN,32,Chequevaluedate
COLUMN,33,Documentnumber
COLUMN,34,Journalclass
COLUMN,35,Journalseqtype
COLUMN,36,Vatgroup
COLUMN,37,Customerreference
COLUMN,38,Reconciledreference
COLUMN,39,Cashflowgroup
COLUMN,40,Cashflowcode
COLUMN,41,Conssourcecompany
COLUMN,42,Conssourcedaybook
COLUMN,43,Branchcode
COLUMN,44,Documentdate
COLUMN,45,Client Db
COLUMN,46,Accountcode
COLUMN,47,Bookkeep Date
COLUMN,48,Rechtseinheit_ID
COLUMN,49,Description002_Expense_Code_Kto_Bez
COLUMN,50,Accounttype_Expense_Code
COLUMN,51,Costcentre_Department
COLUMN,52,Shortname_Department
COLUMN,53,Description001_Department
COLUMN,54,Description002_Department
COLUMN,55,Description003_Department
COLUMN,56,Description004_Department
COLUMN,57,Costcentregroup_Department
COLUMN,58,Costcentregroup_Costcentergr
COLUMN,59,Description001_Costcentergr
COLUMN,60,Description002_Costcentergr
COLUMN,61,Description003_Costcentergr
COLUMN,62,Description004_Costcentergr
COLUMN,63,Locationcode_GB_company
COLUMN,64,Description_GB_company
COLUMN,65,Locationname_GB_company
COLUMN,66,Rechtseinheit_Name
COLUMN,67,Standort_ID
COLUMN,68,Standort_Name
COLUMN,69,Acct Nr
COLUMN,70,Text
COLUMN,71,Kostenstelle_alt
COLUMN,72,KST_Detail
COLUMN,73,Konto 1
COLUMN,74,Ebene1
COLUMN,75,Ebene2
COLUMN,76,Ebene3
COLUMN,77,Ebene4
COLUMN,78,Konto 2
COLUMN,79,Invoice Date
COLUMN,80,Hauptbetrieb_ID
COLUMN,81,Hauptbetrieb_Name
COLUMN,82,Zuordnung_Funktion
COLUMN,83,Wipnumber_So_Header_Records
COLUMN,84,Serviceberater
COLUMN,85,Konto Bezeichnung
COLUMN,86,Umsatzart
COLUMN,87,Kunde_FIBU
COLUMN,88,Kunde
COLUMN,89,Accountcode_SL_Sales_Ledger_Acct_FIBU
COLUMN,90,Customersname_SL_Sales_Ledger_Acct_FIBU
COLUMN,91,Kunde_Rg
COLUMN,92,Order_Number_eindeutig
COLUMN,93,Chassisnumber
COLUMN,94,Order_Desc_100
COLUMN,95,Invoice_Desc_100
COLUMN,96,Order_Desc_30
COLUMN,97,Invoice_Desc_30
COLUMN,98,Franchise_MK_Vehiclerecords
COLUMN,99,Fabrikat
COLUMN,100,Description_MK_Vehiclerecords
COLUMN,101,Model
COLUMN,102,Fahrzeug
COLUMN,103,Kostenstelle
COLUMN,104,Department_SO_Header_Records
COLUMN,105,Auftragsart
COLUMN,106,Zuordnung_Produktbuchungsgruppe
COLUMN,107,Produktbuchungsgruppe
COLUMN,108,Auftragsposition
COLUMN,109,Invoicenumber_Positionen
COLUMN,110,Rechnung_Gutschrift
COLUMN,111,FZG-Altersstaffel
COLUMN,112,Repair_Group_Desc
COLUMN,113,DB1_><_EK
COLUMN,114,Parts_Focus_Group
COLUMN,115,Parts_Make_Desc
COLUMN,116,Parts_Group_Desc
COLUMN,117,Postcode_Slsalesledgeraccount
COLUMN,118,PLZ_1_Stelle
COLUMN,119,PLZ_2_Stelle
COLUMN,120,PLZ_3_Stelle
COLUMN,121,PLZ_4_Stelle
COLUMN,122,PLZ
COLUMN,123,Lohn
COLUMN,124,Fremdl.
COLUMN,125,Teile
COLUMN,126,EW Teile
COLUMN,127,NL Lohn
COLUMN,128,NL Teile
COLUMN,129,Sonst.
COLUMN,130,Mietw.
COLUMN,131,Ersatzw.
COLUMN,132,Reifen
COLUMN,133,EW Reifen
COLUMN,134,Treibst.
COLUMN,135,Schmierst.
COLUMN,136,Rädereinl.
COLUMN,137,Kundenart