C11/System/ARI/IQD/OP/OP_ARI.sql

select "Entry No" as "Entry No",
	   "Customer No" as "Customer No",
	   "Posting Date" as "Posting Date",
	   "Document Type" as "Document Type",
	   "Document No" as "Document No",
	   "Description" as "Description",
	   "Sales (lcy)" as "Sales (lcy)",
	   "Profit (lcy)" as "Profit (lcy)",
	   "Sell-to Customer No" as "Sell-to Customer No",
	   "Customer Posting Group" as "Customer Posting Group",
	   "Global Dimension 1 Code" as "Global Dimension 1 Code",
	   "Global Dimension 2 Code" as "Global Dimension 2 Code",
	   "Salesperson Code" as "Salesperson Code",
	   "User Id" as "User Id",
	   "Source Code" as "Source Code",
	   "On Hold" as "On Hold",
	   "Applies-to Doc Type" as "Applies-to Doc Type",
	   "Applies-to Doc No" as "Applies-to Doc No",
	   "Open" as "Open",
	   "Due Date" as "Due Date",
	   "Pmt Discount Date" as "Pmt Discount Date",
	   "Positive" as "Positive",
	   "Closed By Entry No" as "Closed By Entry No",
	   "Closed At Date" as "Closed At Date",
	   "Closed By Amount" as "Closed By Amount",
	   "Journal Batch Name" as "Journal Batch Name",
	   "Closed By Amount (lcy)" as "Closed By Amount (lcy)",
	   "Document Date" as "Document Date",
	   "No Series" as "No Series",
	   "Last Issued Reminder Level" as "Last Issued Reminder Level",
	   "Dimension Set Id" as "Dimension Set Id",
	   "Direct Debit Mandate Id" as "Direct Debit Mandate Id",
	   "Customer Group Code" as "Customer Group Code",
	   "Branch Code" as "Branch Code",
	   "Main Area" as "Main Area",
	   "Pmt Disc Base" as "Pmt Disc Base",
	   "Vin" as "Vin",
	   "Factory No" as "Factory No",
	   "Cash Reg Receipt No" as "Cash Reg Receipt No",
	   "Comment" as "Comment",
	   "Service Advisor No" as "Service Advisor No",
	   "Salesperson Code 2" as "Salesperson Code 2",
	   "Entry No" as "Entry No",
	   "Cust Ledger Entry No" as "Cust Ledger Entry No",
	   "Entry Type" as "Entry Type",
	   "Posting Date" as "Posting Date",
	   "Document Type" as "Document Type",
	   "Document No" as "Document No",
	   "Amount" as "Amount",
	   "Customer No" as "Customer No",
	   "User Id" as "User Id",
	   "Source Code" as "Source Code",
	   "Debit Amount" as "Debit Amount",
	   "Credit Amount" as "Credit Amount",
	   "Initial Entry Due Date" as "Initial Entry Due Date",
	   "Initial Entry Global Dim 1" as "Initial Entry Global Dim 1",
	   "Initial Entry Global Dim 2" as "Initial Entry Global Dim 2",
	   "Gen Bus Posting Group" as "Gen Bus Posting Group",
	   "Gen Prod Posting Group" as "Gen Prod Posting Group",
	   "Initial Document Type" as "Initial Document Type",
	   "Saldo_Beleg_1" as "Saldo_Beleg_1",
	   "Anzahl_Sätze_Entry_No" as "Anzahl_Sätze_Entry_No",
	   "offen" as "offen",
	   SUM("offen") OVER (partition by "Customer No") as "Gesamt offen KD (Info)",
	   "Invoice Date" as "Invoice Date",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "Sel Name" as "Sel Name",
	   "No_Customer" as "No_Customer",
	   "Name_Customer" as "Name_Customer",
	   "Last Name_Customer" as "Last Name_Customer",
	   "First Name_Customer" as "First Name_Customer",
	   "Kunde" as "Kunde",
	   "Beleg" as "Beleg",
	   "Bereich" as "Bereich",
	   "Tage" as "Tage",
	   "Staffel" as "Staffel",
	   "Mahnstufe" as "Mahnstufe",
	   "Forderungsart" as "Forderungsart",
	   "Abwarten" as "Abwarten",
	   "Leasing" as "Leasing"
from 
(select c86 as "Entry No",
	   c157 as "Customer No",
	   c156 as "Posting Date",
	   c155 as "Document Type",
	   c154 as "Document No",
	   c153 as "Description",
	   c152 as "Sales (lcy)",
	   c151 as "Profit (lcy)",
	   c150 as "Sell-to Customer No",
	   c149 as "Customer Posting Group",
	   c148 as "Global Dimension 1 Code",
	   c147 as "Global Dimension 2 Code",
	   c146 as "Salesperson Code",
	   c99 as "User Id",
	   c145 as "Source Code",
	   c144 as "On Hold",
	   c143 as "Applies-to Doc Type",
	   c142 as "Applies-to Doc No",
	   c141 as "Open",
	   c101 as "Due Date",
	   c140 as "Pmt Discount Date",
	   c139 as "Positive",
	   c138 as "Closed By Entry No",
	   c137 as "Closed At Date",
	   c136 as "Closed By Amount",
	   c135 as "Journal Batch Name",
	   c134 as "Closed By Amount (lcy)",
	   c133 as "Document Date",
	   c132 as "No Series",
	   c89 as "Last Issued Reminder Level",
	   c131 as "Dimension Set Id",
	   c130 as "Direct Debit Mandate Id",
	   c129 as "Customer Group Code",
	   c128 as "Branch Code",
	   c127 as "Main Area",
	   c126 as "Pmt Disc Base",
	   c125 as "Vin",
	   c124 as "Factory No",
	   c123 as "Cash Reg Receipt No",
	   c122 as "Comment",
	   c121 as "Service Advisor No",
	   c120 as "Salesperson Code 2",
	   c119 as "Entry No",
	   c118 as "Cust Ledger Entry No",
	   c117 as "Entry Type",
	   c116 as "Posting Date",
	   c115 as "Document Type",
	   c114 as "Document No",
	   c113 as "Amount",
	   c112 as "Customer No",
	   c111 as "User Id",
	   c110 as "Source Code",
	   c109 as "Debit Amount",
	   c108 as "Credit Amount",
	   c107 as "Initial Entry Due Date",
	   c106 as "Initial Entry Global Dim 1",
	   c105 as "Initial Entry Global Dim 2",
	   c104 as "Gen Bus Posting Group",
	   c103 as "Gen Prod Posting Group",
	   c102 as "Initial Document Type",
	   SUM(c113) OVER (partition by c86) as "Saldo_Beleg_1",
	   COUNT(c157) OVER (partition by c86) as "Anzahl_Sätze_Entry_No",
	   (SUM(c113) OVER (partition by c86)) / (COUNT(c157) OVER (partition by c86)) as "offen",
	   c101 as "Invoice Date",
	   '1' as "Hauptbetrieb",
	   c100 as "Standort",
	   c99 as "Sel Name",
	   c98 as "No_Customer",
	   c97 as "Name_Customer",
	   c96 as "Last Name_Customer",
	   c95 as "First Name_Customer",
	   c94 as "Kunde",
	   c93 as "Beleg",
	   c92 as "Bereich",
	   c91 as "Tage",
	   c90 as "Staffel",
	   c89 as "Mahnstufe",
	   c88 as "Forderungsart",
	   c87 as "Abwarten",
	   '' as "Leasing"
from 
(select T1."Entry No_" as c86,
	   CASE WHEN (T1."Customer No_" LIKE '%Garan%') THEN ('Garantie') WHEN ((CASE WHEN (T3."First Name" <> '') THEN (T3."Last Name" + ', ' + T3."First Name" + ' - ' + T3."No_") ELSE (T3."Name" + ' - ' + T3."No_") END) LIKE '%BMW%') THEN ((CASE WHEN (T3."First Name" <> '') THEN (T3."Last Name" + ', ' + T3."First Name" + ' - ' + T3."No_") ELSE (T3."Name" + ' - ' + T3."No_") END)) ELSE ('Kundenforderungen') END as c87,
	   CASE WHEN (T1."Customer No_" LIKE '%Garan%') THEN ('Garantie') ELSE ('Kundenforderungen') END as c88,
	   T1."Last Issued Reminder Level" as c89,
	   CASE WHEN (((day((now()) - T1."Due Date"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((day((now()) - T1."Due Date"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((day((now()) - T1."Due Date"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((day((now()) - T1."Due Date"))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((day((now()) - T1."Due Date"))) > 84) THEN ('> 12 Wochen') WHEN (((day((now()) - T1."Due Date"))) < 0) THEN ('noch nicht fällig') ELSE null END as c90,
	   (day((now()) - T1."Due Date")) as c91,
	   CASE WHEN (T1."Main Area" = 0) THEN ('Sonstige') WHEN (T1."Main Area" = 1) THEN ('TZ') WHEN (T1."Main Area" = 2) THEN ('Verkauf') WHEN (T1."Main Area" = 3) THEN ('Service') WHEN (T1."Main Area" = 5) THEN ('Kassenbuchung') ELSE ('nicht zuzuordnen') END as c92,
	   CASE WHEN (T1."Comment" <> '') THEN (T1."Document No_" + ' - ' + T1."Description" + ' - ' + T1."Comment" + ' - ' + T1."User ID" + ' - MS:' + (cast_numberToString(cast_integer(T1."Last Issued Reminder Level")))) ELSE (T1."Document No_" + ' - ' + T1."Description" + ' - ' + T1."User ID" + ' - MS:' + (cast_numberToString(cast_integer(T1."Last Issued Reminder Level")))) END as c93,
	   CASE WHEN (T3."First Name" <> '') THEN (T3."Last Name" + ', ' + T3."First Name" + ' - ' + T3."No_") ELSE (T3."Name" + ' - ' + T3."No_") END as c94,
	   T3."First Name" as c95,
	   T3."Last Name" as c96,
	   T3."Name" as c97,
	   T3."No_" as c98,
	   T1."User ID" as c99,
	   CASE WHEN (T1."Branch Code" = 'BUR') THEN ('10') WHEN (T1."Branch Code" = 'MUE') THEN ('20') ELSE null END as c100,
	   T1."Due Date" as c101,
	   T2."Initial Document Type" as c102,
	   T2."Gen_ Prod_ Posting Group" as c103,
	   T2."Gen_ Bus_ Posting Group" as c104,
	   T2."Initial Entry Global Dim_ 2" as c105,
	   T2."Initial Entry Global Dim_ 1" as c106,
	   T2."Initial Entry Due Date" as c107,
	   (convert(float, T2."Credit Amount")) as c108,
	   (convert(float, T2."Debit Amount")) as c109,
	   T2."Source Code" as c110,
	   T2."User ID" as c111,
	   T2."Customer No_" as c112,
	   (convert(float, T2."Amount")) as c113,
	   T2."Document No_" as c114,
	   T2."Document Type" as c115,
	   T2."Posting Date" as c116,
	   T2."Entry Type" as c117,
	   T2."Cust_ Ledger Entry No_" as c118,
	   T2."Entry No_" as c119,
	   T1."Salesperson Code 2" as c120,
	   T1."Service Advisor No_" as c121,
	   T1."Comment" as c122,
	   T1."Cash Reg_ Receipt No_" as c123,
	   T1."Factory No_" as c124,
	   T1."VIN" as c125,
	   T1."Pmt_ Disc_ Base" as c126,
	   T1."Main Area" as c127,
	   T1."Branch Code" as c128,
	   T1."Customer Group Code" as c129,
	   T1."Direct Debit Mandate ID" as c130,
	   T1."Dimension Set ID" as c131,
	   T1."No_ Series" as c132,
	   T1."Document Date" as c133,
	   T1."Closed by Amount (LCY)" as c134,
	   T1."Journal Batch Name" as c135,
	   T1."Closed by Amount" as c136,
	   T1."Closed at Date" as c137,
	   T1."Closed by Entry No_" as c138,
	   T1."Positive" as c139,
	   T1."Pmt_ Discount Date" as c140,
	   T1."Open" as c141,
	   T1."Applies-to Doc_ No_" as c142,
	   T1."Applies-to Doc_ Type" as c143,
	   T1."On Hold" as c144,
	   T1."Source Code" as c145,
	   T1."Salesperson Code" as c146,
	   T1."Global Dimension 2 Code" as c147,
	   T1."Global Dimension 1 Code" as c148,
	   T1."Customer Posting Group" as c149,
	   T1."Sell-to Customer No_" as c150,
	   (convert(float, T1."Profit (LCY)")) as c151,
	   (convert(float, T1."Sales (LCY)")) as c152,
	   T1."Description" as c153,
	   T1."Document No_" as c154,
	   T1."Document Type" as c155,
	   T1."Posting Date" as c156,
	   T1."Customer No_" as c157
from "Vogl7x"."dbo"."BMW AH Vogl$Detailed Cust_ Ledg_ Entry" T2,
	("Vogl7x"."dbo"."BMW AH Vogl$Cust_ Ledger Entry" T1 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer" T3 on T1."Customer No_" = T3."No_")
where (T1."Entry No_" = T2."Cust_ Ledger Entry No_")
and (T1."Open" = 1)
) D2
) D1
-- order by "Customer No" asc,"Entry No" asc