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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\op\forderungen_nav_sc.imr
TITLE,forderungen_nav_sc.imr
BEGIN SQL
select distinct 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,
	   CASE WHEN (T2."Main Area" = 3) THEN ('SC') ELSE null END as c65,
	   (cast_float(T2."Remaining Amount")) as c66,
	   CASE WHEN (((CASE WHEN (T2."Main Area" = 3) THEN ('SC') ELSE null END) = 'SC') and (T1."No_" IN ('001490','001491','GARANTIE'))) THEN ('GWL-Forderungen') WHEN (((CASE WHEN (T2."Main Area" = 3) THEN ('SC') ELSE null END) = 'SC') and (not T1."No_" IN ('001490','001491','GARANTIE'))) THEN ('KD-Forderungen') ELSE null END as c67,
	   CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END as c68,
	   T2."Due Date" as c69,
	   (substring(T2."Document No_" from 6 for 1)) as c70,
	   (now()) as c71,
	   (CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END) as c72,
	   (extract(DAY FROM ((now())) - T2."Due Date")) as c73,
	   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 c74,
	   CASE WHEN ((T5."Service Advisor No_" IS NOT NULL) or (T5."Service Advisor No_" <> '')) THEN (T5."Service Advisor No_" || ' - ' || T6."Name") ELSE ('n.N.') END as c75,
	   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 ((CASE WHEN ((T5."Service Advisor No_" IS NOT NULL) or (T5."Service Advisor No_" <> '')) THEN (T5."Service Advisor No_" || ' - ' || T6."Name") ELSE ('n.N.') END) IS NULL) THEN ('n.N.') ELSE ((CASE WHEN ((T5."Service Advisor No_" IS NOT NULL) or (T5."Service Advisor No_" <> '')) THEN (T5."Service Advisor No_" || ' - ' || T6."Name") ELSE ('n.N.') END)) 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 ((CASE WHEN ((T5."Service Advisor No_" IS NOT NULL) or (T5."Service Advisor No_" <> '')) THEN (T5."Service Advisor No_" || ' - ' || T6."Name") ELSE ('n.N.') END) IS NULL) THEN ('n.N.') ELSE ((CASE WHEN ((T5."Service Advisor No_" IS NOT NULL) or (T5."Service Advisor No_" <> '')) THEN (T5."Service Advisor No_" || ' - ' || T6."Name") ELSE ('n.N.') END)) END)) END as c76,
	   CASE WHEN ((CASE WHEN ((T5."Service Advisor No_" IS NOT NULL) or (T5."Service Advisor No_" <> '')) THEN (T5."Service Advisor No_" || ' - ' || T6."Name") ELSE ('n.N.') END) IS NULL) THEN ('n.N.') ELSE ((CASE WHEN ((T5."Service Advisor No_" IS NOT NULL) or (T5."Service Advisor No_" <> '')) THEN (T5."Service Advisor No_" || ' - ' || T6."Name") ELSE ('n.N.') END)) END as c77,
	   T7."Rechtsanwalt" as c78,
	   CASE WHEN (T7."Rechtsanwalt" = 'J') THEN ('Unfallschaden bei RA') ELSE ((CASE WHEN (((CASE WHEN (T2."Main Area" = 3) THEN ('SC') ELSE null END) = 'SC') and (T1."No_" IN ('001490','001491','GARANTIE'))) THEN ('GWL-Forderungen') WHEN (((CASE WHEN (T2."Main Area" = 3) THEN ('SC') ELSE null END) = 'SC') and (not T1."No_" IN ('001490','001491','GARANTIE'))) THEN ('KD-Forderungen') ELSE null END)) END as c79,
	   T2."Client_DB" as c80,
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as c81,
	   (CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END) as c82,
	   CASE WHEN (((CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN (((substring(T2."Document No_" from 6 for 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_" from 6 for 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_" from 6 for 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_" from 6 for 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_" from 6 for 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_" from 6 for 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_" from 6 for 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_" from 6 for 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_" from 6 for 1))) = '8') THEN ('80') ELSE null END)) IN ('80')) THEN ('WTB') ELSE null END as c83
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")) left outer join "NAVISION"."import"."Service_Ledger_Entry" T8 on (T2."Document No_" = T8."Document No_") and (T2."Client_DB" = T8."Client_DB")) left outer join "NAVISION"."import"."Archived_Service_Header" T5 on (T8."Order No_" = T5."No_") and (T8."Client_DB" = T5."Client_DB")) left outer join "NAVISION"."import"."Employee" T6 on (T5."Service Advisor No_" = T6."No_") and (T5."Client_DB" = T6."Client_DB")) left outer join QSS."C:\GlobalCube\System\NAVISION\IQD\OP\Forderungsmanagement_RA_KZ.ims" T7 on (T2."Customer No_" = T7."KNDNR") and (T2."Document No_" = T7."O500_BELEGNR1"))
where ((((((cast_float(T2."Remaining Amount"))) <> 0) and (T2."Journal Batch Name" <> 'ERSTIMPORT')) and (not T1."No_" LIKE '600%')) and ((((T2."Document No_" LIKE 'WRGG%') or (T2."Document No_" LIKE 'WGGS%')) or (T2."Document No_" LIKE 'WGG%')) or (T2."Document No_" LIKE 'WRG%')))
order by c14 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
COLUMN,65,Remaining_Amount
COLUMN,66,Stufe 5_vor_RA
COLUMN,67,Betrieb Nr
COLUMN,68,Fälligkeitsdatum
COLUMN,69,Doc_Nr_Stelle_2
COLUMN,70,Heute
COLUMN,71,Stufe 4
COLUMN,72,Tage
COLUMN,73,Staffel
COLUMN,74,Verursacher_ori
COLUMN,75,Beleg
COLUMN,76,Verursacher
COLUMN,77,Rechtsanwalt
COLUMN,78,Stufe 5
COLUMN,79,Hauptbetrieb_ID
COLUMN,80,Hauptbetrieb_Name
COLUMN,81,Standort_ID
COLUMN,82,Standort_Name