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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_AUTOLINE
DATASOURCENAME,C:\GlobalCube\System\Autoline\IQD\op\Offene_Posten_Autoline.imr
TITLE,Offene_Posten_Autoline.imr
BEGIN SQL
select c92 as c1,
	   c171 as c2,
	   c170 as c3,
	   c169 as c4,
	   c168 as c5,
	   c106 as c6,
	   c96 as c7,
	   c167 as c8,
	   c166 as c9,
	   c165 as c10,
	   c164 as c11,
	   c163 as c12,
	   c162 as c13,
	   c161 as c14,
	   c160 as c15,
	   c159 as c16,
	   c158 as c17,
	   c157 as c18,
	   c156 as c19,
	   c155 as c20,
	   c154 as c21,
	   c153 as c22,
	   c152 as c23,
	   c151 as c24,
	   c150 as c25,
	   c149 as c26,
	   c148 as c27,
	   c147 as c28,
	   c146 as c29,
	   c145 as c30,
	   c144 as c31,
	   c143 as c32,
	   c142 as c33,
	   c141 as c34,
	   c140 as c35,
	   c139 as c36,
	   c138 as c37,
	   c137 as c38,
	   c136 as c39,
	   c135 as c40,
	   c134 as c41,
	   c133 as c42,
	   c132 as c43,
	   c131 as c44,
	   c130 as c45,
	   c129 as c46,
	   c112 as c47,
	   c128 as c48,
	   c127 as c49,
	   c107 as c50,
	   c126 as c51,
	   c125 as c52,
	   c124 as c53,
	   c123 as c54,
	   c122 as c55,
	   c113 as c56,
	   c121 as c57,
	   c120 as c58,
	   c119 as c59,
	   c118 as c60,
	   c117 as c61,
	   c116 as c62,
	   c115 as c63,
	   c114 as c64,
	   c113 as c65,
	   '' as c66,
	   c112 as c67,
	   c111 as c68,
	   c110 as c69,
	   c109 as c70,
	   c108 as c71,
	   c107 as c72,
	   c106 as c73,
	   c105 as c74,
	   c104 as c75,
	   c103 as c76,
	   c103 as c77,
	   c102 as c78,
	   c101 as c79,
	   c100 as c80,
	   c99 as c81,
	   c93 as c82,
	   c98 as c83,
	   c97 as c84,
	   c96 as c85,
	   XSUM(c97 for c92) as c86,
	   c95 as c87,
	   c94 as c88,
	   c93 as c89
from 
(select T1."AccountCode" as c92,
	   T4."CategoryCode" as c93,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."DueDate"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((extract(DAY FROM (now()) - T1."DueDate"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((extract(DAY FROM (now()) - T1."DueDate"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((extract(DAY FROM (now()) - T1."DueDate"))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((extract(DAY FROM (now()) - T1."DueDate"))) > 84) THEN ('> 12 Wochen') WHEN (((extract(DAY FROM (now()) - T1."DueDate"))) < 0) THEN ('noch nicht fällig') ELSE null END as c94,
	   (extract(DAY FROM (now()) - T1."DueDate")) as c95,
	   T1."DocumentBaseTotal" as c96,
	   T1."DocumentBaseTotal" - T1."AmountPaidBase" as c97,
	   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 c98,
	   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 c99,
	   T9."UserName" as c100,
	   T8."UserName" as c101,
	   T6."CreatingOperator" as c102,
	   T7."UserName" as c103,
	   T6."InvoicingOperator" as c104,
	   T6."WIPNumber" as c105,
	   T1."DueDate" as c106,
	   T1."LastLetterType" as c107,
	   (rtrim((cast_numberToString(cast_integer(T1."DocumentNumber"))))) || ' - ' || (asciiz(extract(YEAR FROM T1."DocumentDate"),4) || '-' || asciiz(extract(MONTH FROM T1."DocumentDate"),2) || '-' || asciiz(extract(DAY FROM T1."DocumentDate"),2)) || ' - ' || T1."CustomerReference" || ' - ' || T1."MiscellaneousRef" || ' - MS: ' || T1."LastLetterType" as c108,
	   T5."LocationCode" || ' - ' || T5."Description" as c109,
	   T5."Description" as c110,
	   T5."LocationCode" as c111,
	   T1."TruePostingOrigin" as c112,
	   T1."Client_DB" as c113,
	   T1."AccountCode" || ' - ' || T4."CustomerSName" as c114,
	   T4."Postcode" as c115,
	   T4."CustomerSName" as c116,
	   T4."AccountCode" as c117,
	   T3."SalesInvoiceNumber" as c118,
	   T2."SalesInvoiceNumber" as c119,
	   T3."SalesExecutive001" as c120,
	   T2."SalesExecutive001" as c121,
	   T1."OriginalInvoice" as c122,
	   T1."SocialIDVatReg" as c123,
	   T1."PaymentReference" as c124,
	   T1."PaymentTypeCode" as c125,
	   T1."ControlAccountType" as c126,
	   T1."LastLetterDate" as c127,
	   T1."TRUEMOD" as c128,
	   T1."TaxBillPending" as c129,
	   T1."TaxBillNumber" as c130,
	   T1."TaxBillDate" as c131,
	   T1."BOEMagicNumber" as c132,
	   T1."USTaxCodes002" as c133,
	   T1."USTaxCodes001" as c134,
	   T1."DocumentArchiveID" as c135,
	   T1."SourceBranch" as c136,
	   T1."SourceModule" as c137,
	   T1."AllowanceCurrency" as c138,
	   T1."AllowanceBase" as c139,
	   T1."SystemMatchNumber" as c140,
	   T1."PRFollowUpDate" as c141,
	   T1."PRSentTo" as c142,
	   T1."PRComment" as c143,
	   T1."PRDateRaised" as c144,
	   T1."PRReference" as c145,
	   T1."PRCode" as c146,
	   T1."MiscellaneousRef" as c147,
	   T1."SettledFlag" as c148,
	   T1."StatementPrintedYN" as c149,
	   T1."DebtFlag" as c150,
	   T1."SDiscountDate002" as c151,
	   T1."SDiscountDate001" as c152,
	   T1."SDiscountOffered002" as c153,
	   T1."SDiscountOffered001" as c154,
	   T1."Status" as c155,
	   T1."LastPmtDaybookItm" as c156,
	   T1."LastPmtDaybookNo" as c157,
	   T1."LastPaymentRef" as c158,
	   T1."LastPaymentDate" as c159,
	   T1."AmountPaidCurr" as c160,
	   T1."AmountPaidBase" as c161,
	   T1."PostingDaybkDate" as c162,
	   T1."DaybookItemNumber" as c163,
	   T1."DaybookNumber" as c164,
	   T1."VatAmountCurr" as c165,
	   T1."VATAmountBase" as c166,
	   T1."DocumentCurrTotal" as c167,
	   T1."DocumentType" as c168,
	   T1."DocumentDate" as c169,
	   T1."DocumentNumber" as c170,
	   T1."CustomerReference" as c171
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" from 6 for 5)) = (cast_numberToString(cast_integer(T6."WIPNumber"))))) left outer join "AUTOLINE"."import"."GB_UserDetails" T7 on ((cast_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 c92 asc
) D1

END SQL
COLUMN,0,Accountcode
COLUMN,1,Customerreference
COLUMN,2,Documentnumber
COLUMN,3,Documentdate
COLUMN,4,Documenttype
COLUMN,5,Duedate
COLUMN,6,Documentbasetotal
COLUMN,7,Documentcurrtotal
COLUMN,8,Vatamountbase
COLUMN,9,Vatamountcurr
COLUMN,10,Daybooknumber
COLUMN,11,Daybookitemnumber
COLUMN,12,Postingdaybkdate
COLUMN,13,Amountpaidbase
COLUMN,14,Amountpaidcurr
COLUMN,15,Lastpaymentdate
COLUMN,16,Lastpaymentref
COLUMN,17,Lastpmtdaybookno
COLUMN,18,Lastpmtdaybookitm
COLUMN,19,Status
COLUMN,20,Sdiscountoffered001
COLUMN,21,Sdiscountoffered002
COLUMN,22,Sdiscountdate001
COLUMN,23,Sdiscountdate002
COLUMN,24,Debtflag
COLUMN,25,Statementprintedyn
COLUMN,26,Settledflag
COLUMN,27,Miscellaneousref
COLUMN,28,Prcode
COLUMN,29,Prreference
COLUMN,30,Prdateraised
COLUMN,31,Prcomment
COLUMN,32,Prsentto
COLUMN,33,Prfollowupdate
COLUMN,34,Systemmatchnumber
COLUMN,35,Allowancebase
COLUMN,36,Allowancecurrency
COLUMN,37,Sourcemodule
COLUMN,38,Sourcebranch
COLUMN,39,Documentarchiveid
COLUMN,40,Ustaxcodes001
COLUMN,41,Ustaxcodes002
COLUMN,42,Boemagicnumber
COLUMN,43,Taxbilldate
COLUMN,44,Taxbillnumber
COLUMN,45,Taxbillpending
COLUMN,46,Truepostingorigin
COLUMN,47,Truemod
COLUMN,48,Lastletterdate
COLUMN,49,Lastlettertype
COLUMN,50,Controlaccounttype
COLUMN,51,Paymenttypecode
COLUMN,52,Paymentreference
COLUMN,53,Socialidvatreg
COLUMN,54,Originalinvoice
COLUMN,55,Client Db
COLUMN,56,Salesexecutive001_VS_NewVehicle
COLUMN,57,Salesexecutive001_VS_UsedVehicle
COLUMN,58,Salesinvoicenumber_VS_NewVehicle
COLUMN,59,Salesinvoicenumber_VS_UsedVehicle
COLUMN,60,Accountcode
COLUMN,61,Customersname
COLUMN,62,Postcode
COLUMN,63,Kunde
COLUMN,64,Hauptbetrieb_ID
COLUMN,65,Hauptbetrieb_Name
COLUMN,66,Standort_ID
COLUMN,67,Locationcode_GB_Company
COLUMN,68,Description_GB_Company
COLUMN,69,Standort_Name
COLUMN,70,Beleg
COLUMN,71,Mahnstufe
COLUMN,72,Bookkeep Date
COLUMN,73,Wipnumber
COLUMN,74,Invoicingoperator
COLUMN,75,Username_GB_User_Service
COLUMN,76,Serviceberater
COLUMN,77,Creatingoperator
COLUMN,78,Username_GB_User_NewVehicle
COLUMN,79,Username_GB_User_Usedvehicle
COLUMN,80,Sel Name
COLUMN,81,Forderungsart
COLUMN,82,Bereich
COLUMN,83,offen
COLUMN,84,Rechnungsbetrag
COLUMN,85,Gesamt offen KD (Info)
COLUMN,86,Tage
COLUMN,87,Staffel
COLUMN,88,Categorycode_SL_SalesledgerAccount