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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\GAPS_BMW\Portal\System\IQD\Service\Teile.imr
TITLE,Teile.imr
BEGIN SQL
select c85 as c1,
	   c159 as c2,
	   c158 as c3,
	   c157 as c4,
	   c119 as c5,
	   c156 as c6,
	   c84 as c7,
	   c155 as c8,
	   c154 as c9,
	   c153 as c10,
	   c152 as c11,
	   c151 as c12,
	   c150 as c13,
	   c149 as c14,
	   c148 as c15,
	   c147 as c16,
	   c146 as c17,
	   c145 as c18,
	   c144 as c19,
	   c143 as c20,
	   c142 as c21,
	   c141 as c22,
	   c140 as c23,
	   c139 as c24,
	   c138 as c25,
	   c137 as c26,
	   c136 as c27,
	   c135 as c28,
	   c134 as c29,
	   c133 as c30,
	   c132 as c31,
	   c131 as c32,
	   c130 as c33,
	   c129 as c34,
	   c128 as c35,
	   c127 as c36,
	   c126 as c37,
	   c125 as c38,
	   c124 as c39,
	   c123 as c40,
	   '1' as c41,
	   c122 as c42,
	   XSUM(c123 at c85,c84,c123 for c85,c84) as c43,
	   c121 as c44,
	   c120 as c45,
	   c119 as c46,
	   c118 as c47,
	   c117 as c48,
	   (@CURRENT_DATE) as c49,
	   c116 as c50,
	   c115 as c51,
	   c114 as c52,
	   c113 as c53,
	   c112 as c54,
	   c111 as c55,
	   c110 as c56,
	   c109 as c57,
	   c108 as c58,
	   c107 as c59,
	   c106 as c60,
	   c105 as c61,
	   c104 as c62,
	   c103 as c63,
	   c102 as c64,
	   c101 as c65,
	   c100 as c66,
	   c99 as c67,
	   c98 as c68,
	   c97 as c69,
	   c96 as c70,
	   c95 as c71,
	   c94 as c72,
	   c93 as c73,
	   c92 as c74,
	   c91 as c75,
	   c90 as c76,
	   c89 as c77,
	   c88 as c78,
	   c87 as c79,
	   c86 as c80
from 
(select T2."Document No_" as c84,
	   T1."No_" as c85,
	   T7."User ID" as c86,
	   T8."Name" as c87,
	   T8."Code" as c88,
	   T7."Salesperson Code" as c89,
	   CASE WHEN (((cast_float(T6."Quantity")) * (cast_float(T6."Unit Cost"))) IS NULL) THEN (0) ELSE (((cast_float(T6."Quantity")) * (cast_float(T6."Unit Cost")))) END as c90,
	   (cast_float(T6."Quantity")) * (cast_float(T6."Unit Cost")) as c91,
	   CASE WHEN (((cast_float(T6."Amount"))) IS NULL) THEN (((cast_float(T2."Amount"))) / 1.19) ELSE (((cast_float(T6."Amount")))) END as c92,
	   (cast_float(T6."Amount")) as c93,
	   (cast_float(T2."Amount")) as c94,
	   T6."Service Order Line No_" as c95,
	   T6."Service Order No_" as c96,
	   T6."Order Type" as c97,
	   T6."Posting Date" as c98,
	   T6."Order Line No_" as c99,
	   T6."Order No_" as c100,
	   T6."Quantity (Base)" as c101,
	   T6."Unit Cost" as c102,
	   T6."Amount" as c103,
	   T6."Line Discount Amount" as c104,
	   T6."Unit Cost (LCY)" as c105,
	   T6."Unit Price" as c106,
	   T6."Quantity" as c107,
	   T6."Description" as c108,
	   T6."No_" as c109,
	   T6."Type" as c110,
	   CASE WHEN ((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) <> 'n.N.') THEN (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)) ELSE (T2."Document No_" || ' - ' || T2."Description" || ' - ' || T2."Comment" || ' - ' || T2."User ID") END as c111,
	   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 c112,
	   T5."Name" as c113,
	   T4."Service Advisor No_" as c114,
	   T3."Document No_" as c115,
	   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 c116,
	   T2."Document No_" || ' - ' || T2."Description" || ' - ' || T2."Comment" || ' - ' || T2."User ID" as c117,
	   T1."Name" || ' - ' || T1."No_" as c118,
	   T2."Posting Date" as c119,
	   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 c120,
	   (extract(DAY FROM (now()) - T2."Due Date")) as c121,
	   CASE WHEN (T2."Branch Code" = 'LBS') THEN ('10') ELSE null END as c122,
	   (cast_float(T2."Remaining Amount")) as c123,
	   T2."Comment" as c124,
	   T2."VIN" as c125,
	   T2."Main Area" as c126,
	   T2."Branch Code" as c127,
	   T2."Customer Group Code" as c128,
	   T2."No_ Series" as c129,
	   T2."Credit Amount" as c130,
	   T2."Debit Amount" as c131,
	   T2."Bal_ Account No_" as c132,
	   T2."Bal_ Account Type" as c133,
	   T2."Reason Code" as c134,
	   T2."Positive" as c135,
	   T2."Due Date" as c136,
	   T2."Open" as c137,
	   T2."Applies-to Doc_ No_" as c138,
	   T2."Applies-to Doc_ Type" as c139,
	   T2."On Hold" as c140,
	   T2."Source Code" as c141,
	   T2."User ID" as c142,
	   T2."Salesperson Code" as c143,
	   T2."Make Code" as c144,
	   T2."Department Code" as c145,
	   T2."Customer Posting Group" as c146,
	   T2."Profit (LCY)" as c147,
	   T2."Sales (LCY)" as c148,
	   T2."Amount (LCY)" as c149,
	   T2."Remaining Amt_ (LCY)" as c150,
	   T2."Original Amount (LCY)" as c151,
	   T2."Remaining Amount" as c152,
	   T2."Amount" as c153,
	   T2."Currency Code" as c154,
	   T2."Description" as c155,
	   T2."Document Type" as c156,
	   T2."Customer No_" as c157,
	   T2."Entry No_" as c158,
	   T1."Name" as c159
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_") left outer join "DMS1"."dbo"."Automag GmbH$Sales Invoice Line" T6 on T6."Document No_" = T2."Document No_") left outer join "DMS1"."dbo"."Automag GmbH$Sales Invoice Header" T7 on T6."Document No_" = T7."No_") left outer join "DMS1"."dbo"."Automag GmbH$Salesperson_Purchaser" T8 on T2."Salesperson Code" = T8."Code")
where ((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) = 'Teile')
order by c85 asc,c84 asc,c123 desc
) D1

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_Invoice
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
COLUMN,46,Kunde
COLUMN,47,Beleg_ori
COLUMN,48,Bookkeep Date_OP
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
COLUMN,55,Type
COLUMN,56,No
COLUMN,57,Description
COLUMN,58,Quantity
COLUMN,59,Unit Price
COLUMN,60,Unit Cost (lcy)
COLUMN,61,Line Discount Amount
COLUMN,62,Amount_VK_Rechnung
COLUMN,63,Unit Cost
COLUMN,64,Quantity (base)
COLUMN,65,Order No
COLUMN,66,Order Line No
COLUMN,67,Posting Date
COLUMN,68,Order Type
COLUMN,69,Service Order No
COLUMN,70,Service Order Line No
COLUMN,71,Betrag_aus_cust_ledg_entry
COLUMN,72,Betrag_Rechnung
COLUMN,73,Umsatz Teile
COLUMN,74,Einsatzpreis
COLUMN,75,Einsatz Teile
COLUMN,76,Salesperson Code_Invoice
COLUMN,77,Code_Salesperson
COLUMN,78,Name_Salesperson
COLUMN,79,User Id_Invoice_falsch