Consulta previsió de cobraments

Consulta de HANA per obtenir la previsó de cobraments a una data:

-- COBROS
-- COBRAMENTS
-- v2
SELECT 
T0."TransType" Objecte,
CASE 
  WHEN T0."TransType"=13 THEN 'Factura'
  WHEN T0."TransType"=14 THEN 'Abonament'
  WHEN T0."TransType"=24 THEN 'Cobrament'
  WHEN T0."TransType"=30 THEN 'Assentament'
  WHEN T0."TransType"=46 THEN 'Pagament'
  WHEN T0."TransType"=203 THEN 'Factura Acompte'
END AS "Tipus Document",
T1."RefDate" AS "Data Comptable",
T0."DueDate" "Data Venciment",
T1."Ref1" AS "Document",
T1."Number" AS "Assentament",
YEAR(T0."DueDate") "Any Venciment",
MONTH(T0."DueDate") "Mes Venciment",
CASE 
  WHEN T0."TransType"=13 THEN CP."PymntGroup"
  WHEN T0."TransType"=14 THEN CP."PymntGroup"
END AS "Condicio Pagament",
CASE 
  WHEN T0."TransType"=13 THEN FA."PeyMethod"
  WHEN T0."TransType"=14 THEN RC."PeyMethod"
END AS "Via Pagament",
T0."Account" "Codi de compta",
T0."ContraAct" "Compta contrapartida",
PC."AcctName" "Nom de la compta",
T0."ShortName" "Codi Client",
IC."CardName" "Nom Client",
T0."Debit"-T0."Credit" "Import MS",
T0."FCDebit"-T0."FCCredit" "Import ME",
IFNULL(T0."FCCurrency", C."MainCurncy") Divisa,
T0."BalDueDeb"-T0."BalDueCred" "Import Pendent MS",
T0."BalFcDeb"-T0."BalFcCred" "Import Pendent ME",
T0."LineMemo" "Comentari"
FROM JDT1 T0                                                                    -- Línies d'assentament
JOIN OJDT T1 on T1."TransId"=T0."TransId"                                       -- Capsalera d'assentament
LEFT JOIN OCRD IC on IC."CardCode"=T0."ShortName"                               -- Interlocutors comercials
LEFT JOIN OINV FA on FA."DocEntry"=T0."CreatedBy" and T0."TransType"=13         -- Factures
LEFT JOIN ORIN RC on RC."DocEntry"=T0."CreatedBy" and T0."TransType"=14         -- Abonaments
LEFT JOIN OCTG CP on CP."GroupNum"=FA."GroupNum" or CP."GroupNum"=RC."GroupNum" -- Condicions de pagament
LEFT JOIN OACT PC on PC."AcctCode"=T0."ContraAct"                               -- Plà de comptes
CROSS JOIN (SELECT TOP 1 * FROM OADM) C                                         -- Dades de la companyia SAP
WHERE
-- Condicions:
-- que siguin línies d'assentament sense reconciliar 
--   o reconciliades que tinguin saldo pendent, deutor o creditor
-- que pertanyin als objectes:
--   13 (Factures de vendes) 
--   14 (Abonaments de vendes) 
--   24 (Cobraments) 
--   30 (Assentaments)
--   46 (Pagaments) 
--   203 (Factures d'anticips)
-- que la seva compta sigui:
--   430000 (Clients) 
--   433000 (Clients del grup) 
--   436000 (Clients de dubtòs cobrament)
-- que no contiguin línies d'assentaments d'ajustos per diferencies de canvi
-- que siguin inferiors a una data de venciment concreta
T0."Account" in ('430000','433000','436000')
--and ((T0."MthDate" is null) or ((T0."TransType"=13) and (not T0."MthDate" is null) and (FA."PaidSum"<>FA."DocTotal")))
and ((T0."MthDate" is null) or ((not T0."MthDate" is null) and (T0."BalDueDeb">0 or T0."BalDueCred">0)))
and T0."TransType" in (13,14,24,30,46,203) 
and not T0."ContraAct" in ('668000','768000')
and T0."DueDate"<'[%0]'
-- Ordenat per client i venciment
ORDER BY IC."CardName",T0."DueDate"