COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\SYSTEM\ARIntelligence\IQD\service\Service_Ausgangsrechnung_ab_2011_ben_AW.imr
TITLE,Service_Ausgangsrechnung_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."Order Date" as c7,
	   T1."Posting Date" as c8,
	   T1."Payment Terms Code" as c9,
	   T1."Location Code" as c10,
	   T1."Shortcut Dimension 1 Code" as c11,
	   T1."Shortcut Dimension 2 Code" as c12,
	   T1."Customer Posting Group" as c13,
	   T1."Price Group Code" as c14,
	   T1."Prices Including VAT" as c15,
	   T1."Allow Quantity Disc_" as c16,
	   T1."Salesperson Code" as c17,
	   T1."Order No_" as c18,
	   T1."On Hold" as c19,
	   T1."Gen_ Bus_ Posting Group" as c20,
	   T1."Transaction Type" as c21,
	   T1."Sell-to Customer Name" as c22,
	   T1."Sell-to Address" as c23,
	   T1."Sell-to City" as c24,
	   T1."Correction" as c25,
	   T1."Document Date" as c26,
	   T1."External Document No_" as c27,
	   T1."Area" as c28,
	   T1."Shipping Agent Code" as c29,
	   T1."No_ Series" as c30,
	   T1."Order No_ Series" as c31,
	   T1."User ID" as c32,
	   T1."Order Type" as c33,
	   T1."Service Order No_" as c34,
	   T1."Customer Group Code" as c35,
	   T1."Branch Code" as c36,
	   '' as c37,
	   '' as c38,
	   T2."Document No_" as c39,
	   T2."Shortcut Dimension 1 Code" as c40,
	   T2."Shortcut Dimension 2 Code" as c41,
	   T2."Order No_" as c42,
	   T2."Order Type" as c43,
	   T2."VIN" as c44,
	   T2."Vehicle Status" as c45,
	   T2."Registration Date" as c46,
	   T2."Mileage" as c47,
	   T2."Customer Group Code" as c48,
	   T3."Service Advisor No_" as c49,
	   T4."Service Advisor No_" as c50,
	   T5."No_" as c51,
	   T5."First Name" as c52,
	   T5."Last Name" as c53,
	   T6."No_" as c54,
	   T6."First Name" as c55,
	   T6."Last Name" as c56,
	   CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" || ' ' || T6."Last Name") ELSE (T5."First Name" || ' ' || T5."Last Name") END as c57,
	   '1' as c58,
	   CASE WHEN (T1."Location Code" = 'LBS') THEN ('10') WHEN (T1."Location Code" = 'WLS') THEN ('20') ELSE null END as c59,
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') ELSE ('Extern') END as c60,
	   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 c61,
	   '' as c62,
	   '' || ' - ' || '' as c63,
	   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 c64,
	   T3."Service Posting Group" as c65,
	   T4."Service Posting Group" as c66,
	   CASE WHEN (T3."Service Posting Group" IS NULL) THEN (T4."Service Posting Group") ELSE (T3."Service Posting Group") END as c67,
	   T7."Code" as c68,
	   T7."Description" as c69,
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T7."Description") END as c70,
	   T8."No_" as c71,
	   T8."Name" as c72,
	   T8."No_" || ' - ' || T8."Name" as c73,
	   '' as c74,
	   '' as c75,
	   '' as c76,
	   T1."Posting Date" as c77,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 180) THEN (T1."No_" || ' - ' || T1."Service Order No_" || ' - ' || T8."Name") ELSE ('Aufträge älter 180 Tage') END as c78,
	   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 c79,
	   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 (T3."Service Posting Group" IS NULL) THEN (T4."Service Posting Group") ELSE (T3."Service Posting Group") END) || ' - ' || (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_" || ' - ' || (CASE WHEN (T3."Service Posting Group" IS NULL) THEN (T4."Service Posting Group") ELSE (T3."Service Posting Group") END) || ' - ' || T8."Name") ELSE null END as c80,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 4) THEN (T1."Service Order No_") ELSE null END as c81,
	   T3."Gen_ Prod_ Posting Group" as c82,
	   T9."No_" as c83,
	   T9."Name" as c84,
	   T10."Description" as c85,
	   CASE WHEN (T1."Sell-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T10."Description") END as c86,
	   T9."No_" || ' - ' || T9."Name" as c87,
	   T11."Duration_Time_Clock" as c88,
	   T11."Monteur" as c89,
	   T11."Monteur" as c90,
	   T11."Duration_Time_Clock" * 12 as c91
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 Invoice Header" T1 left outer join "Automag7x"."dbo"."Automag GmbH$Customer" T8 on T8."No_" = T1."Bill-to Customer 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 Invoice Line" T2 left outer join "Automag7x"."dbo"."Automag GmbH$Archived Service Header" T3 on T2."Service Order No_" = T3."No_") left outer join "Automag7x"."dbo"."Automag GmbH$Service Header" T4 on T2."Service Order No_" = T4."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 ((T1."No_" = T11."Document No_") and (T1."Service Order No_" = T11."Order No_"))
and (((((T1."No_" LIKE 'W%') and (not T2."Type" IN (0,11,12))) and (T1."Posting Date" >= TIMESTAMP '2022-01-01 00:00:00.000')) and (not T1."Service Order No_" IN ('NASISPA'))) 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,Order Date
COLUMN,7,Posting Date
COLUMN,8,Payment Terms Code
COLUMN,9,Location Code
COLUMN,10,Department Code
COLUMN,11,Make Code
COLUMN,12,Customer Posting Group
COLUMN,13,Price Group Code
COLUMN,14,Prices Including Vat
COLUMN,15,Allow Quantity Disc
COLUMN,16,Salesperson Code
COLUMN,17,Order No
COLUMN,18,On Hold
COLUMN,19,Gen Bus Posting Group
COLUMN,20,Transaction Type
COLUMN,21,Sell-to Customer Name
COLUMN,22,Sell-to Address
COLUMN,23,Sell-to City
COLUMN,24,Correction
COLUMN,25,Document Date
COLUMN,26,External Document No
COLUMN,27,Area
COLUMN,28,Shipping Agent Code
COLUMN,29,No Series
COLUMN,30,Order No Series
COLUMN,31,User Id
COLUMN,32,Order Type
COLUMN,33,Service Order No
COLUMN,34,Customer Group Code
COLUMN,35,Branch Code
COLUMN,36,Vin
COLUMN,37,Model_ori
COLUMN,38,Document No
COLUMN,39,Department Code
COLUMN,40,Make Code
COLUMN,41,Order No
COLUMN,42,Order Type
COLUMN,43,Vin
COLUMN,44,Vehicle Status
COLUMN,45,Registration Date
COLUMN,46,Mileage
COLUMN,47,Customer Group Code
COLUMN,48,Service Advisor No_Archiv
COLUMN,49,Service Advisor No_oA
COLUMN,50,No_für_Archiv
COLUMN,51,First Name_für_Archiv
COLUMN,52,Last Name_für_Archiv
COLUMN,53,No
COLUMN,54,First Name
COLUMN,55,Last Name
COLUMN,56,Serviceberater
COLUMN,57,Hauptbetrieb
COLUMN,58,Standort
COLUMN,59,Umsatzart
COLUMN,60,Fabrikat
COLUMN,61,Model
COLUMN,62,Fahrzeug
COLUMN,63,Marke
COLUMN,64,Service Posting Group_für_Archiv
COLUMN,65,Service Posting Group
COLUMN,66,Auftragsart
COLUMN,67,Cust_Gr_Code
COLUMN,68,Cust_Gr_Description
COLUMN,69,Kundenart
COLUMN,70,Cust_No
COLUMN,71,Cust_Name
COLUMN,72,Kunde
COLUMN,73,Auftragsart_1
COLUMN,74,Function Code
COLUMN,75,Monteur
COLUMN,76,Invoice Date
COLUMN,77,Order Number
COLUMN,78,Order Number_Rg_Ausg
COLUMN,79,Order Number_Rg_Ausg_2
COLUMN,80,Order Number_Rg_Ausg_1
COLUMN,81,Gen Prod Posting Group
COLUMN,82,Cust_No_Verkaufskunde
COLUMN,83,Cust_Name_Verkaufskunde
COLUMN,84,Cust_Group_Description_Verkaufskunde
COLUMN,85,Kundenart_Verkaufskunde
COLUMN,86,Kunde_Verkaufskunde
COLUMN,87,Duration Time Clock_Add_Service_Time_Clock_ims
COLUMN,88,Monteur
COLUMN,89,Auftragsposition
COLUMN,90,ben. AW_Time_Clock