C11/System/WERWISO/IQD/Belege/KORE_Buchungen_Vorvorjahr.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,DATEV_Kost_MDB_Vorvorjahr
DATASOURCENAME,C:\GlobalCube\System\WERWISO\IQD\belege\KORE_Buchungen_Vorvorjahr.imr
TITLE,KORE_Buchungen_Vorvorjahr.imr
BEGIN SQL
select T1."idKstNr" as c1,
	   T1."idMonat" as c2,
	   T1."idZeilennr" as c3,
	   T1."dtKonto" as c4,
	   T1."dtBewegungstyp" as c5,
	   T1."dtBSNr" as c6,
	   T1."dtSatzNr" as c7,
	   T1."dtGegenkonto" as c8,
	   T1."dtBelegNr" as c9,
	   T1."dtDatum" as c10,
	   T1."dtBetrag" as c11,
	   T1."dtMenge" as c12,
	   T1."dtAnteil" as c13,
	   T1."dtUmlageReihenfolge" as c14,
	   T1."dtVerrechnungsKst" as c15,
	   T1."dtBasiszeile" as c16,
	   T1."dtBasisbetrag" as c17,
	   T1."dtKostensatz" as c18,
	   T1."dtBasismenge" as c19,
	   T1."dtBasisanteil" as c20,
	   T1."idIndex" as c21,
	   T1."dtBuchungstext" as c22,
	   T1."dtVerarbNr" as c23,
	   T1."dtWerteDirektAusEBEW" as c24,
	   T1."dtKZSonstiges" as c25,
	   T1."dtBetragDivNull" as c26,
	   T1."dtMengeDivNull" as c27,
	   T1."dtKZSollHaben" as c28,
	   T1."dtKtoNrAufbereitet" as c29,
	   T1."dtGKtoNrAufbereitet" as c30,
	   T1."dtBetragUmkehr" as c31,
	   T1."dtMengeUmkehr" as c32,
	   T1."dtWJ" as c33,
	   T1."dtKNWKennz" as c34,
	   T1."dtBelegfeld2" as c35,
	   T1."dtKost2" as c36,
	   T1."dtKRMandSchluessel" as c37,
	   T1."dtDispo_Nr" as c38,
	   T1."dtBelegvorhanden" as c39,
	   T1."dtVorlaufTechn" as c40,
	   T1."dtFolgeBuZaehl" as c41,
	   T1."dtSplittSZaehler" as c42,
	   T1."dtIKVTyp" as c43,
	   T1."KostDatum" as c44,
	   T1."VorlaufDatum" as c45,
	   T2."KtoNr" as c46,
	   T2."KtoBeschriftung" as c47,
	   T3."Zeilennr" as c48,
	   T3."Zeilenbezeichnung" as c49,
	   '1' as c50,
	   CASE WHEN (T1."idKstNr" <> '') THEN ((substring((ltrim(T1."idKstNr")) from 3 for 1))) ELSE ('ungültig') END as c51,
	   CASE WHEN (T1."idKstNr" <> '') THEN ((substring((ltrim(T1."idKstNr")) from 1 for 1))) ELSE ('ungültig') END as c52,
	   CASE WHEN (T1."idKstNr" <> '') THEN ((substring((ltrim(T1."idKstNr")) from 4 for 2))) ELSE ('ungültig') END as c53,
	   CASE WHEN (T1."idKstNr" <> '') THEN ((substring((ltrim(T1."idKstNr")) from 6 for 2))) ELSE ('ungültig') END as c54,
	   T4."KST-LangBez" as c55,
	   T4."KST_EIGENSCHAFT1" as c56,
	   T4."KST_EIGENSCHAFT2" as c57,
	   T4."KST_EIGENSCHAFT3" as c58,
	   T4."KST_EIGENSCHAFT4" as c59,
	   T4."KST_EIGENSCHAFT5" as c60,
	   T4."KST_EIGENSCHAFT6" as c61,
	   T4."KST_EIGENSCHAFT7" as c62,
	   T4."KST_EIGENSCHAFT8" as c63,
	   T4."KST_EIGENSCHAFT9" as c64,
	   T5."KSTESA_NUMMER" as c65,
	   T5."KSTESA_BEZEICHNUNG" as c66,
	   (ltrim(T5."KSTESA_NUMMER")) || ' - ' || T5."KSTESA_BEZEICHNUNG" as c67,
	   'S+K' as c68,
	   T6."KSTESA_NUMMER" as c69,
	   T6."KSTESA_BEZEICHNUNG" as c70,
	   (CASE WHEN (T1."idKstNr" <> '') THEN ((substring((ltrim(T1."idKstNr")) from 1 for 1))) ELSE ('ungültig') END) || ' - ' || T6."KSTESA_BEZEICHNUNG" as c71,
	   T7."KSTESA_NUMMER" as c72,
	   T7."KSTESA_BEZEICHNUNG" as c73,
	   T7."KSTESA_BEZEICHNUNG" as c74,
	   (substring((CASE WHEN (T1."idKstNr" <> '') THEN ((substring((ltrim(T1."idKstNr")) from 4 for 2))) ELSE ('ungültig') END) from 1 for 1)) as c75,
	   T8."KSTESA_NUMMER" as c76,
	   T8."KSTESA_BEZEICHNUNG" as c77,
	   (CASE WHEN (T1."idKstNr" <> '') THEN ((substring((ltrim(T1."idKstNr")) from 4 for 2))) ELSE ('ungültig') END) || ' - ' || T8."KSTESA_BEZEICHNUNG" as c78,
	   T9."KSTESA_NUMMER" as c79,
	   T9."KSTESA_BEZEICHNUNG" as c80,
	   (CASE WHEN (T1."idKstNr" <> '') THEN ((substring((ltrim(T1."idKstNr")) from 6 for 2))) ELSE ('ungültig') END) || ' - ' || T9."KSTESA_BEZEICHNUNG" as c81,
	   CASE WHEN ((od_month(T1."dtDatum")) <> (od_month(T1."idMonat"))) THEN (T1."idMonat") ELSE (T1."dtDatum") END as c82,
	   T1."idZeilennr" as c83,
	   (ascii(T2."KtoNr")) || ' - ' || T2."KtoBeschriftung" as c84,
	   T2."KtoNr" as c85,
	   CASE WHEN ((extract(DAY FROM (now()) - (CASE WHEN ((od_month(T1."dtDatum")) <> (od_month(T1."idMonat"))) THEN (T1."idMonat") ELSE (T1."dtDatum") END))) <= 90) THEN (T1."dtBelegNr" || ' - ' || T1."dtBuchungstext") ELSE ('Belege älter 90 Tage') END as c86,
	   (substring((CASE WHEN (T1."idKstNr" <> '') THEN ((substring((ltrim(T1."idKstNr")) from 6 for 2))) ELSE ('ungültig') END) from 1 for 1)) as c87,
	   CASE WHEN ((substring(((ascii(T2."KtoNr")) || ' - ' || T2."KtoBeschriftung") from 1 for 1)) IN ('0','1','3','9')) THEN ('1') ELSE ('2') END as c88,
	   (substring(((ascii(T2."KtoNr")) || ' - ' || T2."KtoBeschriftung") from 1 for 1)) as c89,
	   T1."dtBetrag" * -1 as c90,
	   RSUM((T1."dtBetrag" * -1)) as c91,
	   T1."dtMenge" as c92
from (((((((("tblKNW" T1 left outer join "tblKtoTxt" T2 on T1."dtKonto" = T2."KtoNr") left outer join "Strukturplan" T3 on T1."idZeilennr" = T3."Zeilennr") left outer join "EbeneElement" T4 on T1."idKstNr" = T4."Element") left outer join "tblKS_KSTESA" T5 on ((ltrim(T4."KST_EIGENSCHAFT5")) = (ltrim(T5."KSTESA_NUMMER"))) and (T5."KSTESA_OWN_ID" = 5)) left outer join "tblKS_KSTESA" T6 on ((ltrim(T4."KST_EIGENSCHAFT1")) = (ltrim(T6."KSTESA_NUMMER"))) and (T6."KSTESA_OWN_ID" = 1)) left outer join "tblKS_KSTESA" T7 on ((ltrim(T4."KST_EIGENSCHAFT2")) = (ltrim(T7."KSTESA_NUMMER"))) and (T7."KSTESA_OWN_ID" = 2)) left outer join "tblKS_KSTESA" T8 on ((ltrim(T4."KST_EIGENSCHAFT3")) = (ltrim(T8."KSTESA_NUMMER"))) and (T8."KSTESA_OWN_ID" = 3)) left outer join "tblKS_KSTESA" T9 on ((ltrim(T4."KST_EIGENSCHAFT4")) = (ltrim(T9."KSTESA_NUMMER"))) and (T9."KSTESA_OWN_ID" = 4))
where (T1."dtKonto" IS NOT NULL)

END SQL
COLUMN,0,Idkstnr
COLUMN,1,Idmonat
COLUMN,2,Idzeilennr
COLUMN,3,Dtkonto
COLUMN,4,Dtbewegungstyp
COLUMN,5,Dtbsnr
COLUMN,6,Dtsatznr
COLUMN,7,Dtgegenkonto
COLUMN,8,Dtbelegnr
COLUMN,9,Dtdatum
COLUMN,10,Dtbetrag
COLUMN,11,Dtmenge
COLUMN,12,Dtanteil
COLUMN,13,Dtumlagereihenfolge
COLUMN,14,Dtverrechnungskst
COLUMN,15,Dtbasiszeile
COLUMN,16,Dtbasisbetrag
COLUMN,17,Dtkostensatz
COLUMN,18,Dtbasismenge
COLUMN,19,Dtbasisanteil
COLUMN,20,Idindex
COLUMN,21,Dtbuchungstext
COLUMN,22,Dtverarbnr
COLUMN,23,Dtwertedirektausebew
COLUMN,24,Dtkzsonstiges
COLUMN,25,Dtbetragdivnull
COLUMN,26,Dtmengedivnull
COLUMN,27,Dtkzsollhaben
COLUMN,28,Dtktonraufbereitet
COLUMN,29,Dtgktonraufbereitet
COLUMN,30,Dtbetragumkehr
COLUMN,31,Dtmengeumkehr
COLUMN,32,Dtwj
COLUMN,33,Dtknwkennz
COLUMN,34,Dtbelegfeld2
COLUMN,35,Dtkost2
COLUMN,36,Dtkrmandschluessel
COLUMN,37,Dtdispo Nr
COLUMN,38,Dtbelegvorhanden
COLUMN,39,Dtvorlauftechn
COLUMN,40,Dtfolgebuzaehl
COLUMN,41,Dtsplittszaehler
COLUMN,42,Dtikvtyp
COLUMN,43,Kostdatum
COLUMN,44,Vorlaufdatum
COLUMN,45,Ktonr
COLUMN,46,Ktobeschriftung
COLUMN,47,Zeilennr
COLUMN,48,Zeilenbezeichnung
COLUMN,49,Mandant ID
COLUMN,50,Standort_ID
COLUMN,51,Marke_ID
COLUMN,52,KST_ID
COLUMN,53,Absatzkanal_ID
COLUMN,54,Kst-langbez
COLUMN,55,Kst Eigenschaft1_Marke
COLUMN,56,Kst Eigenschaft2_Standort
COLUMN,57,Kst Eigenschaft3_KST
COLUMN,58,Kst Eigenschaft4_Absatzkanal
COLUMN,59,Kst Eigenschaft5_KTR
COLUMN,60,Kst Eigenschaft6_Antriebsart
COLUMN,61,Kst Eigenschaft7
COLUMN,62,Kst Eigenschaft8
COLUMN,63,Kst Eigenschaft9
COLUMN,64,Kstesa Nummer_KTR
COLUMN,65,Kstesa Bezeichnung_KTR
COLUMN,66,Kostenträger_mit_Bez
COLUMN,67,Hauptbetrieb
COLUMN,68,Kstesa Nummer_Marke
COLUMN,69,Kstesa Bezeichnung_Marke
COLUMN,70,Marke_mit_Bez
COLUMN,71,Kstesa Nummer_Standort
COLUMN,72,Kstesa Bezeichnung_Standort
COLUMN,73,Standort_Name
COLUMN,74,Kostenstelle
COLUMN,75,Kstesa Nummer_KST
COLUMN,76,Kstesa Bezeichnung_KST
COLUMN,77,KST_mit_Bez
COLUMN,78,Kstesa Nummer_Absatzkanal
COLUMN,79,Kstesa Bezeichnung_Absatzkanal
COLUMN,80,Absatzkanal_mit_Bez
COLUMN,81,Invoice Date
COLUMN,82,Ebene6
COLUMN,83,Konto
COLUMN,84,Acct Nr
COLUMN,85,Text
COLUMN,86,Ebene21
COLUMN,87,Konto_Art
COLUMN,88,Susa
COLUMN,89,Betrag
COLUMN,90,Summe  (Betrag) Nr.1
COLUMN,91,Menge