C11/System/OPTIMA/IQD/Serv_Teile/Auftraege_Gutschrift.sql

select T1."REGISTER_NUMBER" as "Register Number",
	   T1."STATUS" as "Status",
	   T1."STATE_KEY_DATE" as "State Key Date",
	   T1."DEBIT_ACCOUNT" as "Debit Account",
	   T1."INVOICE_NUMBER" as "Invoice Number",
	   T1."WORKSHOP_MODEL" as "Workshop Model",
	   T1."STATE_CODE" as "State Code",
	   T1."TRANSACT_DATE" as "Transact Date",
	   T1."DELIVERY_ACCOUNT" as "Delivery Account",
	   T1."DEPARTMENT" as "Department",
	   T1."DEBET_DEPARTMENT" as "Debet Department",
	   T1."SALESMAN" as "Salesman_2",
	   T1."DEBIT_PERM" as "Debit Perm",
	   T1."ORDER_DATE" as "Order Date",
	   T1."INVOICE_DATE" as "Invoice Date",
	   T1."PMT_TERM" as "Pmt Term",
	   T1."PAYMENT_TEXT" as "Payment Text",
	   T1."COSTS" as "Costs",
	   T1."ORDERS_GROSSVALUE" as "Orders Grossvalue",
	   T1."DISCOUNT_AMOUNT" as "Discount Amount",
	   T1."STOCK" as "Stock",
	   T1."CUSTOMER_GROUP" as "Customer Group_2",
	   T1."BASIS_NUMBER" as "Basis Number",
	   T1."MILEAGE" as "Mileage",
	   T1."PREV_STATUS" as "Prev Status",
	   T1."SALES_CLASS_NUMBER" as "Sales Class Number",
	   T1."TITLE" as "Title",
	   T1."NAME" as "Name",
	   T1."STREET_ADDR" as "Street Addr",
	   T1."ADDR_2" as "Addr 2",
	   T1."ZIPCODE" as "Zipcode",
	   T1."MAIL_ADDR" as "Mail Addr",
	   T1."DISCOUNT_LIMIT" as "Discount Limit",
	   T1."REFERENCE_NUMBER" as "Reference Number",
	   T1."EXPECTED_ORDER_TIM" as "Expected Order Tim",
	   T1."BOL_TAX_SHARE" as "Bol Tax Share",
	   T1."MODEL_TEXT" as "Model Text",
	   T1."WORKSHOP_PRICECODE" as "Workshop Pricecode",
	   T1."SPLIT_COUNTER" as "Split Counter",
	   T1."ARRIVAL_TIME" as "Arrival Time",
	   T1."ARRIVAL_DATE" as "Arrival Date",
	   T1."END_DATE" as "End Date",
	   T1."END_TIME" as "End Time",
	   T1."FAC_MODEL_CODE_S" as "Fac Model Code S",
	   T1."MAKE_CD" as "Make Cd_3",
	   T1."YEAR_MODEL" as "Year Model",
	   T1."TRANSFER_MAKE_CD" as "Transfer Make Cd",
	   T1."CHASSIS_NUMBER" as "Chassis Number",
	   T1."WORKSHOP_TEAM" as "Workshop Team",
	   T1."COMMISSION_SALESMAN" as "Commission Salesman",
	   T1."REF_IDENT_INV_TOTAL" as "Ref Ident Inv Total",
	   T1."REF_IDENT_SALES_CLASS" as "Ref Ident Sales Class",
	   T1."USE_PARTS_PRE_PICKING" as "Use Parts Pre Picking",
	   T1."LDC_ORDER" as "Ldc Order",
	   T1."FHG_REPORT" as "Fhg Report",
	   T1."ACTUAL_INV_DATE_TIME" as "Actual Inv Date Time",
	   T1."CONV_FLAG" as "Conv Flag",
	   T1."UNIQUE_IDENT" as "Unique Ident_2",
	   T2."LINE_NUMBER" as "Line Number",
	   T2."LINE_GROUP_CODE" as "Line Group Code",
	   T2."HANDLER" as "Handler",
	   T2."ORDER_LINETYPE" as "Order Linetype",
	   T2."REDUCTION_CODE" as "Reduction Code",
	   T2."REDUCTION_AMOUNT" as "Reduction Amount",
	   T2."MECHANIC_CODE" as "Mechanic Code",
	   T2."SALESMAN" as "Salesman",
	   T2."DISCOUNT" as "Discount",
	   T2."STDPRICE" as "Stdprice",
	   T2."LINES_NET_VALUE" as "Lines Net Value",
	   T2."PROD_CODE" as "Prod Code",
	   T2."MAKE_CD" as "Make Cd_2",
	   T2."PRODUCT_GROUP" as "Product Group",
	   T2."PROD_NAME" as "Prod Name",
	   T2."ORDER_QUANTITY" as "Order Quantity",
	   T2."DELIVERY_QUANTITY" as "Delivery Quantity",
	   T2."LINE_COSTS" as "Line Costs",
	   T2."REPAIR_CODE" as "Repair Code",
	   T2."REPAIR_GROUP" as "Repair Group_2",
	   T2."REPAIR_NAME" as "Repair Name",
	   T2."USED_TIME" as "Used Time",
	   T2."EST_TIME" as "Est Time",
	   T2."INV_TIME" as "Inv Time",
	   T2."USED_TIME_INT" as "Used Time Int",
	   T2."EST_TIME_INT" as "Est Time Int",
	   T2."INV_TIME_INT" as "Inv Time Int",
	   T2."MAKE_TIME_UNIT" as "Make Time Unit",
	   T2."UNIQUE_IDENT" as "Unique Ident",
	   T3."STAT_CODE" as "Stat Code",
	   T3."STAT_SPECIFY" as "Stat Specify",
	   T4."DEPARTMENT_TYPE_ID" as "Department Type Id",
	   T4."DESCRIPTION" as "Description",
	   T5."SELLER_CODE" as "Seller Code_2",
	   T5."SEL_NAME" as "Sel Name",
	   T5."SEL_DEPARTMENT" as "Sel Department_2",
	   T5."SEL_FIRST_NAME" as "Sel First Name",
	   T5."SEL_FAMILY_NAME" as "Sel Family Name",
	   T6."CUSTOMER_GROUP" as "Customer Group",
	   T6."CUST_GROUP_SPECIFY" as "Cust Group Specify",
	   T7."SELLER_CODE" as "Seller Code",
	   T7."SEL_NAME" as "Sel Name_Monteur",
	   T7."SEL_DEPARTMENT" as "Sel Department",
	   T7."SEL_FIRST_NAME" as "Sel First Name_Monteur",
	   T7."SEL_FAMILY_NAME" as "Sel Family Name_Monteur",
	   T8."REPAIR_GROUP" as "Repair Group",
	   T8."MAKE_CD" as "Make Cd",
	   T8."REPAIR_GRP_SPECIFY" as "Repair Grp Specify",
	   CASE WHEN (T7."SEL_DEPARTMENT" = '0316      ') THEN ((left(T7."SEL_DEPARTMENT",2))) ELSE ((left(T4."DEPARTMENT_TYPE_ID",2))) END as "Standort",
	   CASE WHEN ((T6."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T1."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T6."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T6."CUSTOMER_GROUP" LIKE '9%') or (T1."PMT_TERM" = 'IN')) or ((left(T1."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as "Umsatzart",
	   T5."SEL_NAME" as "Serviceberater",
	   CASE WHEN (T1."STATUS" BETWEEN '30' AND '39') THEN ('Teile') WHEN (T1."STATUS" BETWEEN '40' AND '51') THEN ('Service') WHEN (T1."STATUS" = '70') THEN ('sonst. Auftrag') WHEN (T1."STATUS" = '91') THEN ('Anfrage') ELSE null END as "Auftragsart",
	   CASE WHEN (T1."STATUS" IN ('35','37','47','50')) THEN ('Rechnung') WHEN (T1."STATUS" IN ('36','39','49','51')) THEN ('Gutschrift') ELSE null END as "Rechnung/Gutschrift",
	   (left((((T1."ORDER_NUMBER"))),7)) + ' - ' + T2."HANDLER" + ' - ' + T1."DEBIT_ACCOUNT" + ' - ' + T1."NAME" + ' - ' + (convert(varchar(50), year(T1."INVOICE_DATE")) + '-' + convert(varchar(50), month(T1."INVOICE_DATE")) + '-' + convert(varchar(50), day(T1."INVOICE_DATE"))) as "Kunde_fuer_Gutschriften",
	   T2."LINES_NET_VALUE" as "Umsatz_Gutschriften",
	   '1' as "Hauptbetrieb Id",
	   (CASE WHEN (T7."SEL_DEPARTMENT" = '0316      ') THEN ((left(T7."SEL_DEPARTMENT",2))) ELSE ((left(T4."DEPARTMENT_TYPE_ID",2))) END) as "Standort Id"
from ((((((("deop01"."dbo"."ORDER_HEADER" T1 left outer join "deop01"."dbo"."ORDER_LINE" T2 on T1."ORDER_NUMBER" = T2."ORDER_NUMBER") left outer join "deop01"."dbo"."vPP25" T3 on T1."STATUS" = T3."STAT_CODE") left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T4 on T1."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "deop01"."dbo"."vPP43" T5 on T1."SALESMAN" = T5."SELLER_CODE") left outer join "deop01"."dbo"."vPP48" T6 on T1."CUSTOMER_GROUP" = T6."CUSTOMER_GROUP") left outer join "deop01"."dbo"."vPP43" T7 on T2."MECHANIC_CODE" = T7."SELLER_CODE") left outer join "deop01"."dbo"."vPP73" T8 on (T2."REPAIR_GROUP" = T8."REPAIR_GROUP") and (T2."MAKE_CD" = T8."MAKE_CD"))
where (((T1."STATUS" IN ('35','37','39','47','49','50','51','36')) and ((CASE WHEN (T1."STATUS" IN ('35','37','47','50')) THEN ('Rechnung') WHEN (T1."STATUS" IN ('36','39','49','51')) THEN ('Gutschrift') ELSE null END) LIKE '%Gutschrift%')) and ((day((getdate()) - T1."INVOICE_DATE")) <= 10))