qbuilder/sql/import/fm_rem_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"."Reisacher Electric Mobility$Customer Ledger Entry" AS CLE
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$Customer" AS C ON C."No_" = CLE."Customer No_"
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$Department" AS D ON CLE."Department Code" = D."Code"
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$Salesperson_Purchaser" AS SP ON CLE."Salesperson Code" = SP."Code"
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$Reminder_Fin_ Charge Entry" AS RCE ON CLE."Entry No_" = RCE."Customer Entry No_"
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$Vehicle Ledger Entry" AS VLE ON CLE."Document No_" = VLE."Document No_"
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$Vehicle" AS V ON CLE."VIN" = V."VIN"
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$Customer" AS C2 ON V."Customer No_" = C2."No_"
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$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"."Reisacher Electric Mobility$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"."Reisacher Electric Mobility$Service Ledger Entry" SLE ON CLE."Document No_" = SLE."Document No_"
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$Archived Service Header" ASH ON SLE."Order No_" = ASH."No_"
LEFT JOIN "DMS1"."dbo"."Reisacher Electric Mobility$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%'
			)
		)