qbuilder/sql/import/fm_ahr_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,
case when C."No_" = '036201' then 'Alphabet' else C."Name" end 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,

case
  when CLE."Salesperson Code" = '104' then 'cab' 
  when CLE."Salesperson Code" = '106' then 'bv'
  when CLE."Salesperson Code" = '109' then 'fd'
  when CLE."Salesperson Code" = '111' then 'aho'
  when CLE."Salesperson Code" = '133' then 'mk'
  when CLE."Salesperson Code" = '141' then 'ka'
  when CLE."Salesperson Code" = '142' then 'fl'
  when CLE."Salesperson Code" = '143' then 'tb'
  when CLE."Salesperson Code" = '144' then 'gr'
  when CLE."Salesperson Code" = '145' then 'kgu'
  when CLE."Salesperson Code" = '148' then 'hbo'
  when CLE."Salesperson Code" = '149' then 'sbe'
  when CLE."Salesperson Code" = '152' then 'hp'
  when CLE."Salesperson Code" = '153' then 'rhe'
  when CLE."Salesperson Code" = '154' then 'fja'
  when CLE."Salesperson Code" = '302' then 'jja'
  when CLE."Salesperson Code" = '303' then 'twa'
  when CLE."Salesperson Code" = '304' then 'sdo'
  when CLE."Salesperson Code" = '308' then 'tbe'
  when CLE."Salesperson Code" = '309' then 'ba'
  when CLE."Salesperson Code" = '332' then 'kkl'
  when CLE."Salesperson Code" = '341' then 'af'
  when CLE."Salesperson Code" = '342' then 'vpo'
  when CLE."Salesperson Code" = '343' then 'mku'
  when CLE."Salesperson Code" = '344' then 'rwo'
  when CLE."Salesperson Code" = '345' then 'osa'
  when CLE."Salesperson Code" = '351' then 'ipl'
  when CLE."Salesperson Code" = '353' then 'sse'
  when CLE."Salesperson Code" = '354' then 'rze'
  when CLE."Salesperson Code" = '550' then 'aan'
  when CLE."Salesperson Code" = '811' then 'tal'
  when CLE."Salesperson Code" = '820' then 'wr'
  when CLE."Salesperson Code" = '821' then 'mma'
  when CLE."Salesperson Code" = '822' then 'mme'
else '' end 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"  
  WHEN substring(CLE."Document No_" ,8,1) = '1' THEN ('MM') 
  WHEN substring(CLE."Document No_" ,8,1) = '2' THEN ('VÖH') 
  WHEN substring(CLE."Document No_" ,8,1) = '3' THEN ('KRU') 
  WHEN substring(CLE."Document No_" ,8,1) = '4' THEN ('ULM') 
  WHEN substring(CLE."Document No_" ,8,1) = '5' THEN ('LL') 
  ELSE null END as branch_code,

CASE 
  WHEN CLE."Branch Code" = 'MM'  THEN ('10') 
  WHEN CLE."Branch Code" = 'VÖH' THEN ('20') 
  WHEN CLE."Branch Code" = 'KRU' THEN ('30') 
  WHEN CLE."Branch Code" = 'ULM' THEN ('40') 
  WHEN CLE."Branch Code" = 'LL'  THEN ('50')
  WHEN CLE."Branch Code" = 'GZ'  THEN ('55')
  WHEN substring(CLE."Document No_" ,8,1) = '1' THEN ('10') 
  WHEN substring(CLE."Document No_" ,8,1) = '2' THEN ('20') 
  WHEN substring(CLE."Document No_" ,8,1) = '3' THEN ('30') 
  WHEN substring(CLE."Document No_" ,8,1) = '4' THEN ('40') 
  WHEN substring(CLE."Document No_" ,8,1) = '5' THEN ('50')   
  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 Reisacher$Customer Ledger Entry" as CLE 
left join "DMS1"."dbo"."AH Reisacher$Customer" as C on C."No_" = CLE."Customer No_"
left join "DMS1"."dbo"."AH Reisacher$Department" as D on CLE."Department Code" = D."Code"
left join "DMS1"."dbo"."AH Reisacher$Salesperson_Purchaser" as SP on CLE."Salesperson Code" = SP."Code"
left join "DMS1"."dbo"."AH Reisacher$Reminder_Fin_ Charge Entry" as RCE on CLE."Entry No_" = RCE."Customer Entry No_"
left join "DMS1"."dbo"."AH Reisacher$Vehicle Ledger Entry" as VLE on CLE."Document No_" = VLE."Document No_"

left join "DMS1"."dbo"."AH Reisacher$Vehicle" as V on CLE."VIN" = V."VIN"
left join "DMS1"."dbo"."AH Reisacher$Customer" as C2 on V."Customer No_" = C2."No_" 
left join "DMS1"."dbo"."AH Reisacher$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 Reisacher$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 Reisacher$Service Ledger Entry" SLE on CLE."Document No_" = SLE."Document No_"
left join "DMS1"."dbo"."AH Reisacher$Archived Service Header" ASH on SLE."Order No_" = ASH."No_"
left join "DMS1"."dbo"."AH Reisacher$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_" not in ('VRGF201000476', 'VGGF234000024')
  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%')
  
  )