Post
Ask Question
Ramco Ramco

Ramco Ramco

  • 426
  • 3.5k
  • 569.9k

How to get Opening Balance Value

Dec 27 2024 9:18 AM

Hi

  I have below code and how to get Opening Balance Amount. If user enters date from 01/04/2024 to 27/12/2024. How i can get Opening Balance i.e Balance before 01/04/2024.

SELECT  T3."ShortName" AS "Vendor Code",(Select Max("CardName") from OCRD where "CardCode" = T3."ShortName") AS "Vendor Name",
T3."Debit", T3."Credit"
FROM "TEST"."OJDT"  T2 INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" WHERE
(T2."TransId" not IN (SELECT T0."StornoToTr" FROM OJDT T0 where T0."StornoToTr" is not NULL)) AND (T2."StornoToTr" is NULL)
AND (T3."ShortName"=:Vendor )
and T2."TaxDate">= TO_DATE(:FromDate) and T2."TaxDate"<= TO_DATE(:ToDate)

UNION ALL

SELECT T2."CardCode" AS "Vendor Code", T2."CardName" AS "Vendor Name", (T2."DocTotal") + (T2."DpmAmnt") + (T2."DpmVat"),0
FROM "TEST"."OINV" T2
WHERE T2."DocDate" >= TO_DATE(:FromDate) and T2."DocDate" <= TO_DATE(:ToDate)
AND (T2."CardCode"=:Vendor ) AND T2."CANCELED" not in ('Y','C')

Thanks


Answers (1)