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"