Enhancement Request - Subledger Net Amount Mismatch

Natalie Blackwood

CAA - Transaction Net/Debit/Credit Amount (Accounted) to be rounded off to 2 decimals to match with OTBI.

Issue Description:

We have identified mismatch between OTBI and FDI subledger Transaction Net/Debit/Credit accounted amounts. After a thorough troubleshooting we found that this issue is due to the rounding. In OTBI these amounts are getting rounded off 2 decimals and whereas in FDI Configurable Account Analysis subject area, these metrics are brought without rounding and sum aggregated. Our final observation that applying rounding at FDI reporting level is not helping due to the SUM aggregation and it has to be applied on physical layer/database column in order to match with OTBI values. Attached referenced screenshots at the end.

We suspect if it is due to any exchange rate multiplication in FDI while calculating Ledger or Accounted amounts in Datawarehouse.

Kindly check if this change (rounding off to decimals) can be implemented at DWH table level or Semantic model level to match results with OTBI subledger subject area.

Example:

Invoice Number= XXXXX178

Code block= XX-999999-XXXXXX-999999-99-999999-999999

Journal Line Number =17

We are seeing mismatch in Net Amount Accounted (Debit – Credit) between OTBI (-57.77) and FDI (-57.76). Mismatch at second decimal point.

OTBI Report:

OTBI Net Amount fx=

IFNULL("Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount DR",0.00)-IFNULL("Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount CR",0.00)

We can notice in last column Net Amount rounded to 4 decimals, value is -57.7700 (this confirms that from database itself values are rounded off to 2 decimals)

FDI workbook

For same transaction in FDI.

Net Amount (Dr – Cr) formula:

ifnull("Configurable Account Analysis"."Transaction Amount (LC)"."Debit Amount (Accounted)",0.00)-ifnull("Configurable Account Analysis"."Transaction Amount (LC)"."Credit Amount (Accounted)",0.00)

which is similar to what we are doing in OTBI.

Note : This report has been created using Configurable Account Analysis subject area.

We also verified results in FDI ADW using workbook query.

Also tested same query by applying rounding to directly on physical amount columns and then we could see values are matching with OTBI report.

Note: Rounding at FDI report level not helping due to the sum aggregation.