GuV_8_O21_STK_hist.iqd 5.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154
  1. COGNOS QUERY
  2. STRUCTURE,1,1
  3. DATABASE,O21
  4. DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\belege\GuV_8_O21_STK_hist.imr
  5. TITLE,GuV_8_O21_STK_hist.imr
  6. BEGIN SQL
  7. select T1."ACCT_NR" as c1,
  8. T1."LEDGER_ACCTS_NAME" as c2,
  9. T1."LEDGER_ACCTS_NAME2" as c3,
  10. T1."HANDLER" as c4,
  11. T1."DEPT_SPLIT" as c5,
  12. T1."TYPE_ACCTT" as c6,
  13. T2."ACCT_NO" as c7,
  14. T2."BOOKKEEP_DATE" as c8,
  15. T2."BOOKKEEP_PERIOD" as c9,
  16. T2."DOCUMENT_NO" as c10,
  17. T2."ORIGIN" as c11,
  18. T2."STATUS" as c12,
  19. T2."DEBIT_AMOUNT" as c13,
  20. T2."CREDIT_AMOUNT" as c14,
  21. T2."DEBIT_QUANTITY" as c15,
  22. T2."CREDIT_QUANTITY" as c16,
  23. T2."AA_TRTYPE" as c17,
  24. T2."DEPARTMENT" as c18,
  25. T2."STOCK" as c19,
  26. T2."MAKE_FAMILY" as c20,
  27. T2."MAKE" as c21,
  28. T2."VEHICLE_TYPE" as c22,
  29. T2."MODEL_LINE" as c23,
  30. T2."FACTORY_MODEL" as c24,
  31. T2."WORKSHOP_MODEL" as c25,
  32. T2."PRODUCT_GROUP" as c26,
  33. T2."REPAIR_GROUP" as c27,
  34. T2."KIT_GROUP" as c28,
  35. T2."TIME_CODE" as c29,
  36. T2."INT_VOUCHER_NO" as c30,
  37. T2."BALANCING_MARK" as c31,
  38. T2."USED_VEH_DEST_CODE" as c32,
  39. T2."USE_OF_VEHICLE" as c33,
  40. T2."ACCT_NO_NEXT_CHART" as c34,
  41. T3."REFERENCE_IDENT" as c35,
  42. T3."TRANSACT_DATE" as c36,
  43. T3."HANDLER" as c37,
  44. T3."PROGRAM" as c38,
  45. T3."FUNCTION_CODE" as c39,
  46. T3."MODUL" as c40,
  47. T3."DOCUMENT_KEY" as c41,
  48. T3."COMMENT" as c42,
  49. T4."DEPARTMENT_TYPE_ID" as c43,
  50. T4."DESCRIPTION" as c44,
  51. T4."DEPARTMENT_GROUP" as c45,
  52. T5."AA_TRTYPE_ID" as c46,
  53. T5."DESCRIPTION" as c47,
  54. T5."OWN_DESCRIPTION" as c48,
  55. '1' as c49,
  56. CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = ' ')) THEN ('01') ELSE ((substring(T4."DEPARTMENT_TYPE_ID" from 1 for 2))) END as c50,
  57. CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = '')) THEN ('1') ELSE ((substring(T4."DEPARTMENT_TYPE_ID" from 3 for 1))) END as c51,
  58. CASE WHEN (((T1."ACCT_NR" LIKE '8%') or (T1."ACCT_NR" LIKE '9%')) or (T1."ACCT_NR" = '29930000')) THEN ((T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT") * -1) ELSE (T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT") END as c52,
  59. (db_name()) as c53,
  60. T6."MAKE_TIME_UNIT" as c54,
  61. T6."INV_TIME" as c55,
  62. T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY" as c56,
  63. T6."ORDER_NUMBER" as c57,
  64. T6."LINE_NUMBER" as c58,
  65. (truncate(T1."ACCT_NR")) || '_STK' as c59,
  66. CASE WHEN ((extract(DAY FROM (getdate()) - T2."BOOKKEEP_DATE")) <= 90) THEN ((substring((ascii(T2."DOCUMENT_NO")) from 1 for 7)) || ' - ' || T3."COMMENT") ELSE null END as c60,
  67. 'STK' as c61,
  68. 'STK' as c62,
  69. T6."USED_TIME" as c63,
  70. T6."USED_TIME_INT" as c64,
  71. '1' as c65,
  72. 'Auto Jacob' as c66,
  73. CASE WHEN ((CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = ' ')) THEN ('01') ELSE ((substring(T4."DEPARTMENT_TYPE_ID" from 1 for 2))) END) = '02') THEN ((CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = ' ')) THEN ('01') ELSE ((substring(T4."DEPARTMENT_TYPE_ID" from 1 for 2))) END)) ELSE ('01') END as c67,
  74. CASE WHEN ((CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = ' ')) THEN ('01') ELSE ((substring(T4."DEPARTMENT_TYPE_ID" from 1 for 2))) END) = '02') THEN ('Groß-Gerau') ELSE ('Rüsselsheim') END as c68,
  75. 'Opel' as c69,
  76. '1' as c70
  77. from "OPTIMA"."dbo"."ACCOUNT_INFO" T1,
  78. (("OPTIMA"."dbo"."ACCT_DOC_KEY_01" T2 left outer join "OPTIMA"."dbo"."DEPARTMENT_TYPE" T4 on T2."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "OPTIMA"."dbo"."AA_TRTYPE" T5 on T2."AA_TRTYPE" = T5."AA_TRTYPE_ID"),
  79. (("OPTIMA"."dbo"."ACCT_DOC_DATA_01" T3 left outer join "OPTIMA"."dbo"."ACCT_DOC_SALESCLAS" T7 on T3."REFERENCE_IDENT" = T7."REFERENCE_IDENT") left outer join "OPTIMA"."dbo"."ORDER_LINE" T6 on (T6."ORDER_NUMBER" = T7."ORDER_NUMBER") and (T6."LINE_NUMBER" = T7."ORDER_LINE_NUMBER"))
  80. where (T2."ACCT_NO" = T1."ACCT_NR") and (T2."UNIQUE_IDENT" = T3."REFERENCE_IDENT")
  81. and ((((T1."TYPE_ACCTT" = '2') and (T1."ACCT_NR" LIKE '8%')) and ((T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY") <> 0)) and (T2."BOOKKEEP_DATE" >= TIMESTAMP '2016-01-01 00:00:00.000'))
  82. END SQL
  83. COLUMN,0,Acct Nr_ori
  84. COLUMN,1,Ledger Accts Name
  85. COLUMN,2,Ledger Accts Name2
  86. COLUMN,3,Handler
  87. COLUMN,4,Dept Split
  88. COLUMN,5,Type Acctt_ori
  89. COLUMN,6,Acct No
  90. COLUMN,7,Bookkeep Date
  91. COLUMN,8,Bookkeep Period
  92. COLUMN,9,Document No
  93. COLUMN,10,Origin
  94. COLUMN,11,Status
  95. COLUMN,12,Debit Amount
  96. COLUMN,13,Credit Amount
  97. COLUMN,14,Debit Quantity
  98. COLUMN,15,Credit Quantity
  99. COLUMN,16,Aa Trtype
  100. COLUMN,17,Department
  101. COLUMN,18,Stock
  102. COLUMN,19,Make Family
  103. COLUMN,20,Make
  104. COLUMN,21,Vehicle Type
  105. COLUMN,22,Model Line
  106. COLUMN,23,Factory Model
  107. COLUMN,24,Workshop Model
  108. COLUMN,25,Product Group
  109. COLUMN,26,Repair Group
  110. COLUMN,27,Kit Group
  111. COLUMN,28,Time Code
  112. COLUMN,29,Int Voucher No
  113. COLUMN,30,Balancing Mark
  114. COLUMN,31,Used Veh Dest Code
  115. COLUMN,32,Use Of Vehicle
  116. COLUMN,33,Acct No Next Chart
  117. COLUMN,34,Reference Ident
  118. COLUMN,35,Transact Date
  119. COLUMN,36,Handler
  120. COLUMN,37,Program
  121. COLUMN,38,Function Code
  122. COLUMN,39,Modul
  123. COLUMN,40,Document Key
  124. COLUMN,41,Comment
  125. COLUMN,42,Department Type Id
  126. COLUMN,43,Description
  127. COLUMN,44,Department Group
  128. COLUMN,45,Aa Trtype Id
  129. COLUMN,46,Description
  130. COLUMN,47,Own Description
  131. COLUMN,48,Rechtseinheit
  132. COLUMN,49,Betrieb
  133. COLUMN,50,Marke_alt
  134. COLUMN,51,Betrag_ori
  135. COLUMN,52,Mandant
  136. COLUMN,53,Make Time Unit
  137. COLUMN,54,Inv Time
  138. COLUMN,55,Betrag
  139. COLUMN,56,Order Number
  140. COLUMN,57,Line Number
  141. COLUMN,58,Acct Nr
  142. COLUMN,59,Text
  143. COLUMN,60,Susa
  144. COLUMN,61,Type Acctt
  145. COLUMN,62,Used Time
  146. COLUMN,63,Used Time Int
  147. COLUMN,64,Rechtseinheit_ID
  148. COLUMN,65,Rechtseinheit_Name
  149. COLUMN,66,Betrieb_ID
  150. COLUMN,67,Betrieb_Name
  151. COLUMN,68,Marke
  152. COLUMN,69,Fabrikat_Order_By