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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_AUTOLINE
DATASOURCENAME,C:\GlobalCube\System\Autoline\IQD\belege\Belege_Autoline.imr
TITLE,Belege_Autoline.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")) <= 180) THEN ((rtrim((cast_numberToString(cast_integer(T1."DocumentNumber"))))) || ' - ' || T1."JournalClass" || ' - ' || T1."CustomerReference") ELSE ('Belege älter 180 Tage') END as c71,
	   T4."CostCentreGroup" || ' - ' || T4."Description002" as c72,
	   (rtrim((cast_numberToString(cast_integer(T3."CostCentre"))))) || ' - ' || T3."Description002" as c73
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 (od_left((cast_numberToString(cast_integer(T1."CostCentre"))),2)) = T5."LocationCode")
where ((T2."AccountType" = 'P') and (not T3."CostCentreGroup" LIKE '9%'))
order by 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
COLUMN,72,KST_Detail