Accueil Explorer Aide
S'inscrire Connexion
GlobalCube
/
C11
3
0
Fork 0
Fichiers Tickets 0 Pull Requests 0 Wiki
Aborescence: 12cb950568
Branches Tags
C11
/
System
/
NAVISION
/
IQD
/
OP
/
forderungen_nav_sc.sql

forderungen_nav_sc.sql 14 KB
Historique Raw

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586 
  1. select distinct T1."No_" as "No",
    2. 

  2. 	   T1."Name" as "Name_3",
    3. 

  3. 	   T1."Search Name" as "Search Name",
    4. 

  4. 	   T1."Name 2" as "Name 2",
    5. 

  5. 	   T1."Address" as "Address",
    6. 

  6. 	   T1."Address 2" as "Address 2",
    7. 

  7. 	   T1."City" as "City",
    8. 

  8. 	   T1."Contact" as "Contact",
    9. 

  9. 	   T1."Phone No_" as "Phone No",
    10. 

  10. 	   T1."Telex No_" as "Telex No",
    11. 

  11. 	   T1."Our Account No_" as "Our Account No",
    12. 

  12. 	   T2."Entry No_" as "Entry No",
    13. 

  13. 	   T2."Customer No_" as "Customer No",
    14. 

  14. 	   T2."Posting Date" as "Posting Date",
    15. 

  15. 	   T2."Document Type" as "Document Type",
    16. 

  16. 	   T2."Document No_" as "Document No",
    17. 

  17. 	   T2."Description" as "Description",
    18. 

  18. 	   T2."Currency Code" as "Currency Code",
    19. 

  19. 	   T2."Sell-to Customer No_" as "Sell-to Customer No",
    20. 

  20. 	   T2."Customer Posting Group" as "Customer Posting Group",
    21. 

  21. 	   T2."Department Code" as "Department Code",
    22. 

  22. 	   T2."Make Code" as "Make Code",
    23. 

  23. 	   T2."Salesperson Code" as "Salesperson Code",
    24. 

  24. 	   T2."User ID" as "User Id",
    25. 

  25. 	   T2."Source Code" as "Source Code",
    26. 

  26. 	   T2."On Hold" as "On Hold",
    27. 

  27. 	   T2."Applies-to Doc_ Type" as "Applies-to Doc Type",
    28. 

  28. 	   T2."Applies-to Doc_ No_" as "Applies-to Doc No",
    29. 

  29. 	   T2."Open" as "Open",
    30. 

  30. 	   T2."Due Date" as "Due Date",
    31. 

  31. 	   T2."Pmt_ Discount Date" as "Pmt Discount Date",
    32. 

  32. 	   T2."Positive" as "Positive",
    33. 

  33. 	   T2."Closed by Entry No_" as "Closed By Entry No",
    34. 

  34. 	   T2."Closed at Date" as "Closed At Date",
    35. 

  35. 	   T2."Applies-to ID" as "Applies-to Id",
    36. 

  36. 	   T2."Journal Batch Name" as "Journal Batch Name",
    37. 

  37. 	   T2."Reason Code" as "Reason Code",
    38. 

  38. 	   T2."Bal_ Account Type" as "Bal Account Type",
    39. 

  39. 	   T2."Bal_ Account No_" as "Bal Account No",
    40. 

  40. 	   T2."Transaction No_" as "Transaction No",
    41. 

  41. 	   T2."Document Date" as "Document Date",
    42. 

  42. 	   T2."External Document No_" as "External Document No",
    43. 

  43. 	   T2."Calculate Interest" as "Calculate Interest",
    44. 

  44. 	   T2."Closing Interest Calculated" as "Closing Interest Calculated",
    45. 

  45. 	   T2."No_ Series" as "No Series",
    46. 

  46. 	   T2."Closed by Currency Code" as "Closed By Currency Code",
    47. 

  47. 	   T2."Customer Group Code" as "Customer Group Code",
    48. 

  48. 	   T2."Branch Code" as "Branch Code",
    49. 

  49. 	   T2."Main Area" as "Main Area",
    50. 

  50. 	   T2."VIN" as "Vin",
    51. 

  51. 	   T2."Cash Reg_ Receipt No_" as "Cash Reg Receipt No",
    52. 

  52. 	   T2."Comment" as "Comment",
    53. 

  53. 	   T2."Is Vehicle" as "Is Vehicle",
    54. 

  54. 	   T2."Bulk Customer No_" as "Bulk Customer No",
    55. 

  55. 	   T2."Bulkcust_ Business" as "Bulkcust Business",
    56. 

  56. 	   T2."Book No_" as "Book No",
    57. 

  57. 	   T2."No_ of Bulk Customer" as "No Of Bulk Customer",
    58. 

  58. 	   T3."Code" as "Code_2",
    59. 

  59. 	   T3."Name" as "Name_2",
    60. 

  60. 	   T3."Consolidation Code" as "Consolidation Code",
    61. 

  61. 	   'Forderungen' as "Stufe 1",
    62. 

  62. 	   'Kundenforderungen' as "Stufe 2",
    63. 

  63. 	   T4."Code" as "Code",
    64. 

  64. 	   T4."Name" as "Name",
    65. 

  65. 	   CASE WHEN (T2."Main Area" = 3) THEN ('SC') ELSE null END as "Stufe 3",
    66. 

  66. 	   (convert(float, T2."Remaining Amount")) as "Remaining_Amount",
    67. 

  67. 	   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 "Stufe 5_vor_RA",
    68. 

  68. 	   CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END as "Betrieb Nr",
    69. 

  69. 	   T2."Due Date" as "Fälligkeitsdatum",
    70. 

  70. 	   (substring(T2."Document No_", 6, 1)) as "Doc_Nr_Stelle_2",
    71. 

  71. 	   (getdate()) as "Heute",
    72. 

  72. 	   (CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END) as "Stufe 4",
    73. 

  73. 	   (-1 * datediff(day, ((getdate())), T2."Due Date")) as "Tage",
    74. 

  74. 	   CASE WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) > 42) THEN ('älter 6 Wochen') WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) BETWEEN 22 AND 28) THEN ('3 - 4 Wochen') WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) BETWEEN 15 AND 21) THEN ('2 - 3 Wochen') WHEN (((-1 * datediff(day, ((getdate())), T2."Due Date"))) BETWEEN 0 AND 14) THEN ('0 - 2 Wochen') ELSE ('noch nicht fällig') END as "Staffel",
    75. 

  75. 	   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 "Verursacher_ori",
    76. 

  76. 	   CASE WHEN (T1."No_" IN ('690101','690102','690103','690104','690105')) THEN (T2."Document No_" + ' - ' + T2."Description" + ' - ' + (convert(varchar(50), year(T2."Due Date")) + '-' + convert(varchar(50), month(T2."Due Date")) + '-' + convert(varchar(50), day(T2."Due Date"))) + ' / ' + (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" + ' - ' + (convert(varchar(50), year(T2."Due Date")) + '-' + convert(varchar(50), month(T2."Due Date")) + '-' + convert(varchar(50), day(T2."Due Date"))) + ' / ' + (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 "Beleg",
    77. 

  77. 	   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 "Verursacher",
    78. 

  78. 	   T7."Rechtsanwalt" as "Rechtsanwalt",
    79. 

  79. 	   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 "Stufe 5",
    80. 

  80. 	   T2."Client_DB" as "Hauptbetrieb_ID",
    81. 

  81. 	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
    82. 

  82. 	   (CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END) as "Standort_ID",
    83. 

  83. 	   CASE WHEN (((CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN (((substring(T2."Document No_", 6, 1))) = '1') THEN ('10') WHEN (((substring(T2."Document No_", 6, 1))) = '2') THEN ('20') WHEN (((substring(T2."Document No_", 6, 1))) = '3') THEN ('30') WHEN (((substring(T2."Document No_", 6, 1))) = '4') THEN ('40') WHEN (((substring(T2."Document No_", 6, 1))) = '5') THEN ('50') WHEN (((substring(T2."Document No_", 6, 1))) = '9') THEN ('55') WHEN (((substring(T2."Document No_", 6, 1))) = '6') THEN ('60') WHEN (((substring(T2."Document No_", 6, 1))) = '7') THEN ('70') WHEN (((substring(T2."Document No_", 6, 1))) = '8') THEN ('80') ELSE null END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name"
    84. 

  84. 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 "ims"."Forderungsmanagement_RA_KZ" T7 on (T2."Customer No_" = T7."KNDNR") and (T2."Document No_" = T7."O500_BELEGNR1"))
    85. 

  85. where ((((((convert(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%')))
    86. 

  86. -- order by "Posting Date" asc
    87.