opening balance in SAP HANA

[millicentdark]

Hi Expert,

i will like to know if there is any way or  procedure i can prepare a calculation view to calculate opening balance.

if anyone can assist with screenshots on how to achieve it i will be glad.

Regards

Justice

[millicentdark]

Hi all,

You can assist me to correct the attached query and i will be fine.

Regards

[millicentdark]

Hi experts,

Per my above query, should i bring the drop statement before my column table is created or after all my selection is done i drop the temp table.

Note i am using this for a crystal report for a customer and your quickest solution will be appreciated.

Regards

Justice

[millicentdark]

Hi ,

i want to use this query to design a crystal report for a customer.

I am using a date range parameter.

Any help?

[a_grootens]

Hi Justice,

Just add the TaxDate field in the select and groupby sections:

CREATE VIEW "SERVICE"."AFS_OPENINGBALANCE" AS SELECT A."Account", B."AcctName",

SUM(A."Debit" - A."Credit") AS "Opening Balance", A."TaxDate"

FROM "SERVICE"."JDT1" A

INNER JOIN "SERVICE"."OACT" B ON A."Account" = B."AcctCode" WHERE (A."TransType" = '-2')

GROUP BY A."Account", B."AcctName", A."TaxDate"

In your CR you can use this as a parameter.

Kind regards,

Andy

[millicentdark]

Hi Andy,

Please crosscheck my query. It has other joins.

Regards

Justice

[millicentdark]

CREATE PROCEDURE STAFF_RPORT (IN Prjcode1 date, IN prjcode2 date) AS BEGIN DROP TABLE "test_table";

CREATE COLUMN TABLE "test_table" AS (SELECT T2."TransId", T0."PrjCode", T0."PrjName", T1."Account", T3."AcctName", T1."RefDate", T2."Memo", T1."Debit", T1."Credit", T1."BatchNum", T2."CreateDate", T2.TaxDate, T0."U_PR_TYPE", T1."Ref2", T1."FCDebit", T1."FCCredit", T1."DueDate", IFNULL((SELECT SUM(t5."debit") FROM OPRJ T4 INNER JOIN JDT1 T5 ON T4."PrjCode" = T5."Project" INNER JOIN OJDT T6 ON T5."TransId" = T6."TransId" INNER JOIN OACT T7 ON T5."Account" = T7."AcctCode" WHERE T5."RefDate" < :Prjcode1 AND T0."PrjCode" = T4."PrjCode" AND t5."account" = t1."account"), 0) AS "Debit_BF", IFNULL((SELECT SUM(t5."credit") FROM OPRJ T4 INNER JOIN JDT1 T5 ON T4."PrjCode" = T5."Project" INNER JOIN OJDT T6 ON T5."TransId" = T6."TransId" INNER JOIN OACT T7 ON T5."Account" = T7."AcctCode" WHERE T5."RefDate" < :Prjcode1 AND T0."PrjCode" = T4."PrjCode" AND t5."account" = t1."account"), 0) AS "Credit_BF" FROM OPRJ T0 INNER JOIN JDT1 T1 ON T0."PrjCode" = T1."Project" INNER JOIN OJDT T2 ON T1."TransId" = T2."TransId" INNER JOIN OACT T3 ON T1."Account" = T3."AcctCode" WHERE T1.RefDate >= :Prjcode1 AND T1.RefDate <= :prjcode2 AND T0.U_PR_TYPE = 'Employee' ORDER BY t0.prjcode, T2."TransId");

SELECT * FROM test_table ORDER BY PrjCode;

END;

[a_grootens]

You can create a view in HANA Studio with this:

CREATE VIEW "SERVICE"."AFS_OPENINBALANCE" AS SELECT A."Account", B."AcctName", SUM(A."Debit" - A."Credit") AS "Opening Balance" FROM "SERVICE"."JDT1" A

INNER JOIN "SERVICE"."OACT" B ON A."Account" = B."AcctCode" WHERE (A."TransType" = '-2')

GROUP BY A."Account", B."AcctName"

(in this example I have used catalog "SERVICE". You should replace this with your own catalog name)

After that you can call it in SAP Business One with SELECT * from "AFS_OPENINGBALANCE" WHERE "Account" = '0060'

Kind regards,

Andy