C11/System/ARIntelligence/IQD/OP/OP_Service.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\GAPS_BMW\Portal\System\IQD\OP\OP_Service.imr
TITLE,OP_Service.imr
BEGIN SQL
select distinct c58 as c1,
	   c108 as c2,
	   c107 as c3,
	   c106 as c4,
	   c105 as c5,
	   c104 as c6,
	   c103 as c7,
	   c102 as c8,
	   c101 as c9,
	   c100 as c10,
	   c99 as c11,
	   c98 as c12,
	   c97 as c13,
	   c96 as c14,
	   c95 as c15,
	   c94 as c16,
	   c93 as c17,
	   c92 as c18,
	   c91 as c19,
	   c90 as c20,
	   c89 as c21,
	   c88 as c22,
	   c87 as c23,
	   c86 as c24,
	   c85 as c25,
	   c84 as c26,
	   c67 as c27,
	   c83 as c28,
	   c82 as c29,
	   c81 as c30,
	   c80 as c31,
	   c79 as c32,
	   c78 as c33,
	   c77 as c34,
	   c76 as c35,
	   c75 as c36,
	   c74 as c37,
	   c73 as c38,
	   c72 as c39,
	   c71 as c40,
	   '1' as c41,
	   c70 as c42,
	   XSUM(c71 for c58) as c43,
	   c69 as c44,
	   c68 as c45,
	   c67 as c46,
	   c66 as c47,
	   c65 as c48,
	   (@CURRENT_DATE) as c49,
	   c64 as c50,
	   c63 as c51,
	   c62 as c52,
	   c61 as c53,
	   c60 as c54,
	   c59 as c55
from 
(select T1."No_" as c58,
	   T2."Document No_" || ' - ' || T2."Description" || ' - ' || T2."Comment" || ' - ' || (CASE WHEN ((T4."Service Advisor No_" IS NOT NULL) or (T4."Service Advisor No_" <> '')) THEN (T4."Service Advisor No_" || ' - ' || T5."Name") ELSE ('n.N.') END) as c59,
	   CASE WHEN ((T4."Service Advisor No_" IS NOT NULL) or (T4."Service Advisor No_" <> '')) THEN (T4."Service Advisor No_" || ' - ' || T5."Name") ELSE ('n.N.') END as c60,
	   T5."Name" as c61,
	   T4."Service Advisor No_" as c62,
	   T3."Document No_" as c63,
	   CASE WHEN ((T2."Document No_" LIKE 'VRGF%') or (T2."Document No_" LIKE 'VGGF%')) THEN ('Verkauf') WHEN ((T2."Document No_" LIKE 'VRGT%') or (T2."Document No_" LIKE 'VGGT%')) THEN ('Teile') WHEN ((T2."Document No_" LIKE 'WRG%') or (T2."Document No_" LIKE 'WGG%')) THEN ('Service') WHEN (T2."Document No_" LIKE 'KABG%') THEN ('Kassenbuchungen') WHEN (T2."Document No_" LIKE 'D%') THEN ('Ford. Altsystem') ELSE ('Sonstige') END as c64,
	   T2."Document No_" || ' - ' || T2."Description" || ' - ' || T2."Comment" || ' - ' || T2."User ID" as c65,
	   T1."Name" || ' - ' || T1."No_" as c66,
	   T2."Due Date" as c67,
	   CASE WHEN (((extract(DAY FROM (now()) - T2."Due Date"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((extract(DAY FROM (now()) - T2."Due Date"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((extract(DAY FROM (now()) - T2."Due Date"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((extract(DAY FROM (now()) - T2."Due Date"))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((extract(DAY FROM (now()) - T2."Due Date"))) > 84) THEN ('> 12 Wochen') WHEN (((extract(DAY FROM (now()) - T2."Due Date"))) < 0) THEN ('noch nicht fällig') ELSE null END as c68,
	   (extract(DAY FROM (now()) - T2."Due Date")) as c69,
	   CASE WHEN (T2."Branch Code" = 'LBS') THEN ('10') ELSE null END as c70,
	   (cast_float(T2."Remaining Amount")) as c71,
	   T2."Comment" as c72,
	   T2."VIN" as c73,
	   T2."Main Area" as c74,
	   T2."Branch Code" as c75,
	   T2."Customer Group Code" as c76,
	   T2."No_ Series" as c77,
	   T2."Credit Amount" as c78,
	   T2."Debit Amount" as c79,
	   T2."Bal_ Account No_" as c80,
	   T2."Bal_ Account Type" as c81,
	   T2."Reason Code" as c82,
	   T2."Positive" as c83,
	   T2."Open" as c84,
	   T2."Applies-to Doc_ No_" as c85,
	   T2."Applies-to Doc_ Type" as c86,
	   T2."On Hold" as c87,
	   T2."Source Code" as c88,
	   T2."User ID" as c89,
	   T2."Salesperson Code" as c90,
	   T2."Make Code" as c91,
	   T2."Department Code" as c92,
	   T2."Customer Posting Group" as c93,
	   T2."Profit (LCY)" as c94,
	   T2."Sales (LCY)" as c95,
	   T2."Amount (LCY)" as c96,
	   T2."Remaining Amt_ (LCY)" as c97,
	   T2."Original Amount (LCY)" as c98,
	   T2."Remaining Amount" as c99,
	   T2."Amount" as c100,
	   T2."Currency Code" as c101,
	   T2."Description" as c102,
	   T2."Document No_" as c103,
	   T2."Document Type" as c104,
	   T2."Posting Date" as c105,
	   T2."Customer No_" as c106,
	   T2."Entry No_" as c107,
	   T1."Name" as c108
from (((("DMS1"."dbo"."Automag GmbH$Customer Ledger Entry" T2 left outer join "DMS1"."dbo"."Automag GmbH$Customer" T1 on T1."No_" = T2."Customer No_") left outer join "DMS1"."dbo"."Automag GmbH$Service Ledger Entry" T3 on T3."Document No_" = T2."Document No_") left outer join "DMS1"."dbo"."Automag GmbH$Archived Service Header" T4 on T3."Order No_" = T4."No_") left outer join "DMS1"."dbo"."Automag GmbH$Employee" T5 on T4."Service Advisor No_" = T5."No_")
where ((((cast_float(T2."Remaining Amount"))) <> 0) and ((((T2."Document No_" LIKE 'WRGG%') or (T2."Document No_" LIKE 'WGGS%')) or (T2."Document No_" LIKE 'WGG%')) or (T2."Document No_" LIKE 'WRG%')))
) D1
order by c1 asc

END SQL
COLUMN,0,No
COLUMN,1,Name
COLUMN,2,Entry No
COLUMN,3,Customer No
COLUMN,4,Posting Date
COLUMN,5,Document Type
COLUMN,6,Document No
COLUMN,7,Description
COLUMN,8,Currency Code
COLUMN,9,Amount
COLUMN,10,Remaining Amount
COLUMN,11,Original Amount (lcy)
COLUMN,12,Remaining Amt (lcy)
COLUMN,13,Amount (lcy)
COLUMN,14,Sales (lcy)
COLUMN,15,Profit (lcy)
COLUMN,16,Customer Posting Group
COLUMN,17,Department Code
COLUMN,18,Make Code
COLUMN,19,Salesperson Code
COLUMN,20,User Id
COLUMN,21,Source Code
COLUMN,22,On Hold
COLUMN,23,Applies-to Doc Type
COLUMN,24,Applies-to Doc No
COLUMN,25,Open
COLUMN,26,Due Date
COLUMN,27,Positive
COLUMN,28,Reason Code
COLUMN,29,Bal Account Type
COLUMN,30,Bal Account No
COLUMN,31,Debit Amount
COLUMN,32,Credit Amount
COLUMN,33,No Series
COLUMN,34,Customer Group Code
COLUMN,35,Branch Code
COLUMN,36,Main Area
COLUMN,37,Vin
COLUMN,38,Comment
COLUMN,39,Saldo_Beleg
COLUMN,40,Hauptbetrieb
COLUMN,41,Standort
COLUMN,42,Saldo C U Cust
COLUMN,43,Tage
COLUMN,44,Staffel
COLUMN,45,Bookkeep Date_ori
COLUMN,46,Kunde
COLUMN,47,Beleg_ori
COLUMN,48,Bookkeep Date
COLUMN,49,Bereich
COLUMN,50,Document No_service_ledger_entry
COLUMN,51,Service Advisor No
COLUMN,52,Name_Service_Advisor
COLUMN,53,Verursacher
COLUMN,54,Beleg