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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\SYSTEM\ARIntelligence\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,
	   '' as c32,
	   '' as c33,
	   '' as c34,
	   '' as c35,
	   T2."Document No_" as c36,
	   T2."Shortcut Dimension 1 Code" as c37,
	   T2."Shortcut Dimension 2 Code" as c38,
	   T2."Gen_ Bus_ Posting Group" as c39,
	   T2."Order No_" as c40,
	   T2."VIN" as c41,
	   T2."Vehicle Status" as c42,
	   T2."Registration Date" as c43,
	   T2."Mileage" as c44,
	   CASE WHEN (T2."Service Order No_" IS NULL) THEN ('Gutschrift ohne Auftrag') ELSE (T2."Service Order No_") END as c45,
	   T2."Customer Group Code" as c46,
	   T3."Service Advisor No_" as c47,
	   T4."Service Advisor No_" as c48,
	   T5."No_" as c49,
	   T5."First Name" as c50,
	   T5."Last Name" as c51,
	   T6."No_" as c52,
	   T6."First Name" as c53,
	   T6."Last Name" as c54,
	   CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END as c55,
	   '1' as c56,
	   CASE WHEN (T1."Location Code" = 'LBS') THEN ('10') WHEN (T1."Location Code" = 'WLS') THEN ('20') ELSE null END as c57,
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') ELSE ('Extern') END as c58,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW','BMW I','BMWI','BMW-MINI','BMW-MOT')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as c59,
	   '' as c60,
	   '' || ' - ' || '' as c61,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW','BMW I','BMWI','BMW-MINI','BMW-MOT')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as c62,
	   T3."Service Posting Group" as c63,
	   T4."Service Posting Group" as c64,
	   CASE WHEN (T3."Service Posting Group" IS NULL) THEN (T4."Service Posting Group") ELSE (T3."Service Posting Group") END as c65,
	   T7."Code" as c66,
	   T7."Description" as c67,
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T7."Description") END as c68,
	   T8."No_" as c69,
	   T8."Name" as c70,
	   T8."No_" || ' - ' || T8."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) || ' - ' || T8."Name") ELSE ('Aufträge älter 180 Tage') END as c74,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END) IS NOT NULL)) THEN (T1."No_" || ' - ' || (CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END) || ' - ' || T8."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 (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END) IS NULL)) THEN (T1."No_" || ' - ' || 'SB fehlt' || ' - ' || T8."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 (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END) IS NOT NULL)) THEN (T1."No_" || ' - ' || (CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END) || ' - ' || T8."Name") WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 4) and ((CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END) IS NULL)) THEN (T1."No_" || ' - ' || T8."Name") ELSE null END as c76,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 4) THEN ((CASE WHEN (T2."Service Order No_" IS NULL) THEN ('Gutschrift ohne Auftrag') ELSE (T2."Service Order No_") END)) ELSE null END as c77,
	   T9."No_" as c78,
	   T9."Name" as c79,
	   T10."Description" as c80,
	   CASE WHEN (T1."Sell-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T10."Description") END as c81,
	   T9."No_" || ' - ' || T9."Name" as c82,
	   T11."Duration_Time_Clock" as c83,
	   T11."Monteur" as c84,
	   T11."Monteur" as c85,
	   T11."Duration_Time_Clock" * 12 as c86
from QSS."C:\GlobalCube\System\ARIntelligence\IQD\Service\Add_Service_ledger_mit_Time_Clock_Entry_fuer_Service_Rg_Ausg_ims.ims" T11,
	(((("Automag7x"."dbo"."Automag GmbH$Sales Credit Memo Header" T1 left outer join "Automag7x"."dbo"."Automag GmbH$Customer" T8 on T1."Bill-to Customer No_" = T8."No_") left outer join "Automag7x"."dbo"."Automag GmbH$Customer Group" T7 on T8."Customer Group Code" = T7."Code") left outer join "Automag7x"."dbo"."Automag GmbH$Customer" T9 on T9."No_" = T1."Sell-to Customer No_") left outer join "Automag7x"."dbo"."Automag GmbH$Customer Group" T10 on T9."Customer Group Code" = T10."Code"),
	(((("Automag7x"."dbo"."Automag GmbH$Sales Credit Memo Line" T2 left outer join "Automag7x"."dbo"."Automag GmbH$Archived Service Header" T3 on T3."No_" = T2."Service Order No_") left outer join "Automag7x"."dbo"."Automag GmbH$Service Header" T4 on T4."No_" = T2."Service Order No_") left outer join "Automag7x"."dbo"."Automag GmbH$Employee" T5 on T3."Service Advisor No_" = T5."No_") left outer join "Automag7x"."dbo"."Automag GmbH$Employee" T6 on T4."Service Advisor No_" = T6."No_")
where (T1."No_" = T2."Document No_") and ((T11."Document No_" = T1."No_") and (T11."Order No_" = T1."Service Order No_"))
and ((((T1."No_" LIKE 'WGG%') and (not T2."Type" IN (0,11,12))) and (T1."Posting Date" >= TIMESTAMP '2022-01-01 00:00:00.000')) and ((CASE WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END) IS NOT NULL)) THEN (T1."No_" || ' - ' || (CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END) || ' - ' || T8."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 (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END) IS NULL)) THEN (T1."No_" || ' - ' || 'SB fehlt' || ' - ' || T8."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) <> 'Rechnungen älter 30 Tage'))
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
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