| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546 |
- select
- chassis_number as FAGNR,
- make_code as FTYPE,
- posting_date as REDAT,
- posting_date as OPDAT,
- due_date as FLDAT,
- account_number as O500_KTONR,
- document_number as O500_BELEGNR1,
- entry_number as Reserve_1,
- transact_sum as REBEB,
- transact_outstanding as O500_HW2BETR_OFFEN,
- transact_sum as O500_HW2BUCHGBETR,
- customer_number as KNDNR,
- concat(customer_number,' - ', replace(replace(customer_name, " ", " "), "'", "´")) as KNAME,
- customer_phone as KTELF,
- customer_address as KSTRA,
- customer_zipcode as KDPLZ,
- customer_city as KDORT,
- salesman_user as KDBNR,
- salesman_number as KDBERA,
- salesman_name as KDBNAME,
- verkaeufer as Stufe5,
- reminder_level as Reserve_2,
- case when transact_sum > 0 then 'R' else 'G' end as Reserve_3,
- mahnstufe as O500_MAHNSTUFE,
- mahnstufe2 as Next_MAHNSTUFE,
- branch_code as Stufe3,
- filiale_nummer as FFIRM,
- 'Forderungen DMS' as Stufe1,
- kostenstelle_text as Stufe2,
- 'n.v.' as Stufe4,
- branch_code as Betrieb,
- shipment_date,
- customer_number_leasing,
- customer_name_leasing,
- text1,
- text2
-
- from fm_aha_import as t1
- INNER JOIN ( SELECT document_number, chassis_number, due_date, entry_number, MAX(reminder_level) as reminder_level
- FROM fm_aha_import
- GROUP BY 1, 2, 3, 4 ) AS t2
- USING(document_number, chassis_number, due_date, entry_number, reminder_level)
|
