GAPS/Portal/System/IQD/Belege/Bilanz_SKR51_O21_deop03.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_2
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Belege\Bilanz_SKR51_O21_deop03.imr
TITLE,Bilanz_SKR51_O21_deop03.imr
BEGIN SQL
select (rtrim(T1."ACCT_NR")) as c1,
	   T1."LEDGER_ACCTS_NAME" as c2,
	   T1."DEPT_SPLIT" as c3,
	   T1."TYPE_ACCTT" as c4,
	   T2."ACCT_NO" as c5,
	   T2."BOOKKEEP_DATE" as c6,
	   T2."BOOKKEEP_PERIOD" as c7,
	   T2."DOCUMENT_NO" as c8,
	   T2."DEBIT_AMOUNT" as c9,
	   T2."CREDIT_AMOUNT" as c10,
	   T2."DEBIT_QUANTITY" as c11,
	   T2."CREDIT_QUANTITY" as c12,
	   T2."AA_TRTYPE" as c13,
	   '2' as c14,
	   CASE WHEN ((((rtrim(T2."SITE"))) IS NOT NULL) and (((rtrim(T2."SITE"))) <> '')) THEN (((rtrim(T2."SITE")))) ELSE ('00') END as c15,
	   (rtrim(T2."SITE")) as c16,
	   T3."DESCRIPTION" as c17,
	   CASE WHEN (((ucase((rtrim(T2."STRATEGIC_AREA"))))) = 'AUTO') THEN ('ZZ') WHEN (((ucase((rtrim(T2."STRATEGIC_AREA"))))) = 'RENAULT') THEN ('RN') WHEN ((((ucase((rtrim(T2."STRATEGIC_AREA"))))) IS NOT NULL) and (((ucase((rtrim(T2."STRATEGIC_AREA"))))) <> '')) THEN ((od_left(((ucase((rtrim(T2."STRATEGIC_AREA"))))),2))) ELSE ('00') END as c18,
	   (rtrim(T2."MAKE")) as c19,
	   (ucase((rtrim(T2."STRATEGIC_AREA")))) as c20,
	   T4."DESCRIPTION" as c21,
	   CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END as c22,
	   CASE WHEN ((CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) LIKE '1%') THEN ('1 - NW') WHEN ((CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) LIKE '2%') THEN ('2 - GW') WHEN ((CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) LIKE '3%') THEN ('3 - TZ') WHEN ((CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) LIKE '4%') THEN ('4 - KDD') WHEN ((CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) LIKE '5%') THEN ('5 - WB') WHEN ((CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) LIKE '6%') THEN ('6 - TST') WHEN ((CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) LIKE '7%') THEN ('7 - LOG') WHEN ((CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) LIKE '9%') THEN ('9 - VW') ELSE null END as c23,
	   (CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) || ' - ' || T5."DESCRIPTION" as c24,
	   (rtrim(T2."DEPARTMENT")) as c25,
	   T5."DESCRIPTION" as c26,
	   CASE WHEN ((((rtrim(T2."DESTINATION"))) IS NOT NULL) and (((rtrim(T2."DESTINATION"))) <> '')) THEN (((rtrim(T2."DESTINATION")))) ELSE ('00') END as c27,
	   (substring((CASE WHEN ((((rtrim(T2."DESTINATION"))) IS NOT NULL) and (((rtrim(T2."DESTINATION"))) <> '')) THEN (((rtrim(T2."DESTINATION")))) ELSE ('00') END) from 1 for 1)) as c28,
	   CASE WHEN ((CASE WHEN ((((rtrim(T2."DESTINATION"))) IS NOT NULL) and (((rtrim(T2."DESTINATION"))) <> '')) THEN (((rtrim(T2."DESTINATION")))) ELSE ('00') END) LIKE '0%') THEN ('00 - Absatzweg fehlt') ELSE ((CASE WHEN ((((rtrim(T2."DESTINATION"))) IS NOT NULL) and (((rtrim(T2."DESTINATION"))) <> '')) THEN (((rtrim(T2."DESTINATION")))) ELSE ('00') END) || '- ' || T6."CUST_GROUP_SPECIFY") END as c29,
	   (rtrim(T2."DESTINATION")) as c30,
	   T6."CUSTOMER_GROUP" as c31,
	   T6."CUST_GROUP_SPECIFY" as c32,
	   T2."VEHICLE_TYPE" as c33,
	   CASE WHEN ((((rtrim(T2."MODEL_LINE"))) IS NOT NULL) and (((rtrim(T2."MODEL_LINE"))) <> '')) THEN (((rtrim(T2."MODEL_LINE")))) WHEN ((((cast_numberToString(cast_integer(T7."SKR51_PRODUCT_GROUP")))) IS NOT NULL) and (((cast_numberToString(cast_integer(T7."SKR51_PRODUCT_GROUP")))) <> '')) THEN (((cast_numberToString(cast_integer(T7."SKR51_PRODUCT_GROUP"))))) WHEN ((((rtrim(T2."REPAIR_GROUP"))) IS NOT NULL) and (((rtrim(T2."REPAIR_GROUP"))) <> '')) THEN (((rtrim(T2."REPAIR_GROUP")))) ELSE ('00') END as c34,
	   (rtrim(T2."MODEL_LINE")) as c35,
	   T8."MAKE_CD" as c36,
	   T8."MODEL_LINE" as c37,
	   T8."MOD_LIN_SPECIFY" as c38,
	   (rtrim(T2."PRODUCT_GROUP")) as c39,
	   (cast_numberToString(cast_integer(T7."SKR51_PRODUCT_GROUP"))) as c40,
	   (rtrim(T2."REPAIR_GROUP")) as c41,
	   (CASE WHEN (((ucase((rtrim(T2."STRATEGIC_AREA"))))) = 'AUTO') THEN ('ZZ') WHEN (((ucase((rtrim(T2."STRATEGIC_AREA"))))) = 'RENAULT') THEN ('RN') WHEN ((((ucase((rtrim(T2."STRATEGIC_AREA"))))) IS NOT NULL) and (((ucase((rtrim(T2."STRATEGIC_AREA"))))) <> '')) THEN ((od_left(((ucase((rtrim(T2."STRATEGIC_AREA"))))),2))) ELSE ('00') END) || '-' || (CASE WHEN ((((rtrim(T2."SITE"))) IS NOT NULL) and (((rtrim(T2."SITE"))) <> '')) THEN (((rtrim(T2."SITE")))) ELSE ('00') END) || '-' || ((rtrim(T1."ACCT_NR"))) || '-' || (CASE WHEN ((((rtrim(T2."DEPARTMENT"))) IS NOT NULL) and (((rtrim(T2."DEPARTMENT"))) <> '')) THEN (((rtrim(T2."DEPARTMENT")))) ELSE ('00') END) || '-' || (CASE WHEN ((((rtrim(T2."DESTINATION"))) IS NOT NULL) and (((rtrim(T2."DESTINATION"))) <> '')) THEN (((rtrim(T2."DESTINATION")))) ELSE ('00') END) || '-' || (CASE WHEN ((((rtrim(T2."MODEL_LINE"))) IS NOT NULL) and (((rtrim(T2."MODEL_LINE"))) <> '')) THEN (((rtrim(T2."MODEL_LINE")))) WHEN ((((cast_numberToString(cast_integer(T7."SKR51_PRODUCT_GROUP")))) IS NOT NULL) and (((cast_numberToString(cast_integer(T7."SKR51_PRODUCT_GROUP")))) <> '')) THEN (((cast_numberToString(cast_integer(T7."SKR51_PRODUCT_GROUP"))))) WHEN ((((rtrim(T2."REPAIR_GROUP"))) IS NOT NULL) and (((rtrim(T2."REPAIR_GROUP"))) <> '')) THEN (((rtrim(T2."REPAIR_GROUP")))) ELSE ('00') END) as c42,
	   CASE WHEN ((((rtrim(T2."MODEL_LINE"))) IS NOT NULL) and (((rtrim(T2."MODEL_LINE"))) <> '')) THEN ('NW') WHEN ((((cast_numberToString(cast_integer(T7."SKR51_PRODUCT_GROUP")))) IS NOT NULL) and (((cast_numberToString(cast_integer(T7."SKR51_PRODUCT_GROUP")))) <> '')) THEN ('TZ') WHEN ((((rtrim(T2."REPAIR_GROUP"))) IS NOT NULL) and (((rtrim(T2."REPAIR_GROUP"))) <> '')) THEN ('SC') ELSE ('') END as c43,
	   T9."COMMENT" as c44,
	   (cast_numberToString(cast_integer(T2."DOCUMENT_NO"))) as c45,
	   CASE WHEN ((extract(DAY FROM (now()) - T2."BOOKKEEP_DATE")) <= 30) THEN ((rtrim(((cast_numberToString(cast_integer(T2."DOCUMENT_NO")))))) || ' - ' || T9."COMMENT") ELSE ('Sammelstelle Belege') END as c46,
	   T1."TYPE_ACCTT" as c47,
	   (od_left(((rtrim(T1."ACCT_NR"))),1)) as c48,
	   (database()) as c49,
	   T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT" as c50,
	   CASE WHEN (((rtrim(T1."ACCT_NR"))) IN ('8000','8010','8020','8100','8110','8410','8510','5701')) THEN (T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY") ELSE null END as c51,
	   T9."SUB_ACCT_NO" as c52,
	   (od_left(((cast_numberToString(cast_integer(T2."DOCUMENT_NO")))),1)) as c53
from "deop03"."dbo"."ACCOUNT_INFO" T1,
	"deop03"."dbo"."ACCT_DOC_DATA" T9,
	(((((("deop03"."dbo"."ACCT_DOC_KEY" T2 left outer join "deop03"."dbo"."SITE" T3 on T2."SITE" = T3."SITE_ID") left outer join "deop03"."dbo"."STRATEGIC_AREA" T4 on T2."STRATEGIC_AREA" = T4."STRATEGIC_AREA_ID") left outer join "deop03"."dbo"."DEPARTMENT_TYPE" T5 on T2."DEPARTMENT" = T5."DEPARTMENT_TYPE_ID") left outer join "deop03"."dbo"."vPP48" T6 on T2."DESTINATION" = T6."CUSTOMER_GROUP") left outer join "deop03"."dbo"."vPP65" T7 on T2."PRODUCT_GROUP" = T7."PRODUCT_GROUP") left outer join "deop03"."dbo"."vPP5Q" T8 on (T2."MAKE" = T8."MAKE_CD") and (T2."MODEL_LINE" = T8."MODEL_LINE"))
where (T1."ACCT_NR" = T2."ACCT_NO") and (T2."UNIQUE_IDENT" = T9."REFERENCE_IDENT")
and ((T1."TYPE_ACCTT" = '1') and (T2."BOOKKEEP_PERIOD" >= 202001))

END SQL
COLUMN,0,Acct Nr_kurz
COLUMN,1,Ledger Accts Name
COLUMN,2,Dept Split
COLUMN,3,Type Acctt
COLUMN,4,Acct No
COLUMN,5,Bookkeep Date
COLUMN,6,Bookkeep Period
COLUMN,7,Document No
COLUMN,8,Debit Amount
COLUMN,9,Credit Amount
COLUMN,10,Debit Quantity
COLUMN,11,Credit Quantity
COLUMN,12,Aa Trtype
COLUMN,13,Rechtseinheit
COLUMN,14,Betrieb
COLUMN,15,Site
COLUMN,16,Site_Description
COLUMN,17,Marke
COLUMN,18,Make
COLUMN,19,Strategic Area
COLUMN,20,Strategic Area_Description
COLUMN,21,KST
COLUMN,22,KST_1
COLUMN,23,KST_2
COLUMN,24,Department
COLUMN,25,Department_Description
COLUMN,26,ABS
COLUMN,27,ABS_1
COLUMN,28,ABS_2
COLUMN,29,Destination
COLUMN,30,Customer Group_pp48
COLUMN,31,Cust Group Specify_pp48
COLUMN,32,Vehicle Type
COLUMN,33,KTR
COLUMN,34,Model Line
COLUMN,35,Make Cd_pp5q
COLUMN,36,Model Line_pp5q
COLUMN,37,Mod Lin Specify_pp5q
COLUMN,38,Product Group
COLUMN,39,Skr51 Product Group_pp65
COLUMN,40,Repair Group
COLUMN,41,Acct Nr
COLUMN,42,KTR_Quelle
COLUMN,43,Comment
COLUMN,44,Beleg_Nr
COLUMN,45,Text
COLUMN,46,Susa_Kontoart
COLUMN,47,Susa_Acct_1
COLUMN,48,Mandant
COLUMN,49,Betrag
COLUMN,50,Menge
COLUMN,51,Sub Acct No
COLUMN,52,Beleg_Nr_1