C11/System/OPTIMA/IQD/Belege/GuV_8_O21_STK_csv.sql

select T1."ACCT_NR" as "Acct Nr_ori",
	   T1."LEDGER_ACCTS_NAME" as "Ledger Accts Name",
	   T1."LEDGER_ACCTS_NAME2" as "Ledger Accts Name2",
	   T1."HANDLER" as "Handler_2",
	   T1."DEPT_SPLIT" as "Dept Split",
	   T1."TYPE_ACCTT" as "Type Acctt_",
	   T2."ACCT_NO" as "Acct No",
	   T2."BOOKKEEP_DATE" as "Bookkeep Date",
	   T2."BOOKKEEP_PERIOD" as "Bookkeep Period",
	   T2."DOCUMENT_NO" as "Document No",
	   T2."ORIGIN" as "Origin",
	   T2."STATUS" as "Status",
	   T2."DEBIT_AMOUNT" as "Debit Amount",
	   T2."CREDIT_AMOUNT" as "Credit Amount",
	   T2."DEBIT_QUANTITY" as "Debit Quantity",
	   T2."CREDIT_QUANTITY" as "Credit Quantity",
	   T2."AA_TRTYPE" as "Aa Trtype",
	   T2."DEPARTMENT" as "Department",
	   T2."STOCK" as "Stock",
	   T2."MAKE_FAMILY" as "Make Family",
	   T2."MAKE" as "Make",
	   T2."VEHICLE_TYPE" as "Vehicle Type",
	   T2."MODEL_LINE" as "Model Line",
	   T2."FACTORY_MODEL" as "Factory Model",
	   T2."WORKSHOP_MODEL" as "Workshop Model",
	   T2."PRODUCT_GROUP" as "Product Group",
	   T2."REPAIR_GROUP" as "Repair Group",
	   T2."KIT_GROUP" as "Kit Group",
	   T2."TIME_CODE" as "Time Code",
	   T2."INT_VOUCHER_NO" as "Int Voucher No",
	   T2."BALANCING_MARK" as "Balancing Mark",
	   T2."USED_VEH_DEST_CODE" as "Used Veh Dest Code",
	   T2."USE_OF_VEHICLE" as "Use Of Vehicle",
	   T2."ACCT_NO_NEXT_CHART" as "Acct No Next Chart",
	   T3."REFERENCE_IDENT" as "Reference Ident",
	   T3."TRANSACT_DATE" as "Transact Date",
	   T3."HANDLER" as "Handler",
	   T3."PROGRAM" as "Program",
	   T3."FUNCTION_CODE" as "Function Code",
	   T3."MODUL" as "Modul",
	   T3."DOCUMENT_KEY" as "Document Key",
	   T3."COMMENT" as "Comment",
	   T4."DEPARTMENT_TYPE_ID" as "Department Type Id",
	   T4."DESCRIPTION" as "Description_2",
	   T4."DEPARTMENT_GROUP" as "Department Group",
	   T5."AA_TRTYPE_ID" as "Aa Trtype Id",
	   T5."DESCRIPTION" as "Description",
	   T5."OWN_DESCRIPTION" as "Own Description",
	   T1."CLIENT_DB" as "Rechtseinheit",
	   CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = ' ')) THEN ('01') ELSE ((left(T4."DEPARTMENT_TYPE_ID",2))) END as "Betrieb",
	   CASE WHEN ((T1."ACCT_NR" LIKE '9%') and (T2."DEPARTMENT" = '')) THEN ('1') ELSE ((substring(T4."DEPARTMENT_TYPE_ID", 3, 1))) END as "Marke_ori_alt",
	   (db_name()) as "Mandant",
	   CASE WHEN (((T2."AA_TRTYPE" = 5104) and (T6."LINE_GROUP_CODE" = '150')) and (((left(T3."COMMENT",4))) <> 'A  7')) THEN (T6."EST_TIME_INT") WHEN ((T2."AA_TRTYPE" = 5104) and (((left(T3."COMMENT",4))) <> 'A  7')) THEN (T6."INV_TIME_INT") WHEN (T1."ACCT_NR" IN ('81700','81701','81720','83107','84700')) THEN (0) ELSE (T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY") END as "Betrag",
	   CASE WHEN ((day((getdate()) - T2."BOOKKEEP_DATE")) <= 90) THEN ((left((((T2."DOCUMENT_NO"))),7)) + ' - ' + T3."COMMENT") ELSE ('Summe Belege älter 90 Tage') END as "Text",
	   (left(T1."ACCT_NR",5)) + '_STK' as "Acct Nr",
	   'STK' as "Susa",
	   T6."ORDER_NUMBER" as "Order Number",
	   T6."LINE_NUMBER" as "Line Number",
	   T6."INV_TIME" as "Inv Time",
	   T6."INV_TIME_INT" as "Inv Time Int_2",
	   T6."MAKE_TIME_UNIT" as "Make Time Unit",
	   'STK' as "Type Acctt",
	   T7."Hauptbetrieb_ID" as "Rechtseinheit_ID_ori",
	   T7."Hauptbetrieb_Name" as "Rechtseinheit_Name_ori",
	   T7."Standort_ID" as "Betrieb_ID_ori",
	   T7."Standort_Name" as "Betrieb_Name_ori",
	   T8."Fabrikat" as "Marke_ori",
	   T8."Order_By" as "Fabrikat_Order_By_ori",
	   T7."Hauptbetrieb_ID" as "Rechtseinheit_ID",
	   T7."Hauptbetrieb_Name" as "Rechtseinheit_Name",
	   T7."Standort_ID" as "Betrieb_ID",
	   T7."Standort_Name" as "Betrieb_Name",
	   T8."Fabrikat" as "Marke",
	   T8."Order_By" as "Fabrikat_Order_By",
	   T6."EST_TIME_INT" as "Est Time Int",
	   T6."INV_TIME_INT" as "Inv Time Int",
	   T6."LINE_GROUP_CODE" as "Line Group Code",
	   (left(T3."COMMENT",4)) as "Rabattcode"
from "OPTIMA"."import"."ACCOUNT_INFO" T1,
	(((("OPTIMA"."import"."ACCT_DOC_KEY" T2 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"."data"."GC_Department" T7 on ((substring(T2."DEPARTMENT", 1, 2)) = T7."Standort") and (T2."CLIENT_DB" = T7."Hauptbetrieb_ID")) left outer join "OPTIMA"."data"."GC_Marken" T8 on (T2."CLIENT_DB" = T8."Client_DB") and ((substring(T2."DEPARTMENT", 3, 1)) = T8."Stelle_3_Department")),
	(("OPTIMA"."import"."ACCT_DOC_DATA" T3 left outer join "OPTIMA"."import"."ACCT_DOC_SALESCLAS" T9 on (T3."REFERENCE_IDENT" = T9."REFERENCE_IDENT") and (T3."CLIENT_DB" = T9."CLIENT_DB")) left outer join "OPTIMA"."import"."ORDER_LINE" T6 on ((T6."ORDER_NUMBER" = T9."ORDER_NUMBER") and (T6."LINE_NUMBER" = T9."ORDER_LINE_NUMBER")) and (T6."CLIENT_DB" = T9."CLIENT_DB"))
where ((T1."ACCT_NR" = T2."ACCT_NO") and (T1."CLIENT_DB" = T2."CLIENT_DB")) and ((T2."UNIQUE_IDENT" = T3."REFERENCE_IDENT") and (T2."CLIENT_DB" = T3."CLIENT_DB"))
and ((((T1."TYPE_ACCTT" = '2') and (T1."ACCT_NR" LIKE '8%')) and ((CASE WHEN (((T2."AA_TRTYPE" = 5104) and (T6."LINE_GROUP_CODE" = '150')) and (((left(T3."COMMENT",4))) <> 'A  7')) THEN (T6."EST_TIME_INT") WHEN ((T2."AA_TRTYPE" = 5104) and (((left(T3."COMMENT",4))) <> 'A  7')) THEN (T6."INV_TIME_INT") WHEN (T1."ACCT_NR" IN ('81700','81701','81720','83107','84700')) THEN (0) ELSE (T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY") END) <> 0)) and (T2."BOOKKEEP_DATE" >= convert(datetime, '2018-01-01 00:00:00.000')))