C11/System/OPTIMA/IQD/Belege/GuV_Bilanz_Salden.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\belege\GuV_Bilanz_Salden.imr
TITLE,GuV_Bilanz_Salden.imr
BEGIN SQL
select (CASE WHEN ((T1."Make" IS NULL) or ((rtrim(T1."Make")) = '')) THEN ('00') ELSE ((rtrim(T1."Make"))) END) || '-' || (CASE WHEN ((((rtrim(T2."ACCT_NR"))) LIKE '2%') and (T3."SITE" IS NULL)) THEN ('01') ELSE ((od_right('00' || (od_left(T3."SITE",2)),2))) END) || '-' || ((rtrim(T2."ACCT_NR"))) || '-' || (CASE WHEN ((T3."DEPARTMENT" IS NULL) or ((rtrim(T3."DEPARTMENT")) = '')) THEN ('00') ELSE ((rtrim(T3."DEPARTMENT"))) END) || '-' || CASE WHEN ((T3."DESTINATION" = '00') and (T3."PRICE_CODE" = '#')) THEN ('99') WHEN ((T3."DESTINATION" = '00') and (T3."PRICE_CODE" <> '')) THEN (T3."PRICE_CODE") WHEN (((T3."DESTINATION" = '00') and (T3."PRICE_CODE" = '')) and (T4."CUSTOMER_GROUP" <> '')) THEN (T4."CUSTOMER_GROUP") WHEN ((rtrim(T3."DESTINATION")) = '') THEN ('00') WHEN (T3."DESTINATION" = 'FZ') THEN ('11') ELSE (T3."DESTINATION") END || '-' || (upper(((truncate(CASE WHEN ((T5."MODEL_LINE" IS NOT NULL) and (T5."MODEL_LINE" <> '')) THEN (T5."MODEL_LINE") WHEN ((T3."MODEL_LINE" IS NOT NULL) and (T3."MODEL_LINE" <> '')) THEN (T3."MODEL_LINE") WHEN ((T3."PRODUCT_GROUP" IS NOT NULL) and (T3."PRODUCT_GROUP" <> '')) THEN (T3."PRODUCT_GROUP") WHEN ((T3."REPAIR_GROUP" IS NOT NULL) and (T3."REPAIR_GROUP" <> '')) THEN (T3."REPAIR_GROUP") ELSE ('00') END))))) as c1,
	   CASE WHEN (T6."FUNCTION_CODE" = '4740') THEN ((od_year(T3."BOOKKEEP_DATE")) * 100) ELSE (T3."BOOKKEEP_PERIOD") END as c2,
	   SUM(T3."DEBIT_AMOUNT") as c3,
	   SUM(T3."CREDIT_AMOUNT") as c4,
	   SUM(T3."DEBIT_QUANTITY") as c5,
	   SUM(T3."CREDIT_QUANTITY") as c6
from "OPTIMA"."import"."ACCOUNT_INFO" T2,
	(("OPTIMA"."import"."ACCT_DOC_KEY" T3 left outer join "OPTIMA"."data"."GC_Marken" T1 on T1."Strategic_Area" = T3."STRATEGIC_AREA") left outer join "OPTIMA"."import"."vPP5Q" T5 on (T3."MODEL_LINE" = T5."MODEL_LINE") and (T3."MAKE" = T5."MAKE_CD")),
	(("OPTIMA"."import"."ACCT_DOC_DATA" T6 left outer join "OPTIMA"."import"."ACCT_DOC_SALESCLAS" T7 on T6."REFERENCE_IDENT" = T7."REFERENCE_IDENT") left outer join "OPTIMA"."import"."CUSTOMER" T4 on T4."CUSTOMER_NUMBER" = T7."CUSTOMER_NUMBER")
where (T2."ACCT_NR" = T3."ACCT_NO") and (T3."UNIQUE_IDENT" = T6."REFERENCE_IDENT")
and (T3."BOOKKEEP_DATE" >= TIMESTAMP '2021-01-01 00:00:00.000')
group by (CASE WHEN ((T1."Make" IS NULL) or ((rtrim(T1."Make")) = '')) THEN ('00') ELSE ((rtrim(T1."Make"))) END) || '-' || (CASE WHEN ((((rtrim(T2."ACCT_NR"))) LIKE '2%') and (T3."SITE" IS NULL)) THEN ('01') ELSE ((od_right('00' || (od_left(T3."SITE",2)),2))) END) || '-' || ((rtrim(T2."ACCT_NR"))) || '-' || (CASE WHEN ((T3."DEPARTMENT" IS NULL) or ((rtrim(T3."DEPARTMENT")) = '')) THEN ('00') ELSE ((rtrim(T3."DEPARTMENT"))) END) || '-' || CASE WHEN ((T3."DESTINATION" = '00') and (T3."PRICE_CODE" = '#')) THEN ('99') WHEN ((T3."DESTINATION" = '00') and (T3."PRICE_CODE" <> '')) THEN (T3."PRICE_CODE") WHEN (((T3."DESTINATION" = '00') and (T3."PRICE_CODE" = '')) and (T4."CUSTOMER_GROUP" <> '')) THEN (T4."CUSTOMER_GROUP") WHEN ((rtrim(T3."DESTINATION")) = '') THEN ('00') WHEN (T3."DESTINATION" = 'FZ') THEN ('11') ELSE (T3."DESTINATION") END || '-' || (upper(((truncate(CASE WHEN ((T5."MODEL_LINE" IS NOT NULL) and (T5."MODEL_LINE" <> '')) THEN (T5."MODEL_LINE") WHEN ((T3."MODEL_LINE" IS NOT NULL) and (T3."MODEL_LINE" <> '')) THEN (T3."MODEL_LINE") WHEN ((T3."PRODUCT_GROUP" IS NOT NULL) and (T3."PRODUCT_GROUP" <> '')) THEN (T3."PRODUCT_GROUP") WHEN ((T3."REPAIR_GROUP" IS NOT NULL) and (T3."REPAIR_GROUP" <> '')) THEN (T3."REPAIR_GROUP") ELSE ('00') END))))),CASE WHEN (T6."FUNCTION_CODE" = '4740') THEN ((od_year(T3."BOOKKEEP_DATE")) * 100) ELSE (T3."BOOKKEEP_PERIOD") END
order by c1 asc,c2 asc

END SQL
COLUMN,0,Konto_Nr_Händler
COLUMN,1,Bookkeep Period
COLUMN,2,Debit Amount
COLUMN,3,Credit Amount
COLUMN,4,Debit Quantity
COLUMN,5,Credit Quantity