C11/System/AUTOLINE/IQD/Serv_Teile/Auftraege_Teile_offen.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_AUTOLINE
DATASOURCENAME,C:\GlobalCube\System\Autoline\IQD\serv_teile\Auftraege_Teile_offen.imr
TITLE,Auftraege_Teile_offen.imr
BEGIN SQL
select T1."Client_DB" || '_' || (rtrim((cast_numberToString(cast_integer(T1."WIPNumber"))))) as c1,
	   T1."WIPNumber" as c2,
	   T1."DateCreated" as c3,
	   T1."Registration" as c4,
	   T1."AccountNo" as c5,
	   T1."SaleType" as c6,
	   T1."CustomerName" as c7,
	   T1."MakeModel" as c8,
	   T1."DiscountCategory" as c9,
	   T1."ChassisNumber" as c10,
	   T1."Department" as c11,
	   T1."DateFirstReg" as c12,
	   T1."FranchiseCode" as c13,
	   T1."DateDueIn" as c14,
	   T1."TimeIn" as c15,
	   T1."DateDueOut" as c16,
	   T1."TimeOut" as c17,
	   T1."DeleteFlag" as c18,
	   T1."CustomerDiscType" as c19,
	   T1."InvoiceNumber" as c20,
	   T1."CompanyNumber" as c21,
	   T1."DateUpdated" as c22,
	   T1."MagicMKVehicle" as c23,
	   T1."JobNumber" as c24,
	   T1."MagicNumber001" as c25,
	   T1."MagicNumber002" as c26,
	   T1."MagicNumber003" as c27,
	   T1."SellingDealer" as c28,
	   T1."RetentionType" as c29,
	   T1."WIPLabour" as c30,
	   T1."WIPParts" as c31,
	   T1."WLDateIn" as c32,
	   T1."WLDateOut" as c33,
	   T1."CustomerContact" as c34,
	   T1."MagicMKTarget" as c35,
	   T1."MagicMKCompany" as c36,
	   T1."DateDeleteFlagSet" as c37,
	   T1."InvoicableValue" as c38,
	   T1."BookingStatus" as c39,
	   T1."CreatingOperator" as c40,
	   T1."BookedInOperator" as c41,
	   T1."InvoicingOperator" as c42,
	   T1."BookedOutOperator" as c43,
	   T1."OwningOperator" as c44,
	   T1."Client_DB" as c45,
	   T2."UserID" as c46,
	   T2."UserName" as c47,
	   T2."JobTitle" as c48,
	   T3."FranchiseCode" as c49,
	   T3."FullName001" as c50,
	   T3."FullName002" as c51,
	   T3."FullName003" as c52,
	   T4."LocationCode" as c53,
	   T4."Description" as c54,
	   T4."LocationName" as c55,
	   CASE WHEN (T1."Client_DB" BETWEEN '10' AND '19') THEN ('10') WHEN (T1."Client_DB" BETWEEN '20' AND '29') THEN ('20') WHEN (T1."Client_DB" BETWEEN '30' AND '39') THEN ('30') WHEN (T1."Client_DB" BETWEEN '40' AND '49') THEN ('40') WHEN (T1."Client_DB" BETWEEN '50' AND '59') THEN ('50') ELSE null END as c56,
	   '' as c57,
	   T4."LocationCode" as c58,
	   T4."LocationCode" || ' - ' || T4."Description" as c59,
	   'Serviceberater' as c60,
	   CASE WHEN (T2."UserName" IS NULL) THEN ('n.N.') ELSE (T2."UserName") END as c61,
	   CASE WHEN (T5."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T5."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T5."Franchise" = 'Z') THEN ('Fremd') WHEN (T5."Franchise" = 'S') THEN ('Smart') WHEN (T5."Franchise" = 'F') THEN ('Fremd TRAPO') WHEN ((T5."Franchise" IS NULL) and (T1."FranchiseCode" = 'M')) THEN ('Mercedes PKW') ELSE null END as c62,
	   (od_left((ucase(T5."Description")),4)) as c63,
	   T1."ChassisNumber" as c64,
	   (CASE WHEN (T5."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T5."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T5."Franchise" = 'Z') THEN ('Fremd') WHEN (T5."Franchise" = 'S') THEN ('Smart') WHEN (T5."Franchise" = 'F') THEN ('Fremd TRAPO') WHEN ((T5."Franchise" IS NULL) and (T1."FranchiseCode" = 'M')) THEN ('Mercedes PKW') ELSE null END) as c65,
	   CASE WHEN (T1."Department" = 'P') THEN ('Teile') ELSE ('Service') END as c66,
	   CASE WHEN (T1."SaleType" IN ('W')) THEN ('GWL') WHEN (T1."SaleType" IN ('I')) THEN ('Intern') ELSE ('Extern') END as c67,
	   T1."AccountNo" || ' - ' || T1."CustomerName" as c68,
	   T1."SaleType" as c69,
	   '' as c70,
	   T6."DocumentNumber" as c71,
	   T6."DocumentDate" as c72,
	   CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END as c73,
	   CASE WHEN ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END as c74,
	   CASE WHEN ((extract(DAY FROM (now()) - (CASE WHEN ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END))) <= 30) THEN ((T1."Client_DB" || '_' || (rtrim((cast_numberToString(cast_integer(T1."WIPNumber"))))))) ELSE ('Aufträge älter 30 Tage') END as c75,
	   CASE WHEN ((extract(DAY FROM (now()) - (CASE WHEN ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END))) <= 30) THEN ((rtrim((cast_numberToString(cast_integer(T7."InvoiceNumber"))))) || ' - ' || (CASE WHEN (T2."UserName" IS NULL) THEN ('n.N.') ELSE (T2."UserName") END) || ' - ' || (T1."AccountNo" || ' - ' || T1."CustomerName") || ' - ' || T1."ChassisNumber") ELSE ('Rechnungen älter 30 Tage') END as c76,
	   CASE WHEN ((extract(DAY FROM (now()) - (CASE WHEN ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END))) <= 100) THEN ((T1."Client_DB" || '_' || (rtrim((cast_numberToString(cast_integer(T1."WIPNumber")))))) || ' - ' || (T1."AccountNo" || ' - ' || T1."CustomerName") || ' - ' || T1."ChassisNumber") ELSE ('Aufträge älter 100 Tage') END as c77,
	   CASE WHEN ((extract(DAY FROM (now()) - (CASE WHEN ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END))) <= 100) THEN ((rtrim((cast_numberToString(cast_integer(T7."InvoiceNumber"))))) || ' - ' || (T1."AccountNo" || ' - ' || T1."CustomerName") || ' - ' || T1."ChassisNumber") ELSE ('Rechnungen älter 100 Tage') END as c78,
	   CASE WHEN ((extract(DAY FROM (now()) - (CASE WHEN ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END))) <= 30) THEN ((rtrim((cast_numberToString(cast_integer(T7."LineNumber"))))) || ' - ' || T7."PartNumberPacked" || ' - ' || T7."Description") ELSE ('Auftragspositionen älter 30 Tage') END as c79,
	   ('Teile') as c80,
	   T7."AnalysisCode" as c81,
	   '' as c82,
	   '' as c83,
	   CASE WHEN ((CASE WHEN ((CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END) > 0) THEN ((CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END) - (CASE WHEN ((('Teile')) IN ('Teile')) THEN ((cast_float(T7."OrderQuantity" * T7."CostPrice"))) ELSE (0) END)) ELSE (0) END) >= 0) THEN ('VK > EK') ELSE ('VK < EK') END as c84,
	   'Rechnung' as c85,
	   '' as c86,
	   '' as c87,
	   '' as c88,
	   T8."Postcode" as c89,
	   (od_left(T8."Postcode",1)) as c90,
	   (od_left(T8."Postcode",2)) as c91,
	   (od_left(T8."Postcode",3)) as c92,
	   (od_left(T8."Postcode",4)) as c93,
	   T8."Postcode" as c94,
	   CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END as c95,
	   CASE WHEN ((('Teile')) IN ('Sonst.','W - Waschen','B - Kraftstoffe','D - Kleinmat.')) THEN (T7."ExtendedPrice") ELSE (0) END as c96,
	   (cast_float(T7."OrderQuantity" * T7."SellingPrice" - T7."ExtendedPrice")) as c97,
	   CASE WHEN ((('Teile')) IN ('Teile')) THEN ((cast_float(T7."OrderQuantity" * T7."CostPrice"))) ELSE (0) END as c98,
	   CASE WHEN ((('Teile')) IN ('Sonst.','W - Waschen','B - Kraftstoffe','D - Kleinmat.')) THEN ((cast_float(T7."OrderQuantity" * T7."CostPrice"))) ELSE (0) END as c99,
	   CASE WHEN ((CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END) > 0) THEN ((CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END) - (CASE WHEN ((('Teile')) IN ('Teile')) THEN ((cast_float(T7."OrderQuantity" * T7."CostPrice"))) ELSE (0) END)) ELSE (0) END as c100,
	   T7."PartNumberPacked" as c101,
	   T7."AccountNo" as c102,
	   T7."OrderQuantity" as c103,
	   T7."SupplyQuantity" as c104,
	   T7."ToFollowQuantity" as c105,
	   T7."ProcessedFlag" as c106,
	   T7."OriginalDiscount" as c107,
	   T7."VATCode" as c108,
	   T7."OrderStatus" as c109,
	   T7."RetailPrice" as c110,
	   T7."DiscountCategory" as c111,
	   T7."BinLocation" as c112,
	   T7."AnalysisCode" as c113,
	   T7."Surcharge" as c114,
	   T7."Description" as c115,
	   T7."MenuPartFlag" as c116,
	   T7."Salesman" as c117,
	   T7."PriceListNo" as c118,
	   T7."CostPrice" as c119,
	   T7."SellingPrice" as c120,
	   T7."DiscountPercent" as c121,
	   T7."PartsDiscountCode" as c122,
	   T7."ReturnsCategory" as c123,
	   T7."UnitIssue" as c124,
	   T7."DateInserted" as c125,
	   T7."InvoiceStatus" as c126,
	   T7."AdvicePONumber" as c127,
	   T7."ProductGroup" as c128,
	   T7."DateRequired" as c129,
	   T7."LineNumber" as c130,
	   T7."SaleType" as c131,
	   T7."WIPNumber" as c132,
	   T7."PartNumber" as c133,
	   T7."SupplierAccount" as c134,
	   T7."InvoiceNumber" as c135,
	   T7."DateLastEditted" as c136,
	   T7."PostedToWarranty" as c137,
	   T7."WarrantyCode" as c138,
	   T7."TransferLineNumber" as c139,
	   T7."DepartmentCode" as c140,
	   T7."VehicleMagic" as c141,
	   T7."QuantityCredited" as c142,
	   T7."LastCreditNote" as c143,
	   T7."SOLoghdMagic001" as c144,
	   T7."SOLoghdMagic002" as c145,
	   T7."TargetMagicNumber" as c146,
	   T7."CompanyMagicNumber" as c147,
	   T7."WarrantyInformation" as c148,
	   T7."ExtendedPrice" as c149,
	   T7."ActualLineNoSort" as c150,
	   T7."UserId" as c151,
	   T7."Client_DB" as c152,
	   T1."DateCreated" as c153,
	   (T1."Client_DB" || '_' || (rtrim((cast_numberToString(cast_integer(T1."WIPNumber")))))) || '/' || T1."BookingStatus" || ' - ' || (CASE WHEN (T2."UserName" IS NULL) THEN ('n.N.') ELSE (T2."UserName") END) || ' - ' || (T1."AccountNo" || ' - ' || T1."CustomerName") || ' - ' || T1."ChassisNumber" as c154,
	   (extract(DAY FROM (now()) - T1."DateCreated")) as c155,
	   T5."Franchise" as c156
from ((((("AUTOLINE"."import"."SO_HeaderRecords" T1 left outer join "AUTOLINE"."import"."GB_UserDetails" T2 on ((cast_float(T1."BookedInOperator")) = T2."POSOperator") and ((T1."Department" = T2."CRMDepartment") or (T2."POSOperator" = 888))) left outer join "AUTOLINE"."import"."DD_FranchiseRecords" T3 on T1."FranchiseCode" = T3."FranchiseCode") left outer join "AUTOLINE"."import"."GB_CompanyLocations" T4 on T4."LocationCode" = T1."CompanyNumber") left outer join "AUTOLINE"."import"."MK_VehicleRecords" T5 on T5."VehicleNumber" = T1."MagicMKVehicle") left outer join "AUTOLINE"."import"."SL_SalesLedgerAccounts" T8 on (T1."AccountNo" = T8."AccountCode") and (T8."Client_DB" = '10')),
	("AUTOLINE"."import"."SO_PartsLines" T7 left outer join "AUTOLINE"."import"."SL_SLTransactions" T6 on ((T6."DocumentNumber" = T7."InvoiceNumber") and (T6."TruePostingOrigin" = T7."Client_DB")) and (T6."AccountCode" = T7."AccountNo"))
where ((T1."WIPNumber" = T7."WIPNumber") and (T1."Client_DB" = T7."Client_DB"))
and ((((T1."DateCreated" >= TIMESTAMP '2020-01-01 00:00:00.000') and (T7."InvoiceStatus" <> 'X')) and (T1."InvoiceNumber" = 0)) and (not T1."BookingStatus" IN ('M')))
order by c1 asc,c2 asc

END SQL
COLUMN,0,Order_Number_eindeutig
COLUMN,1,Wipnumber
COLUMN,2,Datecreated
COLUMN,3,Registration
COLUMN,4,Accountno
COLUMN,5,Saletype
COLUMN,6,Customername
COLUMN,7,Makemodel
COLUMN,8,Discountcategory
COLUMN,9,Chassisnumber
COLUMN,10,Department
COLUMN,11,Datefirstreg
COLUMN,12,Franchisecode
COLUMN,13,Dateduein
COLUMN,14,Timein
COLUMN,15,Datedueout
COLUMN,16,Timeout
COLUMN,17,Deleteflag
COLUMN,18,Customerdisctype
COLUMN,19,Invoicenumber
COLUMN,20,Companynumber
COLUMN,21,Dateupdated
COLUMN,22,Magicmkvehicle
COLUMN,23,Jobnumber
COLUMN,24,Magicnumber001
COLUMN,25,Magicnumber002
COLUMN,26,Magicnumber003
COLUMN,27,Sellingdealer
COLUMN,28,Retentiontype
COLUMN,29,Wiplabour
COLUMN,30,Wipparts
COLUMN,31,Wldatein
COLUMN,32,Wldateout
COLUMN,33,Customercontact
COLUMN,34,Magicmktarget
COLUMN,35,Magicmkcompany
COLUMN,36,Datedeleteflagset
COLUMN,37,Invoicablevalue
COLUMN,38,Bookingstatus
COLUMN,39,Creatingoperator
COLUMN,40,Bookedinoperator
COLUMN,41,Invoicingoperator
COLUMN,42,Bookedoutoperator
COLUMN,43,Owningoperator
COLUMN,44,Client Db
COLUMN,45,Userid
COLUMN,46,Username
COLUMN,47,Jobtitle
COLUMN,48,Franchisecode
COLUMN,49,Fullname001
COLUMN,50,Fullname002
COLUMN,51,Fullname003
COLUMN,52,Locationcode
COLUMN,53,Description
COLUMN,54,Locationname
COLUMN,55,Hauptbetrieb_ID
COLUMN,56,Hauptbetrieb_Name
COLUMN,57,Standort_ID
COLUMN,58,Standort_Name
COLUMN,59,Zuordnung_Funktion
COLUMN,60,Serviceberater
COLUMN,61,Fabrikat
COLUMN,62,Model
COLUMN,63,Fahrzeug
COLUMN,64,Marke
COLUMN,65,Auftragsart
COLUMN,66,Umsatzart
COLUMN,67,Kunde
COLUMN,68,Kundenart
COLUMN,69,Kostenstelle
COLUMN,70,Documentnumber_Sltransactions
COLUMN,71,Documentdate_Sltransactions
COLUMN,72,Invoice Date_ori
COLUMN,73,Invoice Date
COLUMN,74,Order_Desc_30
COLUMN,75,Invoice_Desc_30
COLUMN,76,Order_Desc_100
COLUMN,77,Invoice_Desc_100
COLUMN,78,Auftragsposition
COLUMN,79,Zuordnung_Produktbuchungsgruppe
COLUMN,80,Produktbuchungsgruppe
COLUMN,81,FZG-Altersstaffel
COLUMN,82,Repair_Group_Desc
COLUMN,83,DB1_><_EK
COLUMN,84,Rechnung_Gutschrift
COLUMN,85,Parts_Focus_Group
COLUMN,86,Parts_Make_Desc
COLUMN,87,Parts_Group_Desc
COLUMN,88,Postcode_Slsalesledgeraccount
COLUMN,89,PLZ_1_Stelle
COLUMN,90,PLZ_2_Stelle
COLUMN,91,PLZ_3_Stelle
COLUMN,92,PLZ_4_Stelle
COLUMN,93,PLZ
COLUMN,94,Teile
COLUMN,95,Sonst.
COLUMN,96,NL Teile
COLUMN,97,EW Teile
COLUMN,98,EW Sonst.
COLUMN,99,DB 1 Teile
COLUMN,100,Partnumberpacked
COLUMN,101,Accountno
COLUMN,102,Orderquantity
COLUMN,103,Supplyquantity
COLUMN,104,Tofollowquantity
COLUMN,105,Processedflag
COLUMN,106,Originaldiscount
COLUMN,107,Vatcode
COLUMN,108,Orderstatus
COLUMN,109,Retailprice
COLUMN,110,Discountcategory
COLUMN,111,Binlocation
COLUMN,112,Analysiscode
COLUMN,113,Surcharge
COLUMN,114,Description
COLUMN,115,Menupartflag
COLUMN,116,Salesman
COLUMN,117,Pricelistno
COLUMN,118,Costprice
COLUMN,119,Sellingprice
COLUMN,120,Discountpercent
COLUMN,121,Partsdiscountcode
COLUMN,122,Returnscategory
COLUMN,123,Unitissue
COLUMN,124,Dateinserted
COLUMN,125,Invoicestatus_Partslines
COLUMN,126,Adviceponumber
COLUMN,127,Productgroup
COLUMN,128,Daterequired
COLUMN,129,Linenumber
COLUMN,130,Saletype_Partslines
COLUMN,131,Wipnumber
COLUMN,132,Partnumber
COLUMN,133,Supplieraccount
COLUMN,134,Invoicenumber_Partslines
COLUMN,135,Datelasteditted
COLUMN,136,Postedtowarranty
COLUMN,137,Warrantycode
COLUMN,138,Transferlinenumber
COLUMN,139,Departmentcode
COLUMN,140,Vehiclemagic
COLUMN,141,Quantitycredited
COLUMN,142,Lastcreditnote
COLUMN,143,Sologhdmagic001
COLUMN,144,Sologhdmagic002
COLUMN,145,Targetmagicnumber
COLUMN,146,Companymagicnumber
COLUMN,147,Warrantyinformation
COLUMN,148,Extendedprice
COLUMN,149,Actuallinenosort
COLUMN,150,Userid
COLUMN,151,Client Db
COLUMN,152,Order Date
COLUMN,153,Order Number
COLUMN,154,Tage offen
COLUMN,155,Franchise_MK_Vehiclerecords