Consulta per obtenir la previsió de pagaments a una data pel SAP Business One versió per HANA:
-- PAGOS -- PAGAMENTS -- v2 SELECT T0."TransType" Objecte, CASE WHEN T0."TransType"=18 THEN 'Factura Compra' WHEN T0."TransType"=19 THEN 'Abonament Compra' 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" AS "Data Venciment", T1."Ref1" AS "Document", T1."Number" AS "Assentament", YEAR(T0."DueDate") "Any Venciment", MONTH(T0."DueDate") "Mes Venciment", CASE WHEN T0."TransType"=18 THEN CP."PymntGroup" WHEN T0."TransType"=19 THEN CP."PymntGroup" END AS "Condicio Pagament", CASE WHEN T0."TransType"=18 THEN FA."PeyMethod" WHEN T0."TransType"=19 THEN RC."PeyMethod" END AS "Via Pagament", T0."Account" "Codi compta", T0."ContraAct" "Codi compta contrapartida" , PC."AcctName" "Nom 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 OPCH FA on FA."DocEntry"=T0."CreatedBy" and T0."TransType"=18 -- Factures de compres LEFT JOIN ORPC RC on RC."DocEntry"=T0."CreatedBy" and T0."TransType"=19 -- Abonaments de compres 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: -- 18 (Factures de compres) -- 19 (Abonaments de compres) -- 24 (Cobraments) -- 30 (Assentaments) -- 46 (Pagaments) -- 203 (Factures d'anticips) -- que la seva compta sigui: -- 400000 (Proveïdors) -- 410000 (Acreedors) -- que no contiguin línies d'assentaments d'ajustos per diferencies de canvi (factures en divises) -- que siguin inferiors a una data de venciment concreta T0."Account" in ('400000','410000') --and ((T0."MthDate" is null) or ((T0."TransType"=18) 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 (18,19,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"