C11/System/ARIntelligence/IQD/NW_GW/NW_GW_BE.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\GAPS_BMW\Portal\System\IQD\NW_GW\NW_GW_BE.imr
TITLE,NW_GW_BE.imr
BEGIN SQL
select c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30,
	   c31 as c31,
	   c32 as c32,
	   c33 as c33,
	   c34 as c34,
	   c35 as c35,
	   c36 as c36,
	   c37 as c37,
	   c38 as c38,
	   c39 as c39,
	   c40 as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   c44 as c44,
	   c45 as c45,
	   c46 as c46,
	   c47 as c47,
	   c48 as c48,
	   c49 as c49,
	   c50 as c50,
	   c51 as c51,
	   c52 as c52,
	   c53 as c53,
	   c54 as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   c58 as c58,
	   c59 as c59,
	   c60 as c60,
	   c61 as c61,
	   c62 as c62,
	   c63 as c63,
	   c64 as c64,
	   c65 as c65,
	   c66 as c66,
	   c67 as c67,
	   XSUM(c67 for c28) as c68,
	   c69 as c69,
	   c70 as c70,
	   c71 as c71,
	   c72 as c72,
	   c73 as c73,
	   c74 as c74,
	   c75 as c75,
	   c76 as c76,
	   c77 as c77
from 
(select c146 as c1,
	   c145 as c2,
	   c144 as c3,
	   c143 as c4,
	   c142 as c5,
	   c141 as c6,
	   c140 as c7,
	   c139 as c8,
	   c138 as c9,
	   c137 as c10,
	   c136 as c11,
	   c135 as c12,
	   c134 as c13,
	   c133 as c14,
	   c132 as c15,
	   c131 as c16,
	   c130 as c17,
	   c129 as c18,
	   c128 as c19,
	   c127 as c20,
	   c126 as c21,
	   c125 as c22,
	   c124 as c23,
	   c123 as c24,
	   c122 as c25,
	   c121 as c26,
	   c120 as c27,
	   c80 as c28,
	   c119 as c29,
	   c118 as c30,
	   c92 as c31,
	   c117 as c32,
	   c116 as c33,
	   c109 as c34,
	   c115 as c35,
	   c114 as c36,
	   c113 as c37,
	   '1' as c38,
	   c112 as c39,
	   c111 as c40,
	   c111 as c41,
	   c110 as c42,
	   c110 as c43,
	   c109 as c44,
	   c108 as c45,
	   c107 as c46,
	   c106 as c47,
	   c105 as c48,
	   c104 as c49,
	   c103 as c50,
	   c102 as c51,
	   c101 as c52,
	   c100 as c53,
	   c90 as c54,
	   c99 as c55,
	   c98 as c56,
	   c97 as c57,
	   c96 as c58,
	   c95 as c59,
	   c94 as c60,
	   c93 as c61,
	   c92 as c62,
	   (@CURRENT_DATE) as c63,
	   'Bestand' as c64,
	   c91 as c65,
	   XCOUNT(c90 for c80) as c66,
	   (c90) / (XCOUNT(c90 for c80)) as c67,
	   c89 as c69,
	   c88 as c70,
	   c87 as c71,
	   c86 as c72,
	   c85 as c73,
	   c84 as c74,
	   c83 as c75,
	   c82 as c76,
	   c81 as c77
from 
(select T2."VIN" as c80,
	   (cast_float(T4."Total Quote Amount")) as c81,
	   (asciiz(extract(YEAR FROM T4."Price Sticker Date"),4) || '-' || asciiz(extract(MONTH FROM T4."Price Sticker Date"),2) || '-' || asciiz(extract(DAY FROM T4."Price Sticker Date"),2)) as c82,
	   T4."Financing Total" as c83,
	   T4."Down Payment Account" as c84,
	   T4."List Price" as c85,
	   T4."Total Quote Amount" as c86,
	   T4."Price Sticker Date" as c87,
	   T4."Line No_" as c88,
	   T3."Current Price Sticker" as c89,
	   (extract(DAY FROM (now()) - T3."Purchase Receipt Date")) as c90,
	   (od_right(T2."VIN",7)) as c91,
	   ((cast_float(T2."Amount"))) as c92,
	   T7."Description" as c93,
	   T7."Option Type" as c94,
	   T6."Description" as c95,
	   T6."Option Type" as c96,
	   CASE WHEN ((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'VFW') THEN ((asciiz(extract(YEAR FROM T3."Initial Registration"),4) || '-' || asciiz(extract(MONTH FROM T3."Initial Registration"),2) || '-' || asciiz(extract(DAY FROM T3."Initial Registration"),2))) WHEN ((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'NA') THEN ((asciiz(extract(YEAR FROM T3."Purchase Receipt Date"),4) || '-' || asciiz(extract(MONTH FROM T3."Purchase Receipt Date"),2) || '-' || asciiz(extract(DAY FROM T3."Purchase Receipt Date"),2))) WHEN ((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') THEN ((asciiz(extract(YEAR FROM T3."Purchase Receipt Date"),4) || '-' || asciiz(extract(MONTH FROM T3."Purchase Receipt Date"),2) || '-' || asciiz(extract(DAY FROM T3."Purchase Receipt Date"),2))) ELSE null END as c97,
	   T3."Initial Registration" as c98,
	   CASE WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'NA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 0 AND 60)) THEN ('0 - 60 Tage') WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'NA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 61 AND 90)) THEN ('61 - 90 Tage') WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'NA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 91 AND 120)) THEN ('91 - 120 Tage') WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'NA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 121 AND 180)) THEN ('121 - 180 Tage') WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'NA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) > 180)) THEN ('> 180 Tage') WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'VFW') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 0 AND 60)) THEN ('0 - 60 Tage') WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'VFW') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 61 AND 90)) THEN ('61 - 90 Tage') WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'VFW') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 91 AND 120)) THEN ('91 - 120 Tage') WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'VFW') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 121 AND 180)) THEN ('121 - 180 Tage') WHEN (((CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) = 'VFW') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) > 180)) THEN ('> 180 Tage') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 0 AND 30)) THEN ('0 - 30 Tage') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 31 AND 60)) THEN ('31 - 60 Tage') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 61 AND 90)) THEN ('61 - 90 Tage') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) BETWEEN 91 AND 180)) THEN ('91 - 180 Tage') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (((extract(DAY FROM (now()) - T3."Purchase Receipt Date"))) > 180)) THEN ('> 180 Tage') ELSE null END as c99,
	   T3."Purchase Receipt Date" as c100,
	   T5."Code" || ' - ' || T5."Name" as c101,
	   T5."Name" as c102,
	   T5."Code" as c103,
	   T3."Location Code" || ' / ' || ((asciiz(extract(YEAR FROM T4."Price Sticker Date"),4) || '-' || asciiz(extract(MONTH FROM T4."Price Sticker Date"),2) || '-' || asciiz(extract(DAY FROM T4."Price Sticker Date"),2))) as c104,
	   CASE WHEN ((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') THEN ((od_right(T2."VIN",7)) || ' / ' || T3."Model" || ' / ' || (CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END) || ' / ' || (T3."Location Code" || ' / ' || ((asciiz(extract(YEAR FROM T4."Price Sticker Date"),4) || '-' || asciiz(extract(MONTH FROM T4."Price Sticker Date"),2) || '-' || asciiz(extract(DAY FROM T4."Price Sticker Date"),2))))) ELSE ((od_right(T2."VIN",7)) || ' / ' || T3."Model" || ' / ' || (T3."Location Code" || ' / ' || ((asciiz(extract(YEAR FROM T4."Price Sticker Date"),4) || '-' || asciiz(extract(MONTH FROM T4."Price Sticker Date"),2) || '-' || asciiz(extract(DAY FROM T4."Price Sticker Date"),2))))) END as c105,
	   CASE WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - diff.best.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'GA') and (not T3."VAT Prod_ Posting Group" LIKE 'DIFF%')) THEN ('GA - regelbest.') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 0)) THEN ('NA') WHEN (((CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END) = 'NA') and (T3."Vehicle Status" = 2)) THEN ('VFW') ELSE null END as c106,
	   T3."VAT Prod_ Posting Group" as c107,
	   CASE WHEN (T3."Vehicle Status" = 1) THEN ('GA') WHEN (T3."Vehicle Status" IN (0,2)) THEN ('NA') ELSE null END as c108,
	   T3."Model" as c109,
	   T3."Market Segment Code" as c110,
	   T3."Make Code" as c111,
	   CASE WHEN (((T3."Location Code" || ' / ' || ((asciiz(extract(YEAR FROM T4."Price Sticker Date"),4) || '-' || asciiz(extract(MONTH FROM T4."Price Sticker Date"),2) || '-' || asciiz(extract(DAY FROM T4."Price Sticker Date"),2)))) LIKE 'LBS%') or ((T3."Location Code" || ' / ' || ((asciiz(extract(YEAR FROM T4."Price Sticker Date"),4) || '-' || asciiz(extract(MONTH FROM T4."Price Sticker Date"),2) || '-' || asciiz(extract(DAY FROM T4."Price Sticker Date"),2)))) = '')) THEN ('10') WHEN ((T3."Location Code" || ' / ' || ((asciiz(extract(YEAR FROM T4."Price Sticker Date"),4) || '-' || asciiz(extract(MONTH FROM T4."Price Sticker Date"),2) || '-' || asciiz(extract(DAY FROM T4."Price Sticker Date"),2)))) LIKE 'WLS%') THEN ('20') ELSE ((od_left(T1."Department Code",2))) END as c112,
	   T3."Model No_" as c113,
	   T3."Class" as c114,
	   T3."Type" as c115,
	   T3."Vehicle Status" as c116,
	   T3."VIN" as c117,
	   T2."Veh_ Source Code" as c118,
	   T2."Book No_" as c119,
	   T2."Main Area" as c120,
	   T2."Branch Code" as c121,
	   T2."No_ Series" as c122,
	   T2."Source No_" as c123,
	   T2."Source Type" as c124,
	   T2."Document Date" as c125,
	   T2."Credit Amount" as c126,
	   T2."Debit Amount" as c127,
	   T2."Quantity" as c128,
	   T2."Source Code" as c129,
	   T2."User ID" as c130,
	   T2."Make Code" as c131,
	   T2."Department Code" as c132,
	   T2."Bal_ Account No_" as c133,
	   T2."Amount" as c134,
	   T2."Description" as c135,
	   T2."Document No_" as c136,
	   T2."Document Type" as c137,
	   T2."Posting Date" as c138,
	   T2."G_L Account No_" as c139,
	   T2."Entry No_" as c140,
	   T1."Income_Balance" as c141,
	   T1."Department Code" as c142,
	   T1."Make Code" as c143,
	   T1."Account Type" as c144,
	   T1."Name" as c145,
	   T1."No_" as c146
from "DMS1"."dbo"."Automag GmbH$G_L Account" T1,
	((((("DMS1"."dbo"."Automag GmbH$G_L Entry" T2 left outer join "DMS1"."dbo"."Automag GmbH$Vehicle" T3 on T3."VIN" = T2."VIN") left outer join "DMS1"."dbo"."Automag GmbH$Veh_ Price Sticker Header" T4 on ((T3."VIN" = T4."VIN") and (T3."Current Price Sticker" = T4."Line No_")) and (T3."Current Book No_" = T4."Vehicle Book No_")) left outer join "DMS1"."dbo"."Automag GmbH$Salesperson_Purchaser" T5 on T3."Purchaser Code" = T5."Code") left outer join "DMS1"."dbo"."Automag GmbH$Vehicle Option" T6 on ((T3."VIN" = T6."VIN") and (T6."Option Type" = 2)) and (T6."Built-in Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) left outer join "DMS1"."dbo"."Automag GmbH$Vehicle Option" T7 on ((T3."VIN" = T7."VIN") and (T7."Option Type" = 1)) and (T7."Built-in Date" <> TIMESTAMP '1753-01-01 00:00:00.000'))
where (T1."No_" = T2."G_L Account No_")
and ((T1."No_" BETWEEN '31000' AND '32905') and (T3."Vehicle Status" <> 3))
) D2
) D1
order by c68 desc,c28 asc

END SQL
COLUMN,0,No
COLUMN,1,Name
COLUMN,2,Account Type
COLUMN,3,Make Code
COLUMN,4,Department Code
COLUMN,5,Income Balance
COLUMN,6,Entry No
COLUMN,7,G L Account No
COLUMN,8,Posting Date
COLUMN,9,Document Type
COLUMN,10,Document No
COLUMN,11,Description
COLUMN,12,Amount
COLUMN,13,Bal Account No
COLUMN,14,Department Code
COLUMN,15,Make Code
COLUMN,16,User Id
COLUMN,17,Source Code
COLUMN,18,Quantity
COLUMN,19,Debit Amount
COLUMN,20,Credit Amount
COLUMN,21,Document Date
COLUMN,22,Source Type
COLUMN,23,Source No
COLUMN,24,No Series
COLUMN,25,Branch Code
COLUMN,26,Main Area
COLUMN,27,Vin
COLUMN,28,Book No
COLUMN,29,Veh Source Code
COLUMN,30,Betrag
COLUMN,31,Vin_Vehicle
COLUMN,32,Vehicle Status
COLUMN,33,Model_ori
COLUMN,34,Type
COLUMN,35,Class
COLUMN,36,Model No
COLUMN,37,Hauptbetrieb
COLUMN,38,Standort
COLUMN,39,Make Code_Vehicle
COLUMN,40,Fabrikat
COLUMN,41,Model
COLUMN,42,Market Segment Code
COLUMN,43,Modellbez
COLUMN,44,Fahrzeugart
COLUMN,45,Vat Prod Posting Group
COLUMN,46,Fahrzeugtyp
COLUMN,47,FZG
COLUMN,48,Location Code
COLUMN,49,Code_Salesperson Purchase
COLUMN,50,Name_Salesperson Purchase
COLUMN,51,VB_Einkauf
COLUMN,52,Purchase Receipt Date
COLUMN,53,Standtage_ori
COLUMN,54,Standtagestaffel
COLUMN,55,Initial Registration
COLUMN,56,EZ/Eingang
COLUMN,57,Option Type_Polster
COLUMN,58,Polster
COLUMN,59,Option Type_Farbe
COLUMN,60,Farbe
COLUMN,61,Einsatz
COLUMN,62,Heute
COLUMN,63,Fahrzeugstatus
COLUMN,64,FZG-Liste
COLUMN,65,Anzahl
COLUMN,66,Standtage
COLUMN,67,Summe Standtage
COLUMN,68,Current Price Sticker
COLUMN,69,Line No
COLUMN,70,Price Sticker Date
COLUMN,71,Total Quote Amount
COLUMN,72,List Price
COLUMN,73,Down Payment Account
COLUMN,74,Financing Total
COLUMN,75,letzte Preisänderung
COLUMN,76,geplanter VK