C11/System/ARI/IQD/Service/Service_Gutschriften_ab_2011_ben_AW.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_ARI
DATASOURCENAME,C:\GlobalCube\System\ARI\IQD\Service\Service_Gutschriften_ab_2011_ben_AW.imr
TITLE,Service_Gutschriften_ab_2011_ben_AW.imr
BEGIN SQL
select distinct T1."No_" as c1,
	   T1."Sell-to Customer No_" as c2,
	   T1."Bill-to Customer No_" as c3,
	   T1."Bill-to Name" as c4,
	   T1."Bill-to Address" as c5,
	   T1."Bill-to City" as c6,
	   T1."Posting Date" as c7,
	   T1."Payment Terms Code" as c8,
	   T1."Location Code" as c9,
	   T1."Shortcut Dimension 1 Code" as c10,
	   T1."Shortcut Dimension 2 Code" as c11,
	   T1."Customer Posting Group" as c12,
	   T1."Price Group Code" as c13,
	   T1."Prices Including VAT" as c14,
	   T1."Allow Quantity Disc_" as c15,
	   T1."Salesperson Code" as c16,
	   T1."On Hold" as c17,
	   T1."Gen_ Bus_ Posting Group" as c18,
	   T1."Transaction Type" as c19,
	   T1."Sell-to Customer Name" as c20,
	   T1."Sell-to Address" as c21,
	   T1."Sell-to City" as c22,
	   T1."Correction" as c23,
	   T1."Document Date" as c24,
	   T1."External Document No_" as c25,
	   T1."Area" as c26,
	   T1."No_ Series" as c27,
	   T1."User ID" as c28,
	   T1."Order Type" as c29,
	   CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END as c30,
	   T1."Branch Code" as c31,
	   T2."VIN" as c32,
	   '' as c33,
	   '' as c34,
	   T2."Model" as c35,
	   T3."Document No_" as c36,
	   T3."Shortcut Dimension 1 Code" as c37,
	   T3."Shortcut Dimension 2 Code" as c38,
	   T3."Gen_ Bus_ Posting Group" as c39,
	   T3."Order No_" as c40,
	   T3."VIN" as c41,
	   T3."Vehicle Status" as c42,
	   T3."Registration Date" as c43,
	   T3."Mileage" as c44,
	   CASE WHEN (T3."Service Order No_" IS NULL) THEN ('Gutschrift ohne Auftrag') ELSE (T3."Service Order No_") END as c45,
	   T3."Customer Group Code" as c46,
	   T4."Service Advisor No_" as c47,
	   T5."Service Advisor No_" as c48,
	   T6."No_" as c49,
	   T6."First Name" as c50,
	   T6."Last Name" as c51,
	   T7."No_" as c52,
	   T7."First Name" as c53,
	   T7."Last Name" as c54,
	   CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" || ' ' || T7."Last Name") ELSE (T6."First Name" || ' ' || T6."Last Name") END as c55,
	   '1' as c56,
	   CASE WHEN (T1."Location Code" IN ('01BSPKW')) THEN ('10') WHEN (T1."Location Code" IN ('02BSMOT')) THEN ('20') WHEN (T1."Location Code" IN ('03RHF')) THEN ('30') WHEN (T1."Location Code" IN ('04SFH')) THEN ('40') WHEN (T1."Location Code" IN ('05WT')) THEN ('50') WHEN (T1."Location Code" IN ('06BI')) THEN ('26') WHEN (T1."Location Code" IN ('07TR')) THEN ('70') ELSE null END as c57,
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') WHEN (T1."No_ Series" LIKE 'I%') THEN ('Intern') ELSE ('Extern') END as c58,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW I','BMWI','BMW','BMW-MINI','BMW-C1','BMW-ALPINA','BMW-MOT','TRIUMPH','ALPINA')) THEN (T1."Shortcut Dimension 2 Code") WHEN ((CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END) = 'Gutschrift ohne Auftrag') THEN ('GS ohne Auftrag') ELSE ('Fremdfabrikat') END as c59,
	   T2."Model" as c60,
	   T2."VIN" || ' - ' || T2."Model" as c61,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('ALPINA','TRIUMPH','BMW-C1','BMW-ALPINA','BMWI','BMW','BMW-MINI','BMW-MOT')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as c62,
	   T4."Service Posting Group" as c63,
	   T5."Service Posting Group" as c64,
	   CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END as c65,
	   T8."Code" as c66,
	   T8."Description" as c67,
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T8."Description") END as c68,
	   T9."No_" as c69,
	   T9."Name" as c70,
	   T9."No_" || ' - ' || T9."Name" as c71,
	   '' as c72,
	   T1."Posting Date" as c73,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 180) THEN (T1."No_" || ' - ' || (CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END) || ' - ' || T9."Name") ELSE ('Aufträge älter 180 Tage') END as c74,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" || ' ' || T7."Last Name") ELSE (T6."First Name" || ' ' || T6."Last Name") END) IS NOT NULL)) THEN (T1."No_" || ' - ' || (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" || ' ' || T7."Last Name") ELSE (T6."First Name" || ' ' || T6."Last Name") END) || ' - ' || T9."Name" || ' - ' || (asciiz(extract(YEAR FROM T1."Posting Date"),4) || '-' || asciiz(extract(MONTH FROM T1."Posting Date"),2) || '-' || asciiz(extract(DAY FROM T1."Posting Date"),2))) WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" || ' ' || T7."Last Name") ELSE (T6."First Name" || ' ' || T6."Last Name") END) IS NULL)) THEN (T1."No_" || ' - ' || 'SB fehlt' || ' - ' || T9."Name" || ' - ' || (asciiz(extract(YEAR FROM T1."Posting Date"),4) || '-' || asciiz(extract(MONTH FROM T1."Posting Date"),2) || '-' || asciiz(extract(DAY FROM T1."Posting Date"),2))) ELSE ('Rechnungen älter 30 Tage') END as c75,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 4) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" || ' ' || T7."Last Name") ELSE (T6."First Name" || ' ' || T6."Last Name") END) IS NOT NULL)) THEN (T1."No_" || ' - ' || (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" || ' ' || T7."Last Name") ELSE (T6."First Name" || ' ' || T6."Last Name") END) || ' - ' || T9."Name") WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 4) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" || ' ' || T7."Last Name") ELSE (T6."First Name" || ' ' || T6."Last Name") END) IS NULL)) THEN (T1."No_" || ' - ' || T9."Name") ELSE null END as c76,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 4) THEN ((CASE WHEN (T3."Service Order No_" IS NULL) THEN ('Gutschrift ohne Auftrag') ELSE (T3."Service Order No_") END)) ELSE null END as c77,
	   T10."No_" as c78,
	   T10."Name" as c79,
	   T11."Description" as c80,
	   CASE WHEN (T1."Sell-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T11."Description") END as c81,
	   T10."No_" || ' - ' || T10."Name" as c82,
	   T12."Duration_Time_Clock" as c83,
	   T12."Monteur" as c84,
	   T12."Monteur" as c85,
	   T12."Duration_Time_Clock" * 12 as c86,
	   '1' as c87,
	   (CASE WHEN (T1."Location Code" IN ('01BSPKW')) THEN ('10') WHEN (T1."Location Code" IN ('02BSMOT')) THEN ('20') WHEN (T1."Location Code" IN ('03RHF')) THEN ('30') WHEN (T1."Location Code" IN ('04SFH')) THEN ('40') WHEN (T1."Location Code" IN ('05WT')) THEN ('50') WHEN (T1."Location Code" IN ('06BI')) THEN ('26') WHEN (T1."Location Code" IN ('07TR')) THEN ('70') ELSE null END) as c88,
	   'Serviceberater' as c89,
	   T1."Shortcut Dimension 1 Code" as c90,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 100) THEN ((CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END) || ' - ' || T9."Name") ELSE ('Aufträge älter 100 Tage') END as c91,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 100) THEN (T1."No_" || ' - ' || T9."Name") ELSE ('Aufträge älter 100 Tage') END as c92,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 30) THEN ((CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END)) ELSE ('Aufträge älter 30 Tage') END as c93,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 30) THEN (T1."No_" || ' - ' || (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" || ' ' || T7."Last Name") ELSE (T6."First Name" || ' ' || T6."Last Name") END) || ' - ' || T9."Name") ELSE ('Rechnungen älter 30 Tage') END as c94,
	   T1."Gen_ Bus_ Posting Group" as c95,
	   T9."Name" as c96,
	   T2."VIN" as c97,
	   T2."Model" as c98,
	   T3."Gen_ Bus_ Posting Group" as c99,
	   CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END as c100,
	   (CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 as c101,
	   CASE WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 0.01 AND 0.99) THEN ('1') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 1.00 AND 1.99) THEN ('2') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 2.00 AND 2.99) THEN ('3') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 3.00 AND 3.99) THEN ('4') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 4.00 AND 4.99) THEN ('5') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 5.00 AND 5.99) THEN ('6') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 6.00 AND 6.99) THEN ('7') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 7.00 AND 7.99) THEN ('8') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 8.00 AND 8.99) THEN ('9') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 9.00 AND 9.99) THEN ('10') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 > 9.99) THEN ('> 10') WHEN ((CASE WHEN (T3."Registration Date" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 = 0) THEN ('keine Angabe') ELSE null END as c102,
	   CASE WHEN (T1."Gen_ Bus_ Posting Group" LIKE '%LOHN%') THEN (T1."Gen_ Bus_ Posting Group") ELSE null END as c103,
	   'Gutschrift' as c104,
	   '' as c105,
	   '' as c106,
	   '' as c107,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 60) THEN ((CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END)) ELSE ('Aufträge älter 60 Tage') END as c108
from QSS."C:\GlobalCube\System\ARI\IQD\Service\Add_Service_ledger_mit_Time_Clock_Entry_fuer_Service_Rg_Ausg_ims.ims" T12,
	((((("ARI"."import"."Sales Credit Memo Header" T1 left outer join "ARI"."import"."Vehicle" T2 on T1."Supply VIN" = T2."VIN") left outer join "ARI"."import"."Customer" T9 on T1."Bill-to Customer No_" = T9."No_") left outer join "ARI"."import"."Customer Group" T8 on T9."Customer Group Code" = T8."Code") left outer join "ARI"."import"."Customer" T10 on T10."No_" = T1."Sell-to Customer No_") left outer join "ARI"."import"."Customer Group" T11 on T10."Customer Group Code" = T11."Code"),
	(((("ARI"."import"."Sales Credit Memo Line" T3 left outer join "ARI"."import"."Archived Service Header" T4 on T4."No_" = T3."Service Order No_") left outer join "ARI"."import"."Service Header" T5 on T5."No_" = T3."Service Order No_") left outer join "ARI"."import"."Employee" T6 on T4."Service Advisor No_" = T6."No_") left outer join "ARI"."import"."Employee" T7 on T5."Service Advisor No_" = T7."No_")
where (T1."No_" = T3."Document No_") and ((T12."Document No_" = T1."No_") and (T12."Order No_" = T1."Service Order No_"))
and ((((T1."No_" LIKE 'W%') or (T1."No_" LIKE '%I%')) and (not T3."Type" IN (0,11,12))) and (T1."Posting Date" >= TIMESTAMP '2022-01-01 00:00:00.000'))
order by c1 asc

END SQL
COLUMN,0,No
COLUMN,1,Sell-to Customer No
COLUMN,2,Bill-to Customer No
COLUMN,3,Bill-to Name
COLUMN,4,Bill-to Address
COLUMN,5,Bill-to City
COLUMN,6,Posting Date
COLUMN,7,Payment Terms Code
COLUMN,8,Location Code
COLUMN,9,Department Code
COLUMN,10,Make Code
COLUMN,11,Customer Posting Group
COLUMN,12,Price Group Code
COLUMN,13,Prices Including Vat
COLUMN,14,Allow Quantity Disc
COLUMN,15,Salesperson Code
COLUMN,16,On Hold
COLUMN,17,Gen Bus Posting Group
COLUMN,18,Transaction Type
COLUMN,19,Sell-to Customer Name
COLUMN,20,Sell-to Address
COLUMN,21,Sell-to City
COLUMN,22,Correction
COLUMN,23,Document Date
COLUMN,24,External Document No
COLUMN,25,Area
COLUMN,26,No Series
COLUMN,27,User Id
COLUMN,28,Order Type
COLUMN,29,Service Order No_ohne_Einschränkung
COLUMN,30,Branch Code
COLUMN,31,Vin
COLUMN,32,Model Code
COLUMN,33,Model No
COLUMN,34,Model_ori
COLUMN,35,Document No
COLUMN,36,Department Code
COLUMN,37,Make Code
COLUMN,38,Gen Bus Posting Group
COLUMN,39,Order No
COLUMN,40,Vin
COLUMN,41,Vehicle Status
COLUMN,42,Registration Date
COLUMN,43,Mileage
COLUMN,44,Service Order No_
COLUMN,45,Customer Group Code
COLUMN,46,Service Advisor No_Archiv
COLUMN,47,Service Advisor No_oA
COLUMN,48,No_für_Archiv
COLUMN,49,First Name_für_Archiv
COLUMN,50,Last Name_für_Archiv
COLUMN,51,No
COLUMN,52,First Name
COLUMN,53,Last Name
COLUMN,54,Serviceberater
COLUMN,55,Hauptbetrieb
COLUMN,56,Standort
COLUMN,57,Umsatzart
COLUMN,58,Fabrikat
COLUMN,59,Model
COLUMN,60,Fahrzeug
COLUMN,61,Marke
COLUMN,62,Service Posting Group_für_Archiv
COLUMN,63,Service Posting Group
COLUMN,64,Auftragsart
COLUMN,65,Cust_Gr_Code
COLUMN,66,Cust_Gr_Description
COLUMN,67,Kundenart
COLUMN,68,Cust_No
COLUMN,69,Cust_Name
COLUMN,70,Kunde
COLUMN,71,Auftragsart_1
COLUMN,72,Invoice Date
COLUMN,73,Order Number
COLUMN,74,Order Number_Rg_Ausg
COLUMN,75,Order Number_Rg_Ausg_2
COLUMN,76,Order Number_Rg_Ausg_1
COLUMN,77,Cust_No_Verkaufskunde
COLUMN,78,Cust_Name_Verkaufskunde
COLUMN,79,Cust_Group_Description_Verkaufskunde
COLUMN,80,Kundenart_Verkaufskunde
COLUMN,81,Kunde_Verkaufskunde
COLUMN,82,Duration Time Clock_Add_Service_Time_Clock_ims
COLUMN,83,Monteur
COLUMN,84,Auftragsposition
COLUMN,85,ben. AW_Time_Clock
COLUMN,86,Rechtseinheit_ID
COLUMN,87,Standort_ID
COLUMN,88,Zuordnung_Funktion
COLUMN,89,Cost_Centre_ID
COLUMN,90,Order_Desc_100
COLUMN,91,Invoice_Desc_100
COLUMN,92,Order_Desc_30
COLUMN,93,Invoice_Desc_30
COLUMN,94,Customer_Group_Owner
COLUMN,95,Customer_Name_Owner
COLUMN,96,Fahrgestellnummer
COLUMN,97,Model_Desc
COLUMN,98,Produktbuchungsgruppe
COLUMN,99,Fahrzeugalter_Tage
COLUMN,100,Fahrzeugalter
COLUMN,101,FZG-Altersstaffel
COLUMN,102,Repair_Group_Desc
COLUMN,103,Rechnung_Gutschrift
COLUMN,104,Parts_Focus_Group
COLUMN,105,Parts_Make_Desc
COLUMN,106,Parts_Group_Desc
COLUMN,107,Service Order No