GuV_8_O21_STK_csv.iqd 5.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151
  1. COGNOS QUERY
  2. STRUCTURE,1,1
  3. DATABASE,O21
  4. DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\belege\GuV_8_O21_STK_csv.imr
  5. TITLE,GuV_8_O21_STK_csv.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. T1."CLIENT_DB" 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 '9%') and (T2."DEPARTMENT" = '')) THEN ('1') ELSE ((substring(T4."DEPARTMENT_TYPE_ID" from 3 for 1))) END as c51,
  58. (db_name()) as c52,
  59. T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY" as c53,
  60. CASE WHEN ((extract(DAY FROM (getdate()) - T2."BOOKKEEP_DATE")) <= 90) THEN ((substring((ascii(T2."DOCUMENT_NO")) from 1 for 7)) || ' - ' || T3."COMMENT") ELSE ('Summe Belege älter 90 Tage') END as c54,
  61. (substring(T1."ACCT_NR" from 1 for 5)) || '_STK' as c55,
  62. 'STK' as c56,
  63. T6."ORDER_NUMBER" as c57,
  64. T6."LINE_NUMBER" as c58,
  65. T6."INV_TIME" as c59,
  66. T6."INV_TIME_INT" as c60,
  67. T6."MAKE_TIME_UNIT" as c61,
  68. 'STK' as c62,
  69. CASE WHEN (T7."Hauptbetrieb_ID" IS NULL) THEN ('1') ELSE (T7."Hauptbetrieb_ID") END as c63,
  70. 'Bergneustadt Ley' as c64,
  71. CASE WHEN ((T7."Standort_ID" IS NULL) and (T2."CLIENT_DB" = '1')) THEN ('01') WHEN ((T7."Standort_ID" IS NULL) and (T2."CLIENT_DB" = '2')) THEN ('02') ELSE (T7."Standort_ID") END as c65,
  72. CASE WHEN ((T7."Standort_Name" IS NULL) and (T2."CLIENT_DB" IN ('1'))) THEN ('Bergneustadt') WHEN ((T7."Standort_Name" IS NULL) and (T2."CLIENT_DB" IN ('2'))) THEN ('Verwaltung') ELSE (T7."Standort_Name") END as c66,
  73. 'Opel' as c67,
  74. 'Opel' as c68,
  75. '1' as c69
  76. from "OPTIMA"."import"."ACCOUNT_INFO" T1,
  77. (((((("OPTIMA"."import"."ACCT_DOC_KEY" T2 left outer join "OPTIMA"."import"."ACCT_DOC_DATA" T3 on (T2."UNIQUE_IDENT" = T3."REFERENCE_IDENT") and (T2."CLIENT_DB" = T3."CLIENT_DB")) left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T4 on (T2."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") and (T2."CLIENT_DB" = T4."CLIENT_DB")) left outer join "OPTIMA"."import"."AA_TRTYPE" T5 on (T2."AA_TRTYPE" = T5."AA_TRTYPE_ID") and (T2."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."ACCT_DOC_SALESCLAS" T8 on (T3."REFERENCE_IDENT" = T8."REFERENCE_IDENT") and (T3."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."import"."ORDER_LINE" T6 on ((T6."ORDER_NUMBER" = T8."ORDER_NUMBER") and (T6."LINE_NUMBER" = T8."ORDER_LINE_NUMBER")) and (T6."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Department" T7 on ((substring(T2."DEPARTMENT" from 1 for 2)) = T7."Standort") and (T2."CLIENT_DB" = T7."Hauptbetrieb"))
  78. where ((T1."ACCT_NR" = T2."ACCT_NO") and (T1."CLIENT_DB" = T2."CLIENT_DB"))
  79. and ((((T1."TYPE_ACCTT" = '2') and (T1."ACCT_NR" LIKE '8%')) and ((T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY") <> 0)) and (T2."BOOKKEEP_DATE" >= TIMESTAMP '2018-01-01 00:00:00.000'))
  80. END SQL
  81. COLUMN,0,Acct Nr_ori
  82. COLUMN,1,Ledger Accts Name
  83. COLUMN,2,Ledger Accts Name2
  84. COLUMN,3,Handler
  85. COLUMN,4,Dept Split
  86. COLUMN,5,Type Acctt_
  87. COLUMN,6,Acct No
  88. COLUMN,7,Bookkeep Date
  89. COLUMN,8,Bookkeep Period
  90. COLUMN,9,Document No
  91. COLUMN,10,Origin
  92. COLUMN,11,Status
  93. COLUMN,12,Debit Amount
  94. COLUMN,13,Credit Amount
  95. COLUMN,14,Debit Quantity
  96. COLUMN,15,Credit Quantity
  97. COLUMN,16,Aa Trtype
  98. COLUMN,17,Department
  99. COLUMN,18,Stock
  100. COLUMN,19,Make Family
  101. COLUMN,20,Make
  102. COLUMN,21,Vehicle Type
  103. COLUMN,22,Model Line
  104. COLUMN,23,Factory Model
  105. COLUMN,24,Workshop Model
  106. COLUMN,25,Product Group
  107. COLUMN,26,Repair Group
  108. COLUMN,27,Kit Group
  109. COLUMN,28,Time Code
  110. COLUMN,29,Int Voucher No
  111. COLUMN,30,Balancing Mark
  112. COLUMN,31,Used Veh Dest Code
  113. COLUMN,32,Use Of Vehicle
  114. COLUMN,33,Acct No Next Chart
  115. COLUMN,34,Reference Ident
  116. COLUMN,35,Transact Date
  117. COLUMN,36,Handler
  118. COLUMN,37,Program
  119. COLUMN,38,Function Code
  120. COLUMN,39,Modul
  121. COLUMN,40,Document Key
  122. COLUMN,41,Comment
  123. COLUMN,42,Department Type Id
  124. COLUMN,43,Description
  125. COLUMN,44,Department Group
  126. COLUMN,45,Aa Trtype Id
  127. COLUMN,46,Description
  128. COLUMN,47,Own Description
  129. COLUMN,48,Rechtseinheit
  130. COLUMN,49,Betrieb
  131. COLUMN,50,Marke_ori_alt
  132. COLUMN,51,Mandant
  133. COLUMN,52,Betrag
  134. COLUMN,53,Text
  135. COLUMN,54,Acct Nr
  136. COLUMN,55,Susa
  137. COLUMN,56,Order Number
  138. COLUMN,57,Line Number
  139. COLUMN,58,Inv Time
  140. COLUMN,59,Inv Time Int
  141. COLUMN,60,Make Time Unit
  142. COLUMN,61,Type Acctt
  143. COLUMN,62,Rechtseinheit_ID
  144. COLUMN,63,Rechtseinheit_Name
  145. COLUMN,64,Betrieb_ID
  146. COLUMN,65,Betrieb_Name
  147. COLUMN,66,Fabrikat
  148. COLUMN,67,Marke
  149. COLUMN,68,Fabrikat_Order_By