C11/System/LOCOSOFT/IQD/Belege/LOC_Belege.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Locosoft_GC
DATASOURCENAME,C:\GlobalCube\System\LOCOSOFT\IQD\belege\loc_belege.imr
TITLE,loc_belege.imr
BEGIN SQL
select T1."accounting_date" as c1,
	   T1."document_type" as c2,
	   (intdiv(T1."document_number",1)) as c3,
	   T1."position_in_document" as c4,
	   T1."customer_number" as c5,
	   T1."nominal_account_number" as c6,
	   T1."is_balanced" as c7,
	   T1."clearing_number" as c8,
	   T1."document_date" as c9,
	   T1."posted_value" as c10,
	   T1."debit_or_credit" as c11,
	   T1."posted_count" as c12,
	   T1."branch_number" as c13,
	   T1."customer_contra_account" as c14,
	   T1."nominal_contra_account" as c15,
	   T1."contra_account_text" as c16,
	   T1."account_form_page_number" as c17,
	   T1."account_form_page_line" as c18,
	   T1."serial_number_each_month" as c19,
	   T1."employee_number" as c20,
	   T1."invoice_date" as c21,
	   T1."invoice_number" as c22,
	   T1."dunning_level" as c23,
	   T1."last_dunning_date" as c24,
	   T1."journal_page" as c25,
	   T1."journal_line" as c26,
	   T1."cash_discount" as c27,
	   T1."term_of_payment" as c28,
	   CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END as c29,
	   CASE WHEN (T1."vehicle_reference" IS NULL) THEN (' - ') ELSE (T1."vehicle_reference") END as c30,
	   T1."vat_id_number" as c31,
	   T1."account_statement_number" as c32,
	   T1."account_statement_page" as c33,
	   T1."vat_key" as c34,
	   T1."days_for_cash_discount" as c35,
	   T1."day_of_actual_accounting" as c36,
	   T1."skr51_branch" as c37,
	   T1."skr51_make" as c38,
	   T1."skr51_cost_center" as c39,
	   T1."skr51_sales_channel" as c40,
	   T1."skr51_cost_unit" as c41,
	   T1."previously_used_account_no" as c42,
	   T1."free_form_accounting_text" as c43,
	   CASE WHEN (T1."skr51_cost_unit" BETWEEN 1 AND 49) THEN ('Neuwagen') WHEN (T1."skr51_cost_unit" BETWEEN 50 AND 59) THEN ('Gebrauchtwagen') WHEN (T1."skr51_cost_unit" BETWEEN 60 AND 69) THEN ('Teile & Zubehör') WHEN (T1."skr51_cost_unit" BETWEEN 70 AND 79) THEN ('Service') WHEN (T1."skr51_cost_unit" = 0) THEN ('Ohne Kostenträger') ELSE null END as c44,
	   T2."is_profit_loss_account" as c45,
	   T1."subsidiary_to_company_ref" as c46,
	   '0' || (cast_numberToString(cast_integer(T1."branch_number"))) as c47,
	   CASE WHEN (((CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END) = 'Saldenübernahme CDK') or (T1."accounting_date" = DATE '2019-01-01')) THEN (DATE '2019-12-01') ELSE (T1."accounting_date") END as c48,
	   (rtrim((cast_numberToString(cast_integer(T1."skr51_make"))))) as c49,
	   CASE WHEN ((extract(DAY FROM (now()) - (cdatetime((CASE WHEN (((CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END) = 'Saldenübernahme CDK') or (T1."accounting_date" = DATE '2019-01-01')) THEN (DATE '2019-12-01') ELSE (T1."accounting_date") END))))) <= 90) THEN ((ascii(((intdiv(T1."document_number",1))))) || ' - ' || (CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END) || '/' || (CASE WHEN (T1."vehicle_reference" IS NULL) THEN (' - ') ELSE (T1."vehicle_reference") END) || ' - ' || (od_left((cast_numberToString(cast_integer(T1."employee_number"))),4))) ELSE ('Buchungen älter 90 Tage') END as c50,
	   '1' as c51,
	   CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END as c52,
	   (od_left((cast_numberToString(cast_integer(T1."nominal_account_number"))),4)) || ' - ' || T2."account_description" as c53,
	   CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_count" / 100 * -1) ELSE (T1."posted_count" / 100) END as c54,
	   (length((cast_numberToString(cast_integer(T1."skr51_cost_center"))) || 'Z') - 1) as c55,
	   (rtrim((cast_numberToString(cast_integer(T1."skr51_cost_center"))))) || ' - ' || T3."skr51_cost_center_name" as c56,
	   (length((cast_numberToString(cast_integer(T1."skr51_sales_channel"))) || 'Z') - 1) as c57,
	   (rtrim((cast_numberToString(cast_integer(T1."skr51_sales_channel"))))) || ' - ' || T3."skr51_sales_channel_name" as c58,
	   (length((cast_numberToString(cast_integer(T1."skr51_cost_unit"))) || 'Z') - 1) as c59,
	   (rtrim((cast_numberToString(cast_integer(T1."skr51_make"))))) || ' - ' || (rtrim((cast_numberToString(cast_integer(T1."skr51_cost_unit"))))) || ' - ' || (T3."skr51_cost_unit_name") as c60,
	   CASE WHEN (T1."skr51_cost_unit" BETWEEN 1 AND 49) THEN (((rtrim((cast_numberToString(cast_integer(T1."skr51_make"))))) || ' - ' || (rtrim((cast_numberToString(cast_integer(T1."skr51_cost_unit"))))) || ' - ' || (T3."skr51_cost_unit_name"))) ELSE ((rtrim((cast_numberToString(cast_integer(T1."skr51_cost_unit"))))) || ' - ' || (T3."skr51_cost_unit_name")) END as c61,
	   CASE WHEN (T2."is_profit_loss_account" = 'J') THEN ('2') ELSE ('1') END as c62,
	   (od_left((cast_numberToString(cast_integer(T1."nominal_account_number"))),1)) as c63,
	   CASE WHEN (not T1."skr51_cost_center" IN (0,2)) THEN ((rtrim((cast_numberToString(cast_integer(T1."nominal_account_number"))))) || '_' || (rtrim((cast_numberToString(cast_integer(T1."skr51_cost_center")))))) ELSE ((cast_numberToString(cast_integer(T1."nominal_account_number")))) END as c64
from "dbo"."nominal_accounts" T2,
	("dbo"."journal_accountings" T1 left outer join "dbo"."accounts_characteristics" T3 on (((((T3."subsidiary_to_company_ref" = T1."subsidiary_to_company_ref") and (T3."skr51_branch" = T1."skr51_branch")) and (T3."skr51_make" = T1."skr51_make")) and (T3."skr51_cost_center" = T1."skr51_cost_center")) and (T3."skr51_sales_channel" = T1."skr51_sales_channel")) and (T3."skr51_cost_unit" = T1."skr51_cost_unit"))
where ((T2."nominal_account_number" = T1."nominal_account_number") and (T2."subsidiary_to_company_ref" = T1."subsidiary_to_company_ref"))
and ((T2."is_profit_loss_account" = 'J') and (not (CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END) IN ('G&V-Abschlussbuchung')))
order by c6 asc

END SQL
COLUMN,0,Accounting Date
COLUMN,1,Document Type
COLUMN,2,Document Number
COLUMN,3,Position In Document
COLUMN,4,Customer Number
COLUMN,5,Nominal Account Number
COLUMN,6,Is Balanced
COLUMN,7,Clearing Number
COLUMN,8,Document Date
COLUMN,9,Posted Value
COLUMN,10,Debit Or Credit
COLUMN,11,Posted Count
COLUMN,12,Branch Number
COLUMN,13,Customer Contra Account
COLUMN,14,Nominal Contra Account
COLUMN,15,Contra Account Text
COLUMN,16,Account Form Page Number
COLUMN,17,Account Form Page Line
COLUMN,18,Serial Number Each Month
COLUMN,19,Employee Number
COLUMN,20,Invoice Date
COLUMN,21,Invoice Number
COLUMN,22,Dunning Level
COLUMN,23,Last Dunning Date
COLUMN,24,Journal Page
COLUMN,25,Journal Line
COLUMN,26,Cash Discount
COLUMN,27,Term Of Payment
COLUMN,28,Posting Text
COLUMN,29,Vehicle Reference
COLUMN,30,Vat Id Number
COLUMN,31,Account Statement Number
COLUMN,32,Account Statement Page
COLUMN,33,Vat Key
COLUMN,34,Days For Cash Discount
COLUMN,35,Day Of Actual Accounting
COLUMN,36,Skr51 Branch
COLUMN,37,Skr51 Make
COLUMN,38,Skr51 Cost Center
COLUMN,39,Skr51 Sales Channel
COLUMN,40,Skr51 Cost Unit
COLUMN,41,Previously Used Account No
COLUMN,42,Free Form Accounting Text
COLUMN,43,Free Form Document Text
COLUMN,44,Nom_Account_Is Profit Loss Account
COLUMN,45,Rechtseinheit
COLUMN,46,Betrieb
COLUMN,47,Bookkeep Date
COLUMN,48,Marke
COLUMN,49,Text
COLUMN,50,Mandant
COLUMN,51,Betrag
COLUMN,52,Konto_mit_Bezeichnung
COLUMN,53,Menge
COLUMN,54,Stellen Cost Center
COLUMN,55,KST
COLUMN,56,Stellen Sales Channel
COLUMN,57,Absatzkanal
COLUMN,58,Stellen Cost Unit
COLUMN,59,Kostenträger_mit_Null
COLUMN,60,Kostenträger
COLUMN,61,GuV_Bilanz
COLUMN,62,Susa
COLUMN,63,Acct Nr