qbuilder/sql/import/fm_aha_import.sql

select distinct
CLE."VIN" as chassis_number,
CLE."Make Code" as make_code,
CLE."Posting Date" as posting_date,
CLE."Due Date" as due_date,
C."No_" as account_number,
CLE."Document No_" as document_number,
CLE."Entry No_" as entry_number,

CLE."Amount" as transact_sum,
CLE."Remaining Amount" as transact_outstanding,

C."No_" as customer_number,
C."Name" as customer_name,
C."Phone No_" as customer_phone,
C."Address" as customer_address,
C."Post Code" as customer_zipcode,
C."City" as customer_city,
CLE."Salesperson Code" as salesman_number,
SP."Name" as salesman_name,

'' as salesman_user,

case
  when E."Name" <> '' then ASH."Service Advisor No_" + ' - ' + E."Name"
  else CLE."Salesperson Code" + ' - ' + SP."Name"
end as verkaeufer,
RCE."Reminder Level" as reminder_level,

case 
  when RCE."Reminder Level" = '0' then 'Mahnst. 0: Kontoauszug vor Mahnung' 
  when RCE."Reminder Level" = '1' then 'Mahnst. 1: Zahlungserinnerung' 
  when RCE."Reminder Level" = '2' then 'Mahnst. 2: RA-Androhung' 
  when RCE."Reminder Level" = '3' then 'Mahnst. M: Rechtsanwalt' 
  else NULL end as mahnstufe,
  
case 
  when RCE."Reminder Level" = '0' then 'Mahnst. 1: Zahlungserinnerung'  
  when RCE."Reminder Level" = '1' then 'Mahnst. 2: RA-Androhung'  
  when RCE."Reminder Level" = '2' then 'Mahnst. M: Rechtsanwalt' 
  when RCE."Reminder Level" = '3' then '-' 
  else NULL end as mahnstufe2,

CASE 
  WHEN CLE."Branch Code" <> '' THEN CLE."Branch Code"  
  ELSE 'n.v.' END as branch_code,

CASE 
  WHEN CLE."Branch Code" = 'GÖG'  THEN ('60') 
  WHEN CLE."Branch Code" = 'LEH' THEN ('70') 
  WHEN CLE."Branch Code" = 'WTB' THEN ('80') 
  ELSE 'n.v.' END as filiale_nummer,

case
  when (not CLE."Main Area" IN (1,2,3)
		and not left(CLE."Document No_",2) IN ('AL','BF','E4','HV','KA','LB','LO','LP','LQ','LR','RB','SP','UM')
		and not CLE."Document No_" LIKE '166%'
		and not CLE."Document No_" LIKE '169%'
		and not CLE."Document No_" LIKE 'UB0%') then (		
    CASE
	  WHEN C."No_" IN ('690101','690102','690103','690104','690105') THEN ('RA') 
      WHEN left(CLE."Document No_",3) IN ('D12','D13','D14','D15','D16','D23','D24','D25','D32','D34','D35','D43','D44','D45','D51','D52','D54','D55') THEN ('SC') 
      WHEN left(CLE."Document No_",3) IN ('D17','D18','D26','D27','D36','D46','D47','D56','D57') THEN ('TZ') 
      WHEN right(CLE."Document No_",2) = 'NA' THEN ('NA') 
      WHEN right(CLE."Document No_",2) = 'GA' THEN ('GA') 
      ELSE ('n.v.') END )
  when ((CLE."Document No_" LIKE 'VRGGFZ%' 
			or CLE."Document No_" LIKE 'VGUGFZ%' 
			or CLE."Document No_" LIKE 'VRGF%' 
			or CLE."Document No_" LIKE 'VRF%' 
			or CLE."Document No_" LIKE 'VGGF%')
		and VLE."Entry Type" = 1) then (
    CASE
	  WHEN substring(VLE."Department Code",3,1) = '1' THEN ('NA') 
      WHEN substring(VLE."Department Code",3,1) = '2' THEN ('GA') 
      WHEN substring(CLE."Department Code",3,1) = '4' THEN ('SC') 
      WHEN substring(CLE."Department Code",3,1) = '5' THEN ('TZ') 
      ELSE ('NA/GA') END )
  else
    CASE
      WHEN (CLE."Main Area" = 3) THEN ('SC') 
      WHEN (CLE."Main Area" = 1) THEN ('TZ')  
      WHEN (CLE."Main Area" = 2) THEN ('NA/GA')
      ELSE 'SC/TZ' END    
  end as kostenstelle_text,
  CASE WHEN V."Date of Sales Shipment" > '2000-01-01' THEN V."Date of Sales Shipment" ELSE NULL END as shipment_date,
  CASE WHEN C."No_" <> C2."No_" THEN C2."No_" ELSE '' END as customer_number_leasing,
  CASE WHEN C."No_" <> C2."No_" THEN C2."Name" ELSE '' END as customer_name_leasing,
  PSDT1."Text" as text1,
  PSDT2."Text" as text2

from "DMS1"."dbo"."AH Augsburg$Customer Ledger Entry" as CLE 
left join "DMS1"."dbo"."AH Augsburg$Customer" as C on C."No_" = CLE."Customer No_"
left join "DMS1"."dbo"."AH Augsburg$Department" as D on CLE."Department Code" = D."Code"
left join "DMS1"."dbo"."AH Augsburg$Salesperson_Purchaser" as SP on CLE."Salesperson Code" = SP."Code"
left join "DMS1"."dbo"."AH Augsburg$Reminder_Fin_ Charge Entry" as RCE on CLE."Entry No_" = RCE."Customer Entry No_"
left join "DMS1"."dbo"."AH Augsburg$Vehicle Ledger Entry" as VLE on CLE."Document No_" = VLE."Document No_"

left join "DMS1"."dbo"."AH Augsburg$Vehicle" as V on CLE."VIN" = V."VIN"
left join "DMS1"."dbo"."AH Augsburg$Customer" as C2 on V."Customer No_" = C2."No_" 
left join "DMS1"."dbo"."AH Augsburg$Posted Sales Document Text" as PSDT1 on CLE."Document No_" = PSDT1."Document No_" and PSDT1."Line No_" = 10000 and PSDT1."Header_Footer" = 1
left join "DMS1"."dbo"."AH Augsburg$Posted Sales Document Text" as PSDT2 on CLE."Document No_" = PSDT2."Document No_" and PSDT2."Line No_" = 20000 and PSDT2."Header_Footer" = 1

left join "DMS1"."dbo"."AH Augsburg$Service Ledger Entry" SLE on CLE."Document No_" = SLE."Document No_"
left join "DMS1"."dbo"."AH Augsburg$Archived Service Header" ASH on SLE."Order No_" = ASH."No_"
left join "DMS1"."dbo"."AH Augsburg$Employee" E on ASH."Service Advisor No_" = E."No_"

where CLE."Remaining Amount" <> 0 
  and CLE."Journal Batch Name" <> 'ERSTIMPORT' 
  and not C."No_" LIKE '600%' 
  
  and (
  
  ((CLE."Document No_" LIKE 'VRGGFZ%' 
    or CLE."Document No_" LIKE 'VGUGFZ%' 
 or CLE."Document No_" LIKE 'VRGF%' 
 or CLE."Document No_" LIKE 'VRF%' 
 or CLE."Document No_" LIKE 'VGGF%')
  and VLE."Entry Type" = 1)
  
  or 

  (not CLE."Main Area" IN (1,2,3)
  and not left(CLE."Document No_",2) IN ('AL','BF','E4','HV','KA','LB','LO','LP','LQ','LR','RB','SP','UM')
  and not CLE."Document No_" LIKE '166%'
  and not CLE."Document No_" LIKE '169%'
  and not CLE."Document No_" LIKE 'UB0%')
  
  or
  
  (CLE."Document No_" LIKE 'WRG%' 
    or CLE."Document No_" LIKE 'WGG%' 
 or CLE."Document No_" LIKE 'WGGS%' 
 or CLE."Document No_" LIKE 'VRGGET%' 
 or CLE."Document No_" LIKE 'VRT%' 
 or CLE."Document No_" LIKE 'VRGT%' 
 or CLE."Document No_" LIKE 'VGUGET%')
  
  )