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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_ARI
DATASOURCENAME,C:\GlobalCube\System\ARI\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,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END as c34,
	   T1."Customer Group Code" as c35,
	   T1."Branch Code" as c36,
	   T2."VIN" as c37,
	   T2."Model" as c38,
	   T3."Document No_" as c39,
	   T3."Shortcut Dimension 1 Code" as c40,
	   T3."Shortcut Dimension 2 Code" as c41,
	   T3."Order No_" as c42,
	   T3."Order Type" as c43,
	   T3."VIN" as c44,
	   T3."Vehicle Status" as c45,
	   T3."Registration Date" as c46,
	   T3."Mileage" as c47,
	   T3."Customer Group Code" as c48,
	   T4."Service Advisor No_" as c49,
	   T5."Service Advisor No_" as c50,
	   T6."No_" as c51,
	   T6."First Name" as c52,
	   T6."Last Name" as c53,
	   T7."No_" as c54,
	   T7."First Name" as c55,
	   T7."Last Name" as c56,
	   CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" || ' ' || T7."Last Name") ELSE (T6."First Name" || ' ' || T6."Last Name") END as c57,
	   '1' as c58,
	   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 ('60') WHEN (T1."Location Code" IN ('07TR')) THEN ('70') ELSE null END as c59,
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') WHEN (T1."No_ Series" LIKE 'I%') THEN ('Intern') ELSE ('Extern') END as c60,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('ALPINA','BMW','BMW-ALPINA','BMW-C1','BMWI','BMW-MINI','BMW-MOT','TRIUMPH')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as c61,
	   T2."Model" as c62,
	   T2."VIN" || ' - ' || T2."Model" as c63,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('ALPINA','BMW','BMW-ALPINA','BMW-C1','BMWI','BMW-MINI','BMW-MOT','TRIUMPH')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as c64,
	   T4."Service Posting Group" as c65,
	   T5."Service Posting Group" as c66,
	   CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END as c67,
	   T8."Code" as c68,
	   T8."Description" as c69,
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T8."Description") END as c70,
	   T9."No_" as c71,
	   T9."Name" as c72,
	   T9."No_" || ' - ' || T9."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_" || ' - ' || (CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END) || ' - ' || T9."Name") ELSE ('Aufträge älter 180 Tage') END as c78,
	   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 c79,
	   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 (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END) || ' - ' || (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_" || ' - ' || (CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END) || ' - ' || T9."Name") ELSE null END as c80,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 4) THEN ((CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END)) ELSE null END as c81,
	   T10."No_" as c82,
	   T10."Name" as c83,
	   T11."Description" as c84,
	   CASE WHEN (T1."Sell-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T11."Description") END as c85,
	   T10."No_" || ' - ' || T10."Name" as c86,
	   T12."Duration_Time_Clock" as c87,
	   T12."Monteur" as c88,
	   T12."Monteur" as c89,
	   T12."Duration_Time_Clock" * 12 as c90,
	   (T12."Duration_Time_Clock" * 12) as c91,
	   '1' as c92,
	   (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 ('60') WHEN (T1."Location Code" IN ('07TR')) THEN ('70') ELSE null END) as c93,
	   'Serviceberater' as c94,
	   T1."Shortcut Dimension 1 Code" as c95,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 100) THEN ((CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END) || ' - ' || T9."Name") ELSE ('Aufträge älter 100 Tage') END as c96,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 100) THEN (T1."No_" || ' - ' || T9."Name") ELSE ('Rechnungen älter 100 Tage') END as c97,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 30) THEN ((CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END)) ELSE ('Aufträge älter 30 Tage') END as c98,
	   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 c99,
	   T1."Gen_ Bus_ Posting Group" as c100,
	   T9."Name" as c101,
	   T2."VIN" as c102,
	   T2."Model" as c103,
	   T1."Gen_ Bus_ Posting Group" as c104,
	   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 c105,
	   (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 c106,
	   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 c107,
	   CASE WHEN (T1."Gen_ Bus_ Posting Group" LIKE '%LOHN%') THEN (T1."Gen_ Bus_ Posting Group") ELSE null END as c108,
	   'Rechnung' as c109,
	   '' as c110,
	   '' as c111,
	   '' as c112
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 Invoice Header" T1 left outer join "ARI"."import"."Vehicle" T2 on T1."Supply VIN" = T2."VIN") left outer join "ARI"."import"."Customer" T9 on T9."No_" = T1."Bill-to Customer 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 Invoice Line" T3 left outer join "ARI"."import"."Archived Service Header" T4 on T3."Service Order No_" = T4."No_") left outer join "ARI"."import"."Service Header" T5 on T3."Service Order No_" = T5."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 ((T1."No_" = T12."Document No_") and (T1."Service Order No_" = T12."Order No_"))
and (((((od_left(T1."No_",1)) IN ('I','W')) and (not T3."Type" IN (0,11,12))) and (T1."Posting Date" >= TIMESTAMP '2024-03-01 00:00:00.000')) and (not (CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END) IN ('NASISPA')))
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,Cust_No_Verkaufskunde
COLUMN,82,Cust_Name_Verkaufskunde
COLUMN,83,Cust_Group_Description_Verkaufskunde
COLUMN,84,Kundenart_Verkaufskunde
COLUMN,85,Kunde_Verkaufskunde
COLUMN,86,Duration Time Clock_Add_Service_Time_Clock_ims
COLUMN,87,Monteur
COLUMN,88,Auftragsposition
COLUMN,89,ben. AW_Time_Clock
COLUMN,90,ben Zeit
COLUMN,91,Rechtseinheit_ID
COLUMN,92,Standort_ID
COLUMN,93,Zuordnung_Funktion
COLUMN,94,Cost_Centre_ID
COLUMN,95,Order_Desc_100
COLUMN,96,Invoice_Desc_100
COLUMN,97,Order_Desc_30
COLUMN,98,Invoice_Desc_30
COLUMN,99,Customer_Group_Owner
COLUMN,100,Customer_Name_Owner
COLUMN,101,Fahrgestellnummer
COLUMN,102,Model_Desc
COLUMN,103,Produktbuchungsgruppe
COLUMN,104,Fahrzeugalter_Tage
COLUMN,105,Fahrzeugalter
COLUMN,106,FZG-Altersstaffel
COLUMN,107,Repair_Group_Desc
COLUMN,108,Rechnung_Gutschrift
COLUMN,109,Parts_Focus_Group
COLUMN,110,Parts_Make_Desc
COLUMN,111,Parts_Group_Desc