C11/System/NAVISION/IQD/OP/forderungen_nav_rest_rest.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\op\forderungen_nav_rest_rest.imr
TITLE,forderungen_nav_rest_rest.imr
BEGIN SQL
select T1."No_" as c1,
	   T1."Name" as c2,
	   T1."Search Name" as c3,
	   T1."Name 2" as c4,
	   T1."Address" as c5,
	   T1."Address 2" as c6,
	   T1."City" as c7,
	   T1."Contact" as c8,
	   T1."Phone No_" as c9,
	   T1."Telex No_" as c10,
	   T1."Our Account No_" as c11,
	   T2."Entry No_" as c12,
	   T2."Customer No_" as c13,
	   T2."Posting Date" as c14,
	   T2."Document Type" as c15,
	   T2."Document No_" as c16,
	   T2."Description" as c17,
	   T2."Currency Code" as c18,
	   T2."Sell-to Customer No_" as c19,
	   T2."Customer Posting Group" as c20,
	   T2."Department Code" as c21,
	   T2."Make Code" as c22,
	   T2."Salesperson Code" as c23,
	   T2."User ID" as c24,
	   T2."Source Code" as c25,
	   T2."On Hold" as c26,
	   T2."Applies-to Doc_ Type" as c27,
	   T2."Applies-to Doc_ No_" as c28,
	   T2."Open" as c29,
	   T2."Due Date" as c30,
	   T2."Pmt_ Discount Date" as c31,
	   T2."Positive" as c32,
	   T2."Closed by Entry No_" as c33,
	   T2."Closed at Date" as c34,
	   T2."Applies-to ID" as c35,
	   T2."Journal Batch Name" as c36,
	   T2."Reason Code" as c37,
	   T2."Bal_ Account Type" as c38,
	   T2."Bal_ Account No_" as c39,
	   T2."Transaction No_" as c40,
	   T2."Document Date" as c41,
	   T2."External Document No_" as c42,
	   T2."Calculate Interest" as c43,
	   T2."Closing Interest Calculated" as c44,
	   T2."No_ Series" as c45,
	   T2."Closed by Currency Code" as c46,
	   T2."Customer Group Code" as c47,
	   T2."Branch Code" as c48,
	   T2."Main Area" as c49,
	   T2."VIN" as c50,
	   T2."Cash Reg_ Receipt No_" as c51,
	   T2."Comment" as c52,
	   T2."Is Vehicle" as c53,
	   T2."Bulk Customer No_" as c54,
	   T2."Bulkcust_ Business" as c55,
	   T2."Book No_" as c56,
	   T2."No_ of Bulk Customer" as c57,
	   T3."Code" as c58,
	   T3."Name" as c59,
	   T3."Consolidation Code" as c60,
	   'Forderungen' as c61,
	   'Kundenforderungen' as c62,
	   T4."Code" as c63,
	   T4."Name" as c64,
	   ('nicht zuzuordnen 1') as c65,
	   (cast_float(T2."Remaining Amount")) as c66,
	   CASE WHEN (((('nicht zuzuordnen 1')) = 'SC') and (T1."No_" IN ('001490','001491'))) THEN ('GWL-Forderungen') WHEN (((('nicht zuzuordnen 1')) = 'SC') and (not T1."No_" IN ('001490','001491'))) THEN ('KD-Forderungen') ELSE null END as c67,
	   CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END as c68,
	   T2."Due Date" as c69,
	   (now()) as c70,
	   (CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END) as c71,
	   (extract(DAY FROM ((now())) - T2."Due Date")) as c72,
	   CASE WHEN (((extract(DAY FROM ((now())) - T2."Due Date"))) > 42) THEN ('älter 6 Wochen') WHEN (((extract(DAY FROM ((now())) - T2."Due Date"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((extract(DAY FROM ((now())) - T2."Due Date"))) BETWEEN 22 AND 28) THEN ('3 - 4 Wochen') WHEN (((extract(DAY FROM ((now())) - T2."Due Date"))) BETWEEN 15 AND 21) THEN ('2 - 3 Wochen') WHEN (((extract(DAY FROM ((now())) - T2."Due Date"))) BETWEEN 0 AND 14) THEN ('0 - 2 Wochen') ELSE ('noch nicht fällig') END as c73,
	   CASE WHEN ((T2."Salesperson Code" <> '') and (T4."Code" <> '')) THEN (T2."Salesperson Code" || ' - ' || T4."Name") ELSE ('n.N.') END as c74,
	   CASE WHEN (T1."No_" IN ('690101','690102','690103','690104','690105')) THEN (T2."Document No_" || ' - ' || T2."Description" || ' - ' || (asciiz(extract(YEAR FROM T2."Due Date"),4) || '-' || asciiz(extract(MONTH FROM T2."Due Date"),2) || '-' || asciiz(extract(DAY FROM T2."Due Date"),2)) || ' / ' || (CASE WHEN ((T2."Salesperson Code" <> '') and (T4."Code" <> '')) THEN (T2."Salesperson Code" || ' - ' || T4."Name") ELSE ('n.N.') END)) ELSE (T2."Document No_" || ' - ' || T1."Name" || ' - ' || (asciiz(extract(YEAR FROM T2."Due Date"),4) || '-' || asciiz(extract(MONTH FROM T2."Due Date"),2) || '-' || asciiz(extract(DAY FROM T2."Due Date"),2)) || ' / ' || (CASE WHEN ((T2."Salesperson Code" <> '') and (T4."Code" <> '')) THEN (T2."Salesperson Code" || ' - ' || T4."Name") ELSE ('n.N.') END)) END as c75,
	   (od_left(T2."Document No_",3)) as c76,
	   (od_left(T2."Document No_",2)) as c77,
	   CASE WHEN ((((cast_float(T2."Remaining Amount"))) <= 0) and (((od_left(T2."Document No_",3))) IN ('KAB','AUG','BFS','HVB','SPK','RBK','LBW'))) THEN ('nicht zuzuordnen 1') WHEN ((((cast_float(T2."Remaining Amount"))) <= 0) and ((od_left(T2."Document No_",2)) IN ('SP','HV'))) THEN ('nicht zuzuordnen 1') WHEN ((('nicht zuzuordnen 1')) IN ('Ford. bei Rechtsanwalt')) THEN ((('nicht zuzuordnen 1'))) ELSE ('nicht zuzuordnen') END as c78,
	   T2."Client_DB" as c79,
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as c80,
	   (CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END) as c81,
	   CASE WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" = 'AAM') THEN ('60') WHEN (T2."Branch Code" = 'GÖG') THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE null END)) IN ('80')) THEN ('WTB') ELSE null END as c82
from ((("NAVISION"."import"."Customer_Ledger_Entry" T2 left outer join "NAVISION"."import"."Customer" T1 on (T1."No_" = T2."Customer No_") and (T1."Client_DB" = T2."Client_DB")) left outer join "NAVISION"."import"."Department" T3 on (T2."Department Code" = T3."Code") and (T2."Client_DB" = T3."Client_DB")) left outer join "NAVISION"."import"."Salesperson_Purchaser" T4 on (T2."Salesperson Code" = T4."Code") and (T2."Client_DB" = T4."Client_DB"))
where ((((((((((cast_float(T2."Remaining Amount"))) <> 0) and (T2."Journal Batch Name" <> 'ERSTIMPORT')) and (not T1."No_" LIKE '600%')) and ((od_left(T2."Document No_",2)) IN ('AL','BF','E4','HV','KA','LB','LO','LP','LQ','LR','RB','SP','UM'))) or (T2."Document No_" LIKE '166%')) or (T2."Document No_" LIKE '169%')) or (T2."Document No_" LIKE 'UB0%')) or (T2."Document No_" LIKE 'ERGGFZ%'))
order by c16 asc

END SQL
COLUMN,0,No
COLUMN,1,Name
COLUMN,2,Search Name
COLUMN,3,Name 2
COLUMN,4,Address
COLUMN,5,Address 2
COLUMN,6,City
COLUMN,7,Contact
COLUMN,8,Phone No
COLUMN,9,Telex No
COLUMN,10,Our Account No
COLUMN,11,Entry No
COLUMN,12,Customer No
COLUMN,13,Posting Date
COLUMN,14,Document Type
COLUMN,15,Document No
COLUMN,16,Description
COLUMN,17,Currency Code
COLUMN,18,Sell-to Customer No
COLUMN,19,Customer Posting Group
COLUMN,20,Department Code
COLUMN,21,Make Code
COLUMN,22,Salesperson Code
COLUMN,23,User Id
COLUMN,24,Source Code
COLUMN,25,On Hold
COLUMN,26,Applies-to Doc Type
COLUMN,27,Applies-to Doc No
COLUMN,28,Open
COLUMN,29,Due Date
COLUMN,30,Pmt Discount Date
COLUMN,31,Positive
COLUMN,32,Closed By Entry No
COLUMN,33,Closed At Date
COLUMN,34,Applies-to Id
COLUMN,35,Journal Batch Name
COLUMN,36,Reason Code
COLUMN,37,Bal Account Type
COLUMN,38,Bal Account No
COLUMN,39,Transaction No
COLUMN,40,Document Date
COLUMN,41,External Document No
COLUMN,42,Calculate Interest
COLUMN,43,Closing Interest Calculated
COLUMN,44,No Series
COLUMN,45,Closed By Currency Code
COLUMN,46,Customer Group Code
COLUMN,47,Branch Code
COLUMN,48,Main Area
COLUMN,49,Vin
COLUMN,50,Cash Reg Receipt No
COLUMN,51,Comment
COLUMN,52,Is Vehicle
COLUMN,53,Bulk Customer No
COLUMN,54,Bulkcust Business
COLUMN,55,Book No
COLUMN,56,No Of Bulk Customer
COLUMN,57,Code
COLUMN,58,Name
COLUMN,59,Consolidation Code
COLUMN,60,Stufe 1
COLUMN,61,Stufe 2
COLUMN,62,Code
COLUMN,63,Name
COLUMN,64,Stufe 3_ori
COLUMN,65,Remaining_Amount
COLUMN,66,Stufe 5
COLUMN,67,Betrieb Nr
COLUMN,68,Fälligkeitsdatum
COLUMN,69,Heute
COLUMN,70,Stufe 4
COLUMN,71,Tage
COLUMN,72,Staffel
COLUMN,73,Verursacher
COLUMN,74,Beleg
COLUMN,75,links_doc_no_3
COLUMN,76,links_doc_no_2
COLUMN,77,Stufe 3
COLUMN,78,Hauptbetrieb_ID
COLUMN,79,Hauptbetrieb_Name
COLUMN,80,Standort_ID
COLUMN,81,Standort_Name