C11/System/NAVISION/IQD/OP/forderungen_nav_tz_ma.sql

select T1."No_" as "No",
	   T1."Name" as "Name_3",
	   T1."Search Name" as "Search Name",
	   T1."Name 2" as "Name 2",
	   T1."Address" as "Address",
	   T1."Address 2" as "Address 2",
	   T1."City" as "City",
	   T1."Contact" as "Contact",
	   T1."Phone No_" as "Phone No",
	   T1."Telex No_" as "Telex No",
	   T1."Our Account No_" as "Our Account No",
	   T2."Entry No_" as "Entry No",
	   T2."Customer No_" as "Customer No",
	   T2."Posting Date" as "Posting Date",
	   T2."Document Type" as "Document Type",
	   T2."Document No_" as "Document No",
	   T2."Description" as "Description",
	   T2."Currency Code" as "Currency Code",
	   T2."Sell-to Customer No_" as "Sell-to Customer No",
	   T2."Customer Posting Group" as "Customer Posting Group",
	   T2."Department Code" as "Department Code",
	   T2."Make Code" as "Make Code",
	   T2."Salesperson Code" as "Salesperson Code",
	   T2."User ID" as "User Id",
	   T2."Source Code" as "Source Code",
	   T2."On Hold" as "On Hold",
	   T2."Applies-to Doc_ Type" as "Applies-to Doc Type",
	   T2."Applies-to Doc_ No_" as "Applies-to Doc No",
	   T2."Open" as "Open",
	   T2."Due Date" as "Due Date",
	   T2."Pmt_ Discount Date" as "Pmt Discount Date",
	   T2."Positive" as "Positive",
	   T2."Closed by Entry No_" as "Closed By Entry No",
	   T2."Closed at Date" as "Closed At Date",
	   T2."Applies-to ID" as "Applies-to Id",
	   T2."Journal Batch Name" as "Journal Batch Name",
	   T2."Reason Code" as "Reason Code",
	   T2."Bal_ Account Type" as "Bal Account Type",
	   T2."Bal_ Account No_" as "Bal Account No",
	   T2."Transaction No_" as "Transaction No",
	   T2."Document Date" as "Document Date",
	   T2."External Document No_" as "External Document No",
	   T2."Calculate Interest" as "Calculate Interest",
	   T2."Closing Interest Calculated" as "Closing Interest Calculated",
	   T2."No_ Series" as "No Series",
	   T2."Closed by Currency Code" as "Closed By Currency Code",
	   T2."Customer Group Code" as "Customer Group Code",
	   T2."Branch Code" as "Branch Code",
	   T2."Main Area" as "Main Area",
	   T2."VIN" as "Vin",
	   T2."Cash Reg_ Receipt No_" as "Cash Reg Receipt No",
	   T2."Comment" as "Comment",
	   T2."Is Vehicle" as "Is Vehicle",
	   T2."Bulk Customer No_" as "Bulk Customer No",
	   T2."Bulkcust_ Business" as "Bulkcust Business",
	   T2."Book No_" as "Book No",
	   T2."No_ of Bulk Customer" as "No Of Bulk Customer",
	   T3."Code" as "Code_2",
	   T3."Name" as "Name_2",
	   T3."Consolidation Code" as "Consolidation Code",
	   'Forderungen DMS' as "Stufe 1",
	   CASE WHEN (T2."Main Area" = 1) THEN ('T&Z') ELSE null END as "Stufe 2",
	   T4."Code" as "Code",
	   T4."Name" as "Name",
	   (CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END) as "Stufe 3",
	   (convert(float, T2."Remaining Amount")) as "Remaining_Amount_MA",
	   (CASE WHEN ((T2."Salesperson Code" <> '') and (T4."Code" <> '')) THEN (T2."Salesperson Code" + ' - ' + T4."Name") ELSE ('n.N.') END) as "Stufe 5",
	   CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END as "Betrieb Nr",
	   T2."Due Date" as "Fälligkeitsdatum",
	   CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END as "Doc_Nr_Stelle_2",
	   (getdate()) as "Heute",
	   '' as "Stufe 4",
	   (-1 * datediff(day, ((getdate())), T2."Due Date")) as "Tage",
	   CASE WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) > 42) THEN ('älter 6 Wochen') WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) BETWEEN 22 AND 28) THEN ('3 - 4 Wochen') WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) BETWEEN 15 AND 21) THEN ('2 - 3 Wochen') WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) BETWEEN 0 AND 14) THEN ('0 - 2 Wochen') ELSE ('noch nicht fällig') END as "Staffel",
	   CASE WHEN ((T2."Salesperson Code" <> '') and (T4."Code" <> '')) THEN (T2."Salesperson Code" + ' - ' + T4."Name") ELSE ('n.N.') END as "Verursacher",
	   CASE WHEN (T1."No_" IN ('690101','690102','690103','690104','690105')) THEN (T2."Document No_" + ' - ' + T2."Description" + ' - ' + (convert(varchar(50), year(T2."Due Date")) + '-' + convert(varchar(50), month(T2."Due Date")) + '-' + convert(varchar(50), day(T2."Due Date"))) + ' / ' + (CASE WHEN ((T2."Salesperson Code" <> '') and (T4."Code" <> '')) THEN (T2."Salesperson Code" + ' - ' + T4."Name") ELSE ('n.N.') END)) ELSE (T2."Document No_" + ' - ' + T1."Name" + ' - ' + (convert(varchar(50), year(T2."Due Date")) + '-' + convert(varchar(50), month(T2."Due Date")) + '-' + convert(varchar(50), day(T2."Due Date"))) + ' / ' + (CASE WHEN ((T2."Salesperson Code" <> '') and (T4."Code" <> '')) THEN (T2."Salesperson Code" + ' - ' + T4."Name") ELSE ('n.N.') END)) END as "Beleg",
	   T2."Client_DB" as "Hauptbetrieb_ID",
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   (CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END) as "Standort_ID",
	   CASE WHEN (((CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '1') THEN ('10') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '2') THEN ('20') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '3') THEN ('30') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '4') THEN ('40') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '5') THEN ('50') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '9') THEN ('55') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '6') THEN ('60') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '7') THEN ('70') WHEN ((CASE WHEN ((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) THEN ((substring(T2."Document No_", 8, 1))) WHEN (T2."Document No_" LIKE 'VRGT%') THEN ((substring(T2."Document No_", 7, 1))) ELSE null END) = '8') THEN ('80') ELSE null END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name"
from ((("NAVISION"."import"."Customer_Ledger_Entry" T2 left outer join "NAVISION"."import"."Customer" T1 on (T1."No_" = T2."Customer No_") and (T1."Client_DB" = T2."Client_DB")) left outer join "NAVISION"."import"."Department" T3 on (T2."Department Code" = T3."Code") and (T2."Client_DB" = T3."Client_DB")) left outer join "NAVISION"."import"."Salesperson_Purchaser" T4 on (T2."Salesperson Code" = T4."Code") and (T2."Client_DB" = T4."Client_DB"))
where ((((((convert(float, T2."Remaining Amount"))) <> 0) and (T2."Journal Batch Name" <> 'ERSTIMPORT')) and (not T1."No_" LIKE '600%')) and ((((T2."Document No_" LIKE 'VRGGET%') or (T2."Document No_" LIKE 'VGUGET%')) or (T2."Document No_" LIKE 'VRT%')) or (T2."Document No_" LIKE 'VRGT%')))
-- order by "Posting Date" asc