qbuilder/sql/fm_neu_naga.txt

select 
 "DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."VIN" as "FAGNR",
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Make Code" as "FTYPE", 
"DMS1"."dbo"."AH Reisacher$Customer"."Name" as "KNAME",
"DMS1"."dbo"."AH Reisacher$Customer"."No_" as "KNDNR", 
"DMS1"."dbo"."AH Reisacher$Customer"."Phone No_" as "KTELF", 
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Posting Date" as "REDAT",
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Remaining Amount" as "O500_HW2BUCHGBETR", 
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Due Date" as "FLDAT", 
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Remaining Amount" as "O500_HW2BETR_OFFEN",
"DMS1"."dbo"."AH Reisacher$Customer"."No_" as "O500_KTONR", 
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" as "O500_BELEGNR1", 
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Posting Date" as "OPDAT",  
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Amount" as "REBEB", 
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Salesperson Code" as "KDBERA",  
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Salesperson Code" as "KDBNR",
"DMS1"."dbo"."AH Reisacher$Salesperson_Purchaser"."Name" as "KDBNAME",
'Forderungen DMS' as "Stufe1",
'n v' as "Stufe4",
'n v' as "ZA1",
'n v' as "ZA1_BESCH",
'n v' as "ZA1_BETR_ZWI",
'n v' as "ZA1_BETR",
'n v' as "ZA2",
'n v' as "ZA2_BESCH",
'n v' as "ZA2_BETR_ZWI",
'n v' as "ZA2_BETR",
'n v' as "ZA3",
'n v' as "ZA3_BESCH",
'n v' as "ZA3_BETR_ZWI",
'n v' as "ZA3_BETR",
"DMS1"."dbo"."AH Reisacher$Customer"."Address" as "KSTRA",
"DMS1"."dbo"."AH Reisacher$Customer"."Post Code" as "KDPLZ",
"DMS1"."dbo"."AH Reisacher$Customer"."City" as "KDORT",
"DMS1"."dbo"."AH Reisacher$Customer"."Name" as "LKNAME",
"DMS1"."dbo"."AH Reisacher$Customer"."No_" as "LKNDNR",
"DMS1"."dbo"."AH Reisacher$Customer"."Address" as "LKSTRA",
"DMS1"."dbo"."AH Reisacher$Customer"."Post Code" as "LKDPLZ",
"DMS1"."dbo"."AH Reisacher$Customer"."City" as "LKDORT",
"DMS1"."dbo"."AH Reisacher$Customer"."Phone No_" as "LKTELF",
"DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Entry No_" as "Reserve_1", 
(CASE WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '1') THEN ('10') WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '2') THEN ('20') WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '3') THEN ('30') WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '4') THEN ('40') WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '5') THEN ('50') ELSE null END) as "FFIRM",
CASE WHEN ((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Department Code",3,1)) = '1') THEN ('NA') WHEN ((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Department Code",3,1)) = '2') THEN ('GA') WHEN ((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Department Code",3,1)) = '4') THEN ('SC') WHEN ((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Department Code",3,1)) = '5') THEN ('T&Z') ELSE ('NA/GA') END as "Stufe2",
case when "DMS1"."dbo"."AH Reisacher$Customer "."Reminder Terms Code"  ='MAHN0' then 'Mahnst. 1: Zahlungserinnerung'  when  "DMS1"."dbo"."AH Reisacher$Customer "."Reminder Terms Code" ='MAHN1' then 'Mahnst. 2: RA-Androhung'  when  "DMS1"."dbo"."AH Reisacher$Customer "."Reminder Terms Code" ='MAHN2' then 'Mahnst. M: Rechtsanwalt' when  "DMS1"."dbo"."AH Reisacher$Customer "."Reminder Terms Code" ='MAHN4' then '-' else NULL end as "Next_MAHNSTUFE", case when  "DMS1"."dbo"."AH Reisacher$Customer "."Reminder Terms Code" ='MAHN0' then 'Mahnst. 0: Kontoauszug vor Mahnung' when  "DMS1"."dbo"."AH Reisacher$Customer "."Reminder Terms Code" ='MAHN1' then 'Mahnst. 1: Zahlungserinnerung' when  "DMS1"."dbo"."AH Reisacher$Customer "."Reminder Terms Code" ='MAHN2' then 'Mahnst. 2: RA-Androhung' when  "DMS1"."dbo"."AH Reisacher$Customer "."Reminder Terms Code" ='MAHN4' then 'Mahnst. M: Rechtsanwalt' else NULL end as "O500_MAHNSTUFE",
(CASE WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '1') THEN ('10') WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '2') THEN ('20') WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '3') THEN ('30') WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '4') THEN ('40') WHEN (((substring("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" ,8,1))) = '5') THEN ('50') ELSE null END)  as "Stufe3",
("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Salesperson Code"+' - ' + "DMS1"."dbo"."AH Reisacher$Salesperson_Purchaser"."Name") as "Stufe5"

from ((("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry" left outer join "DMS1"."dbo"."AH Reisacher$Customer" on "DMS1"."dbo"."AH Reisacher$Customer"."No_" = "DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Customer No_") left outer join "DMS1"."dbo"."AH Reisacher$Department" on "DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Department Code" = "DMS1"."dbo". "AH Reisacher$Department"."Code") left outer join "DMS1"."dbo"."AH Reisacher$Salesperson_Purchaser" on "DMS1"."dbo". "AH Reisacher$Customer Ledger Entry"."Salesperson Code" = "DMS1"."dbo"."AH Reisacher$Salesperson_Purchaser"."Code") where(((((("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Remaining Amount")) <> 0) and ("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Journal Batch Name" <> 'ERSTIMPORT')) and (not "DMS1"."dbo". "AH Reisacher$Customer"."No_"LIKE '600%')) and (("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_"LIKE 'VRGGFZ%') or ("DMS1"."dbo"."AH Reisacher$Customer Ledger Entry"."Document No_" LIKE 'VGUGFZ%')))