C11/System/LOCOSOFT/IQD/OP/OP_aus_LOC_Belege_Deb_Saldo.sql

select "Accounting Date" as "Accounting Date",
	   "Document Type" as "Document Type",
	   "Document Number" as "Document Number",
	   "Position In Document" as "Position In Document",
	   "Nominal Account Number" as "Nominal Account Number",
	   "Customer Number" as "Customer Number",
	   "Is Balanced" as "Is Balanced",
	   "Clearing Number" as "Clearing Number",
	   "Document Date" as "Document Date",
	   "Posted Value" as "Posted Value",
	   "Debit Or Credit" as "Debit Or Credit",
	   "Posted Count" as "Posted Count",
	   "Branch Number" as "Branch Number",
	   "Customer Contra Account" as "Customer Contra Account",
	   "Nominal Contra Account" as "Nominal Contra Account",
	   "Contra Account Text" as "Contra Account Text",
	   "Account Form Page Number" as "Account Form Page Number",
	   "Account Form Page Line" as "Account Form Page Line",
	   "Serial Number Each Month" as "Serial Number Each Month",
	   "Employee Number" as "Employee Number",
	   "Invoice Date" as "Invoice Date",
	   "Invoice Number" as "Invoice Number",
	   "Dunning Level" as "Dunning Level",
	   "Last Dunning Date" as "Last Dunning Date",
	   "Journal Page" as "Journal Page",
	   "Journal Line" as "Journal Line",
	   "Cash Discount" as "Cash Discount",
	   "Term Of Payment" as "Term Of Payment",
	   "Posting Text" as "Posting Text",
	   "Vehicle Reference" as "Vehicle Reference",
	   "Vat Id Number" as "Vat Id Number",
	   "Account Statement Number" as "Account Statement Number",
	   "Account Statement Page" as "Account Statement Page",
	   "Vat Key" as "Vat Key",
	   "Days For Cash Discount" as "Days For Cash Discount",
	   "Day Of Actual Accounting" as "Day Of Actual Accounting",
	   "Skr51 Branch" as "Skr51 Branch",
	   "Skr51 Make" as "Skr51 Make",
	   "Skr51 Cost Center" as "Skr51 Cost Center",
	   "Skr51 Sales Channel" as "Skr51 Sales Channel",
	   "Skr51 Cost Unit" as "Skr51 Cost Unit",
	   "Previously Used Account No" as "Previously Used Account No",
	   "Free Form Accounting Text" as "Free Form Accounting Text",
	   "Free Form Document Text" as "Free Form Document Text",
	   "Rechtseinheit" as "Rechtseinheit",
	   "Betrieb" as "Betrieb",
	   "Bookkeep Date" as "Bookkeep Date",
	   "Text" as "Text",
	   "Mandant" as "Mandant",
	   "Betrag" as "Betrag",
	   "Menge" as "Menge",
	   "Acct Nr" as "Acct Nr",
	   "Customer Number_suctomer_supplier" as "Customer Number_suctomer_supplier",
	   "Is Supplier" as "Is Supplier",
	   "First Name" as "First Name",
	   "Family Name" as "Family Name",
	   "Document Type In Journal" as "Document Type In Journal",
	   "Document Type Description" as "Document Type Description",
	   SUM("Betrag") OVER (partition by "Customer Number_suctomer_supplier") as "Debitorsaldo",
	   "Invoice Type_Invoices" as "Invoice Type_Invoices",
	   "Invoice Number_Invoices" as "Invoice Number_Invoices",
	   "Subsidiary_Invoices" as "Subsidiary_Invoices",
	   "Invoice Date_Invoices" as "Invoice Date_Invoices",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort_ori" as "Standort_ori",
	   "Sel Name" as "Sel Name",
	   "OP-Saldo_" as "OP-Saldo_",
	   "Kostenstelle" as "Kostenstelle",
	   "Tage" as "Tage",
	   "Staffel" as "Staffel",
	   "Kunde" as "Kunde",
	   "Beleg" as "Beleg",
	   "Employee Number_Employees_journal_accountings" as "Employee Number_Employees_journal_accountings",
	   "Name_Employees_journal_accountings" as "Name_Employees_journal_accountings",
	   "Anzahl_Stellen_Invoice_Number" as "Anzahl_Stellen_Invoice_Number",
	   "Forderungsart_aus_erste_Stelle_Inv_Number" as "Forderungsart_aus_erste_Stelle_Inv_Number",
	   "Forderungsart" as "Forderungsart",
	   "Standort_aus_2_Stelle_Invoice_Number" as "Standort_aus_2_Stelle_Invoice_Number",
	   "Standort" as "Standort"
from 
(select c112 as "Accounting Date",
	   c154 as "Document Type",
	   c153 as "Document Number",
	   c152 as "Position In Document",
	   c109 as "Nominal Account Number",
	   c151 as "Customer Number",
	   c150 as "Is Balanced",
	   c149 as "Clearing Number",
	   c148 as "Document Date",
	   c147 as "Posted Value",
	   c146 as "Debit Or Credit",
	   c145 as "Posted Count",
	   c144 as "Branch Number",
	   c143 as "Customer Contra Account",
	   c142 as "Nominal Contra Account",
	   c141 as "Contra Account Text",
	   c140 as "Account Form Page Number",
	   c139 as "Account Form Page Line",
	   c138 as "Serial Number Each Month",
	   c137 as "Employee Number",
	   c136 as "Invoice Date",
	   c135 as "Invoice Number",
	   c134 as "Dunning Level",
	   c133 as "Last Dunning Date",
	   c132 as "Journal Page",
	   c131 as "Journal Line",
	   c130 as "Cash Discount",
	   c129 as "Term Of Payment",
	   c128 as "Posting Text",
	   c127 as "Vehicle Reference",
	   c126 as "Vat Id Number",
	   c125 as "Account Statement Number",
	   c124 as "Account Statement Page",
	   c123 as "Vat Key",
	   c122 as "Days For Cash Discount",
	   c121 as "Day Of Actual Accounting",
	   c120 as "Skr51 Branch",
	   c119 as "Skr51 Make",
	   c118 as "Skr51 Cost Center",
	   c117 as "Skr51 Sales Channel",
	   c116 as "Skr51 Cost Unit",
	   c115 as "Previously Used Account No",
	   c114 as "Free Form Accounting Text",
	   c113 as "Free Form Document Text",
	   '1' as "Rechtseinheit",
	   c84 as "Betrieb",
	   c112 as "Bookkeep Date",
	   c111 as "Text",
	   '1' as "Mandant",
	   c96 as "Betrag",
	   c110 as "Menge",
	   c109 as "Acct Nr",
	   c83 as "Customer Number_suctomer_supplier",
	   c108 as "Is Supplier",
	   c107 as "First Name",
	   c106 as "Family Name",
	   c105 as "Document Type In Journal",
	   c104 as "Document Type Description",
	   c103 as "Invoice Type_Invoices",
	   c102 as "Invoice Number_Invoices",
	   c101 as "Subsidiary_Invoices",
	   c100 as "Invoice Date_Invoices",
	   c99 as "Hauptbetrieb",
	   c98 as "Standort_ori",
	   c97 as "Sel Name",
	   c96 as "OP-Saldo_",
	   c95 as "Kostenstelle",
	   c94 as "Tage",
	   c93 as "Staffel",
	   c92 as "Kunde",
	   c91 as "Beleg",
	   c90 as "Employee Number_Employees_journal_accountings",
	   c89 as "Name_Employees_journal_accountings",
	   c88 as "Anzahl_Stellen_Invoice_Number",
	   c87 as "Forderungsart_aus_erste_Stelle_Inv_Number",
	   c86 as "Forderungsart",
	   c85 as "Standort_aus_2_Stelle_Invoice_Number",
	   c84 as "Standort",
	   SUM(c96) OVER (partition by c83) as c80
from 
(select T3."customer_number" as c83,
	   ('0' + ((convert(varchar(50), T1."branch_number")))) as c84,
	   CASE WHEN ((((len((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END)))) = 7) and ((substring((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END), 2, 1)) BETWEEN '1' AND '4')) THEN ('0' + (substring((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END), 2, 1))) ELSE null END as c85,
	   CASE WHEN ((CASE WHEN (T5."invoice_type" IN (2,4,6,3)) THEN ('3') WHEN (T5."invoice_type" IN (5)) THEN ('6') WHEN (T5."invoice_type" IN (7)) THEN ('1') WHEN (T5."invoice_type" IN (8)) THEN ('2') WHEN (T4."document_type_in_journal" = 'R') THEN ('7') ELSE null END) = '1') THEN ('Neuwagen') WHEN ((CASE WHEN (T5."invoice_type" IN (2,4,6,3)) THEN ('3') WHEN (T5."invoice_type" IN (5)) THEN ('6') WHEN (T5."invoice_type" IN (7)) THEN ('1') WHEN (T5."invoice_type" IN (8)) THEN ('2') WHEN (T4."document_type_in_journal" = 'R') THEN ('7') ELSE null END) = '2') THEN ('Gebrauchtwagen') WHEN ((CASE WHEN (T5."invoice_type" IN (2,4,6,3)) THEN ('3') WHEN (T5."invoice_type" IN (5)) THEN ('6') WHEN (T5."invoice_type" IN (7)) THEN ('1') WHEN (T5."invoice_type" IN (8)) THEN ('2') WHEN (T4."document_type_in_journal" = 'R') THEN ('7') ELSE null END) = '3') THEN ('Service') WHEN ((CASE WHEN (T5."invoice_type" IN (2,4,6,3)) THEN ('3') WHEN (T5."invoice_type" IN (5)) THEN ('6') WHEN (T5."invoice_type" IN (7)) THEN ('1') WHEN (T5."invoice_type" IN (8)) THEN ('2') WHEN (T4."document_type_in_journal" = 'R') THEN ('7') ELSE null END) = '6') THEN ('Teile') ELSE ('nicht zuzuordnen') END as c86,
	   CASE WHEN ((((len((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END)))) = 7) and ((substring((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END), 1, 1)) IN ('2','3','4'))) THEN ('Service') WHEN ((((len((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END)))) = 7) and ((substring((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END), 1, 1)) IN ('5'))) THEN ('Teile') WHEN ((((len((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END)))) = 7) and ((substring((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END), 1, 1)) IN ('6'))) THEN ('Garantie') WHEN ((((len((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END)))) = 7) and ((substring((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END), 1, 1)) IN ('7'))) THEN ('Neuwagen') WHEN ((((len((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END)))) = 7) and ((substring((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END), 1, 1)) IN ('8'))) THEN ('Gebrauchtwagen') WHEN (T1."document_type" = 'R') THEN ('Rent') ELSE ('nicht zuzuordnen') END as c87,
	   (len((CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END))) as c88,
	   T2."name" as c89,
	   T2."employee_number" as c90,
	   ((CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) + '/' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END)) as c91,
	   CASE WHEN (T3."first_name" IS NOT NULL) THEN ((left(((convert(varchar(50), T3."customer_number"))))) + ' - ' + T3."first_name" + ' ' + T3."family_name") ELSE ((left(((convert(varchar(50), T3."customer_number"))))) + ' ' + T3."family_name") END as c92,
	   CASE WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((day((getdate()) - (convert(datetime, T1."accounting_date"))))) > 84) THEN ('> 12 Wochen') ELSE null END as c93,
	   (day((getdate()) - (convert(datetime, T1."accounting_date")))) as c94,
	   CASE WHEN (T5."invoice_type" IN (2,4,6,3)) THEN ('3') WHEN (T5."invoice_type" IN (5)) THEN ('6') WHEN (T5."invoice_type" IN (7)) THEN ('1') WHEN (T5."invoice_type" IN (8)) THEN ('2') WHEN (T4."document_type_in_journal" = 'R') THEN ('7') ELSE null END as c95,
	   (CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END) as c96,
	   (left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name" as c97,
	   CASE WHEN (T5."subsidiary" IS NOT NULL) THEN ('0' + (left(((convert(varchar(50), T5."subsidiary")))))) ELSE ('nicht zuzuordnen') END as c98,
	   CASE WHEN ((('0' + ((convert(varchar(50), T1."branch_number"))))) IN ('015                                                                                                                                                                                                                                                            ')) THEN ('15') ELSE ('1') END as c99,
	   T5."invoice_date" as c100,
	   T5."subsidiary" as c101,
	   T5."invoice_number" as c102,
	   T5."invoice_type" as c103,
	   T4."document_type_description" as c104,
	   T4."document_type_in_journal" as c105,
	   T3."family_name" as c106,
	   T3."first_name" as c107,
	   T3."is_supplier" as c108,
	   T1."nominal_account_number" as c109,
	   CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_count" / 100 * -1) ELSE (T1."posted_count" / 100) END as c110,
	   CASE WHEN ((CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) IS NOT NULL) THEN ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) + '/' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) ELSE ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) END as c111,
	   T1."accounting_date" as c112,
	   CASE WHEN ((((((CASE WHEN ((CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) IS NOT NULL) THEN ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) + '/' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) ELSE ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) END) LIKE '%RG-BONUS%') or ((CASE WHEN ((CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) IS NOT NULL) THEN ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) + '/' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) ELSE ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) END) LIKE '%GEBURTSTAGS%')) or ((CASE WHEN ((CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) IS NOT NULL) THEN ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) + '/' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) ELSE ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) END) LIKE '%STARTGUTHABEN%')) or ((CASE WHEN ((CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) IS NOT NULL) THEN ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) + '/' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) ELSE ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) END) LIKE '%BONUS F%')) or ((CASE WHEN ((CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) IS NOT NULL) THEN ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END) + '/' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) ELSE ((rtrim((convert(varchar(50), ((T1."document_number")))))) + ' - ' + (CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + ((left(((convert(varchar(50), T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(varchar(50), year(T1."document_date")) + '-' + convert(varchar(50), month(T1."document_date")) + '-' + convert(varchar(50), day(T1."document_date")))) END) LIKE '%RG-ABZUG%')) THEN ('Bonus/Startguthaben') ELSE ('ohne Bonus/Startguthaben') END as c113,
	   T1."free_form_accounting_text" as c114,
	   T1."previously_used_account_no" as c115,
	   T1."skr51_cost_unit" as c116,
	   T1."skr51_sales_channel" as c117,
	   T1."skr51_cost_center" as c118,
	   T1."skr51_make" as c119,
	   T1."skr51_branch" as c120,
	   T1."day_of_actual_accounting" as c121,
	   (convert(varchar(50), year(T1."accounting_date")) + '-' + convert(varchar(50), month(T1."accounting_date")) + '-' + convert(varchar(50), day(T1."accounting_date"))) as c122,
	   T1."vat_key" as c123,
	   T1."account_statement_page" as c124,
	   T1."account_statement_number" as c125,
	   T1."vat_id_number" as c126,
	   CASE WHEN ((T1."vehicle_reference" IS NULL) or (T1."vehicle_reference" = ' ')) THEN (' - ') ELSE (T1."vehicle_reference") END as c127,
	   CASE WHEN ((T1."posting_text" IS NULL) or (T1."posting_text" = ' ')) THEN (' - ') ELSE (T1."posting_text") END as c128,
	   T1."term_of_payment" as c129,
	   T1."cash_discount" as c130,
	   T1."journal_line" as c131,
	   T1."journal_page" as c132,
	   T1."last_dunning_date" as c133,
	   T1."dunning_level" as c134,
	   CASE WHEN ((T1."invoice_number" IS NULL) or (T1."invoice_number" = ' ')) THEN (' - ') ELSE (T1."invoice_number") END as c135,
	   T1."invoice_date" as c136,
	   T1."employee_number" as c137,
	   T1."serial_number_each_month" as c138,
	   T1."account_form_page_line" as c139,
	   T1."account_form_page_number" as c140,
	   T1."contra_account_text" as c141,
	   T1."nominal_contra_account" as c142,
	   T1."customer_contra_account" as c143,
	   T1."branch_number" as c144,
	   T1."posted_count" as c145,
	   T1."debit_or_credit" as c146,
	   T1."posted_value" as c147,
	   T1."document_date" as c148,
	   T1."clearing_number" as c149,
	   T1."is_balanced" as c150,
	   T1."customer_number" as c151,
	   T1."position_in_document" as c152,
	   (T1."document_number") as c153,
	   T1."document_type" as c154
from (((("journal_accountings" T1 left outer join "LOCOSOFT"."dbo"."employees" T2 on T1."employee_number" = T2."employee_number") left outer join "LOCOSOFT"."dbo"."customers_suppliers" T3 on T3."customer_number" = T1."customer_number") left outer join "document_types" T4 on T4."document_type_in_journal" = T1."document_type") left outer join "invoices" T5 on T5."invtype_invnr" = T1."invoice_number")
where (((((T1."nominal_account_number" IN (1400,1409,1451,1408,1525)) and (T1."clearing_number" = 0)) and (T1."customer_number" IS NOT NULL)) and (not T4."document_type_in_journal" IN ('A','E','U','V'))) and (T3."customer_number" IS NOT NULL))
) D2
) D1
where (c80 <> 0)
-- order by "Customer Number" asc,"Document Type In Journal" asc,"Nominal Account Number" asc