C11/System/AUTOLINE/IQD/OP/Offene_Posten_Autoline.sql

select "Accountcode_2",
	   "Customerreference",
	   "Documentnumber",
	   "Documentdate",
	   "Documenttype",
	   "Duedate",
	   "Documentbasetotal",
	   "Documentcurrtotal",
	   "Vatamountbase",
	   "Vatamountcurr",
	   "Daybooknumber",
	   "Daybookitemnumber",
	   "Postingdaybkdate",
	   "Amountpaidbase",
	   "Amountpaidcurr",
	   "Lastpaymentdate",
	   "Lastpaymentref",
	   "Lastpmtdaybookno",
	   "Lastpmtdaybookitm",
	   "Status",
	   "Sdiscountoffered001",
	   "Sdiscountoffered002",
	   "Sdiscountdate001",
	   "Sdiscountdate002",
	   "Debtflag",
	   "Statementprintedyn",
	   "Settledflag",
	   "Miscellaneousref",
	   "Prcode",
	   "Prreference",
	   "Prdateraised",
	   "Prcomment",
	   "Prsentto",
	   "Prfollowupdate",
	   "Systemmatchnumber",
	   "Allowancebase",
	   "Allowancecurrency",
	   "Sourcemodule",
	   "Sourcebranch",
	   "Documentarchiveid",
	   "Ustaxcodes001",
	   "Ustaxcodes002",
	   "Boemagicnumber",
	   "Taxbilldate",
	   "Taxbillnumber",
	   "Taxbillpending",
	   "Truepostingorigin",
	   "Truemod",
	   "Lastletterdate",
	   "Lastlettertype",
	   "Controlaccounttype",
	   "Paymenttypecode",
	   "Paymentreference",
	   "Socialidvatreg",
	   "Originalinvoice",
	   "Client Db",
	   "Salesexecutive001_VS_NewVehicle",
	   "Salesexecutive001_VS_UsedVehicle",
	   "Salesinvoicenumber_VS_NewVehicle",
	   "Salesinvoicenumber_VS_UsedVehicle",
	   "Accountcode",
	   "Customersname",
	   "Postcode",
	   "Kunde",
	   "Client Db" as "Hauptbetrieb_ID",
	   '' as "Hauptbetrieb_Name",
	   "Truepostingorigin" as "Standort_ID",
	   "Locationcode_GB_Company",
	   "Description_GB_Company",
	   "Standort_Name",
	   "Beleg",
	   "Lastlettertype" as "Mahnstufe",
	   "Duedate" as "Bookkeep Date",
	   "Wipnumber",
	   "Invoicingoperator",
	   "Username_GB_User_Service",
	   "Username_GB_User_Service" as "Serviceberater",
	   "Creatingoperator",
	   "Username_GB_User_NewVehicle",
	   "Username_GB_User_Usedvehicle",
	   "Sel Name",
	   "Forderungsart",
	   "Bereich",
	   "offen",
	   "Documentbasetotal" as "Rechnungsbetrag",
	   SUM("offen") OVER (partition by "Accountcode_2") as "Gesamt offen KD (Info)",
	   "Tage",
	   "Staffel",
	   "Forderungsart" as "Categorycode_SL_SalesledgerAccount"
from 
(select T1."AccountCode" as "Accountcode_2",
	   T4."CategoryCode" as "Forderungsart",
	   CASE WHEN (((-1 * datediff(day, (getdate()), T1."DueDate"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((-1 * datediff(day, (getdate()), T1."DueDate"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((-1 * datediff(day, (getdate()), T1."DueDate"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((-1 * datediff(day, (getdate()), T1."DueDate"))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((-1 * datediff(day, (getdate()), T1."DueDate"))) > 84) THEN ('> 12 Wochen') WHEN (((-1 * datediff(day, (getdate()), T1."DueDate"))) < 0) THEN ('noch nicht fällig') ELSE null END as "Staffel",
	   (-1 * datediff(day, (getdate()), T1."DueDate")) as "Tage",
	   T1."DocumentBaseTotal" as "Documentbasetotal",
	   T1."DocumentBaseTotal" - T1."AmountPaidBase" as "offen",
	   CASE WHEN (T2."SalesExecutive001" IS NOT NULL) THEN ('Neuwagen') WHEN (T3."SalesExecutive001" IS NOT NULL) THEN ('Gebrauchtwagen') WHEN (T6."WIPNumber" IS NOT NULL) THEN ('Aftersales') ELSE ('nicht zuzuordnen') END as "Bereich",
	   CASE WHEN (T7."UserName" IS NOT NULL) THEN (T7."UserName") WHEN (T8."UserName" IS NOT NULL) THEN (T8."UserName") WHEN (T9."UserName" IS NOT NULL) THEN (T9."UserName") ELSE ('n.N.') END as "Sel Name",
	   T9."UserName" as "Username_GB_User_Usedvehicle",
	   T8."UserName" as "Username_GB_User_NewVehicle",
	   T6."CreatingOperator" as "Creatingoperator",
	   T7."UserName" as "Username_GB_User_Service",
	   T6."InvoicingOperator" as "Invoicingoperator",
	   T6."WIPNumber" as "Wipnumber",
	   T1."DueDate" as "Duedate",
	   T1."LastLetterType" as "Lastlettertype",
	   (rtrim((((T1."DocumentNumber"))))) + ' - ' + (convert(varchar(50), year(T1."DocumentDate")) + '-' + convert(varchar(50), month(T1."DocumentDate")) + '-' + convert(varchar(50), day(T1."DocumentDate"))) + ' - ' + T1."CustomerReference" + ' - ' + T1."MiscellaneousRef" + ' - MS: ' + T1."LastLetterType" as "Beleg",
	   T5."LocationCode" + ' - ' + T5."Description" as "Standort_Name",
	   T5."Description" as "Description_GB_Company",
	   T5."LocationCode" as "Locationcode_GB_Company",
	   T1."TruePostingOrigin" as "Truepostingorigin",
	   T1."Client_DB" as "Client Db",
	   T1."AccountCode" + ' - ' + T4."CustomerSName" as "Kunde",
	   T4."Postcode" as "Postcode",
	   T4."CustomerSName" as "Customersname",
	   T4."AccountCode" as "Accountcode",
	   T3."SalesInvoiceNumber" as "Salesinvoicenumber_VS_UsedVehicle",
	   T2."SalesInvoiceNumber" as "Salesinvoicenumber_VS_NewVehicle",
	   T3."SalesExecutive001" as "Salesexecutive001_VS_UsedVehicle",
	   T2."SalesExecutive001" as "Salesexecutive001_VS_NewVehicle",
	   T1."OriginalInvoice" as "Originalinvoice",
	   T1."SocialIDVatReg" as "Socialidvatreg",
	   T1."PaymentReference" as "Paymentreference",
	   T1."PaymentTypeCode" as "Paymenttypecode",
	   T1."ControlAccountType" as "Controlaccounttype",
	   T1."LastLetterDate" as "Lastletterdate",
	   T1."TRUEMOD" as "Truemod",
	   T1."TaxBillPending" as "Taxbillpending",
	   T1."TaxBillNumber" as "Taxbillnumber",
	   T1."TaxBillDate" as "Taxbilldate",
	   T1."BOEMagicNumber" as "Boemagicnumber",
	   T1."USTaxCodes002" as "Ustaxcodes002",
	   T1."USTaxCodes001" as "Ustaxcodes001",
	   T1."DocumentArchiveID" as "Documentarchiveid",
	   T1."SourceBranch" as "Sourcebranch",
	   T1."SourceModule" as "Sourcemodule",
	   T1."AllowanceCurrency" as "Allowancecurrency",
	   T1."AllowanceBase" as "Allowancebase",
	   T1."SystemMatchNumber" as "Systemmatchnumber",
	   T1."PRFollowUpDate" as "Prfollowupdate",
	   T1."PRSentTo" as "Prsentto",
	   T1."PRComment" as "Prcomment",
	   T1."PRDateRaised" as "Prdateraised",
	   T1."PRReference" as "Prreference",
	   T1."PRCode" as "Prcode",
	   T1."MiscellaneousRef" as "Miscellaneousref",
	   T1."SettledFlag" as "Settledflag",
	   T1."StatementPrintedYN" as "Statementprintedyn",
	   T1."DebtFlag" as "Debtflag",
	   T1."SDiscountDate002" as "Sdiscountdate002",
	   T1."SDiscountDate001" as "Sdiscountdate001",
	   T1."SDiscountOffered002" as "Sdiscountoffered002",
	   T1."SDiscountOffered001" as "Sdiscountoffered001",
	   T1."Status" as "Status",
	   T1."LastPmtDaybookItm" as "Lastpmtdaybookitm",
	   T1."LastPmtDaybookNo" as "Lastpmtdaybookno",
	   T1."LastPaymentRef" as "Lastpaymentref",
	   T1."LastPaymentDate" as "Lastpaymentdate",
	   T1."AmountPaidCurr" as "Amountpaidcurr",
	   T1."AmountPaidBase" as "Amountpaidbase",
	   T1."PostingDaybkDate" as "Postingdaybkdate",
	   T1."DaybookItemNumber" as "Daybookitemnumber",
	   T1."DaybookNumber" as "Daybooknumber",
	   T1."VatAmountCurr" as "Vatamountcurr",
	   T1."VATAmountBase" as "Vatamountbase",
	   T1."DocumentCurrTotal" as "Documentcurrtotal",
	   T1."DocumentType" as "Documenttype",
	   T1."DocumentDate" as "Documentdate",
	   T1."DocumentNumber" as "Documentnumber",
	   T1."CustomerReference" as "Customerreference"
from (((((((("AUTOLINE"."import"."SL_SLTransactions" T1 left outer join "AUTOLINE"."import"."VS_NewVehicleStock" T2 on (T2."SalesInvoiceNumber" = T1."DocumentNumber") and (T2."Client_DB" = T1."Client_DB")) left outer join "AUTOLINE"."import"."VS_UsedVehicleStock" T3 on (T1."DocumentNumber" = T3."SalesInvoiceNumber") and (T1."Client_DB" = T3."Client_DB")) left outer join "AUTOLINE"."import"."SL_SalesLedgerAccounts" T4 on (T1."AccountCode" = T4."AccountCode") and (T1."Client_DB" = T4."Client_DB")) left outer join "AUTOLINE"."import"."GB_CompanyLocations" T5 on T5."LocationCode" = T1."TruePostingOrigin") left outer join "AUTOLINE"."import"."SO_HeaderRecords" T6 on (T1."TruePostingOrigin" = T6."Client_DB") and ((substring(T1."MiscellaneousRef", 6, 5)) = (((T6."WIPNumber"))))) left outer join "AUTOLINE"."import"."GB_UserDetails" T7 on ((convert(float, T6."CreatingOperator")) = T7."POSOperator") and ((T6."Department" = T7."CRMDepartment") or (T7."POSOperator" = 888))) left outer join "AUTOLINE"."import"."GB_UserDetails" T8 on T8."UserID" = T2."SalesExecutive001") left outer join "AUTOLINE"."import"."GB_UserDetails" T9 on T9."UserID" = T3."SalesExecutive001")
where (((T1."AmountPaidBase" <> T1."DocumentBaseTotal") and (T1."DocumentBaseTotal" <> 0)) and (not T1."AccountCode" IN ('000999')))
-- order by "Accountcode_2" asc
) D1