COGNOS QUERY STRUCTURE,1,1 DATABASE,Locosoft_GC DATASOURCENAME,C:\Gaps_Locosoft_neu\Portal\System\IQD\Belege\LOC_Belege_STK.imr TITLE,LOC_Belege_STK.imr BEGIN SQL select T1."accounting_date" as c1, T1."document_type" as c2, T1."document_number" 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, T1."posting_text" as c29, T1."vehicle_reference" 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, T1."free_form_document_text" as c44, T2."is_profit_loss_account" as c45, '1' as c46, '0' || (cast_numberToString(cast_integer(T1."skr51_branch"))) as c47, T1."accounting_date" as c48, '0' || (cast_numberToString(cast_integer(T1."skr51_make"))) as c49, CASE WHEN ((extract(DAY FROM (now()) - (cdatetime(T1."accounting_date")))) <= 90) THEN ((cast_numberToString(cast_integer(T1."document_number"))) || ' - ' || T1."invoice_number" || ' - ' || T1."posting_text" || '/' || T1."vehicle_reference" || ' - ' || (cast_numberToString(cast_integer(T1."employee_number")))) 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, (cast_numberToString(cast_integer(T1."nominal_account_number"))) || ' - ' || T2."account_description" as c53, CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_count" / 100) ELSE (T1."posted_count" / 100) END as c54, (length((cast_numberToString(cast_integer(T1."skr51_cost_center"))) || 'Z') - 1) as c55, CASE WHEN (((length((cast_numberToString(cast_integer(T1."skr51_cost_center"))) || 'Z') - 1)) = 1) THEN ('0' || (cast_numberToString(cast_integer(T1."skr51_cost_center")))) ELSE ((cast_numberToString(cast_integer(T1."skr51_cost_center")))) END as c56, (length((cast_numberToString(cast_integer(T1."skr51_sales_channel"))) || 'Z') - 1) as c57, CASE WHEN (((length((cast_numberToString(cast_integer(T1."skr51_sales_channel"))) || 'Z') - 1)) = 1) THEN ('0' || (cast_numberToString(cast_integer(T1."skr51_sales_channel")))) ELSE ((cast_numberToString(cast_integer(T1."skr51_sales_channel")))) END as c58, (length((cast_numberToString(cast_integer(T1."skr51_cost_unit"))) || 'Z') - 1) as c59, CASE WHEN (((length((cast_numberToString(cast_integer(T1."skr51_cost_unit"))) || 'Z') - 1)) = 1) THEN ('0' || (cast_numberToString(cast_integer(T1."skr51_cost_unit")))) ELSE ((cast_numberToString(cast_integer(T1."skr51_cost_unit")))) END as c60, CASE WHEN ((CASE WHEN (((length((cast_numberToString(cast_integer(T1."skr51_cost_center"))) || 'Z') - 1)) = 1) THEN ('0' || (cast_numberToString(cast_integer(T1."skr51_cost_center")))) ELSE ((cast_numberToString(cast_integer(T1."skr51_cost_center")))) END) IN ('14','11','13','10','12','15')) THEN ((cast_numberToString(cast_integer(T1."skr51_make"))) || '-' || (CASE WHEN (((length((cast_numberToString(cast_integer(T1."skr51_cost_unit"))) || 'Z') - 1)) = 1) THEN ('0' || (cast_numberToString(cast_integer(T1."skr51_cost_unit")))) ELSE ((cast_numberToString(cast_integer(T1."skr51_cost_unit")))) END)) ELSE ((CASE WHEN (((length((cast_numberToString(cast_integer(T1."skr51_cost_unit"))) || 'Z') - 1)) = 1) THEN ('0' || (cast_numberToString(cast_integer(T1."skr51_cost_unit")))) ELSE ((cast_numberToString(cast_integer(T1."skr51_cost_unit")))) END)) END as c61, ('3') as c62, 'STK' as c63, (cast_numberToString(cast_integer(T1."nominal_account_number"))) || '_STK' as c64 from "dbo"."nominal_accounts" T2, "dbo"."journal_accountings" T1 where (T2."nominal_account_number" = T1."nominal_account_number") and ((T2."is_profit_loss_account" = 'J') and (T1."nominal_account_number" IN (8000,8010,8100,8110,8400,8410))) 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_ori COLUMN,52,Konto_mit_Bezeichnung COLUMN,53,Betrag 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