GuV_8_O21_csv_Umsatz_Belege.sql 4.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778
  1. select T1."ACCT_NR" as "Acct Nr_Umsatz",
  2. T1."LEDGER_ACCTS_NAME" as "Ledger Accts Name",
  3. T1."LEDGER_ACCTS_NAME2" as "Ledger Accts Name2",
  4. T1."HANDLER" as "Handler_2",
  5. T1."DEPT_SPLIT" as "Dept Split",
  6. T1."TYPE_ACCTT" as "Type Acctt",
  7. T2."ACCT_NO" as "Acct No",
  8. T2."BOOKKEEP_DATE" as "Bookkeep Date",
  9. T2."BOOKKEEP_PERIOD" as "Bookkeep Period",
  10. T2."DOCUMENT_NO" as "Document No",
  11. T2."ORIGIN" as "Origin",
  12. T2."STATUS" as "Status",
  13. T2."DEBIT_AMOUNT" as "Debit Amount",
  14. T2."CREDIT_AMOUNT" as "Credit Amount",
  15. T2."DEBIT_QUANTITY" as "Debit Quantity",
  16. T2."CREDIT_QUANTITY" as "Credit Quantity",
  17. T2."AA_TRTYPE" as "Aa Trtype",
  18. T2."DEPARTMENT" as "Department",
  19. T2."STOCK" as "Stock",
  20. T2."MAKE_FAMILY" as "Make Family",
  21. T2."MAKE" as "Make",
  22. T2."VEHICLE_TYPE" as "Vehicle Type",
  23. T2."MODEL_LINE" as "Model Line",
  24. T2."FACTORY_MODEL" as "Factory Model",
  25. T2."WORKSHOP_MODEL" as "Workshop Model",
  26. T2."PRODUCT_GROUP" as "Product Group",
  27. T2."REPAIR_GROUP" as "Repair Group",
  28. T2."KIT_GROUP" as "Kit Group",
  29. T2."TIME_CODE" as "Time Code",
  30. T2."INT_VOUCHER_NO" as "Int Voucher No",
  31. T2."BALANCING_MARK" as "Balancing Mark",
  32. T2."USED_VEH_DEST_CODE" as "Used Veh Dest Code",
  33. T2."USE_OF_VEHICLE" as "Use Of Vehicle",
  34. T2."ACCT_NO_NEXT_CHART" as "Acct No Next Chart",
  35. T3."REFERENCE_IDENT" as "Reference Ident",
  36. T3."TRANSACT_DATE" as "Transact Date",
  37. T3."HANDLER" as "Handler",
  38. T3."PROGRAM" as "Program",
  39. T3."FUNCTION_CODE" as "Function Code",
  40. T3."MODUL" as "Modul",
  41. T3."DOCUMENT_KEY" as "Document Key",
  42. T3."COMMENT" as "Comment",
  43. T4."DEPARTMENT_TYPE_ID" as "Department Type Id",
  44. T4."DESCRIPTION" as "Description_2",
  45. T4."DEPARTMENT_GROUP" as "Department Group",
  46. T5."AA_TRTYPE_ID" as "Aa Trtype Id",
  47. T5."DESCRIPTION" as "Description",
  48. T5."OWN_DESCRIPTION" as "Own Description",
  49. T1."CLIENT_DB" as "Rechtseinheit",
  50. CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = ' ')) THEN ('01') ELSE ((substring(T4."DEPARTMENT_TYPE_ID", 1, 2))) END as "Betrieb",
  51. CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = '')) THEN ('1') ELSE ((substring(T4."DEPARTMENT_TYPE_ID", 3, 1))) END as "Marke_ori_alt",
  52. T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT" as "Betrag_",
  53. (db_name()) as "Mandant",
  54. T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY" as "Menge",
  55. CASE WHEN ((-1 * datediff(day, (getdate()), T2."BOOKKEEP_DATE")) <= 90) THEN ((substring((convert(varchar(50), T2."DOCUMENT_NO")), 1, 7)) + ' - ' + T3."COMMENT") ELSE ('Summe Belege älter 90 Tage') END as "Text",
  56. (substring(T1."ACCT_NR", 1, 1)) as "Susa",
  57. T6."ORDER_NUMBER" as "Order Number",
  58. T6."LINE_NUMBER" as "Line Number",
  59. T6."INV_TIME" as "Inv Time",
  60. T6."INV_TIME_INT" as "Inv Time Int",
  61. T6."MAKE_TIME_UNIT" as "Make Time Unit",
  62. T7."Hauptbetrieb_ID" as "Rechtseinheit_ID_ori",
  63. T7."Hauptbetrieb_Name" as "Rechtseinheit_Name_ori",
  64. T7."Standort_ID" as "Betrieb_ID_ori",
  65. T7."Standort_Name" as "Betrieb_Name_ori",
  66. 'Opel' as "Marke_ori",
  67. '1' as "Fabrikat_Order_By_ori",
  68. CASE WHEN (T7."Hauptbetrieb_ID" IS NULL) THEN ('1') ELSE (T7."Hauptbetrieb_ID") END as "Rechtseinheit_ID",
  69. CASE WHEN (T7."Hauptbetrieb_Name" IS NULL) THEN ('Auto Jacob') ELSE (T7."Hauptbetrieb_Name") END as "Rechtseinheit_Name",
  70. CASE WHEN (T7."Standort_ID" IS NULL) THEN ('01') ELSE (T7."Standort_ID") END as "Betrieb_ID",
  71. CASE WHEN (T7."Standort_Name" IS NULL) THEN ('Rüsselsheim') ELSE (T7."Standort_Name") END as "Betrieb_Name",
  72. CASE WHEN ('Opel' IS NULL) THEN ('Opel') ELSE ('Opel') END as "Marke",
  73. '1' as "Fabrikat_Order_By",
  74. (T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT") * -1 as "Umsatz FIBU gesamt"
  75. from "OPTIMA"."import"."ACCOUNT_INFO" T1,
  76. (((((("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", 1, 2)) = T7."Standort") and (T2."CLIENT_DB" = T7."Hauptbetrieb"))
  77. where ((T1."ACCT_NR" = T2."ACCT_NO") and (T1."CLIENT_DB" = T2."CLIENT_DB"))
  78. and (((T1."TYPE_ACCTT" = '2') and (T2."BOOKKEEP_DATE" >= convert(datetime, '2018-01-01 00:00:00.000'))) and ((substring(T1."ACCT_NR", 1, 1)) = '8'))