Oracle Financial Accounting Hub is capable of imporing multiple supporting reference from external system to FAH(also known as SLA Subledger Accounting) journal line. Up to 60 supporting reference fields are supported.
However, when FAH data are sent to General Ledger, these supporting references are not sent along with other journal fields, so that they are only visible in FAH but not in GL. In some scenario, business may need to track back from GL jounal all the way to sub ledger journal and related supporting reference fields. Since supporting reference fields are configured by FAH Accounting Rules(refer to this article for setup) and it dynamically picking up backend table columns, it is difficult to extract them accurately by SQL query.
Fortunately, Oracle database supports transpose operations which enables advanced feature to resolve this kind of requirement. Here is an example how to extract these dynamically configured fields from single SQL.
Let’s have a look at following example. In this FAH journal line of cash account, there are 5 supporting references imported.
If we take a look at data format in backend tables, we may notice these supporting references are stored in two tables:
- XLA_AE_LINES – This table stores values in columns like SR1, SR2, …, SR60.
- XLA_SUP_REF_COMBINATIONS – This table stores supporting reference short names in columns like SUP_REF_CODE1, SUP_REF_CODE2, …, SUP_REF_CODE60.
The columns looks meaningless, right? So what’s the solution? In Oracle SQL, there are two keywords can be used to traspose the SQL – PIVOT and UNPIVOT. Detail usage can be found in this blog.
In our case, we divide the whole solution into 3 steps. Firstly we join XLA_AE_LINES and XLA_SUP_REF_COMBINATIONS to combinate SUP_REF_CODE and SR columns in same line:
Secondly, we take advantage of UNPIVOT to transpose the single line into multiple line, with pairing the related names and values.
Thirdly, we take advantage of PIVOT to transpose again the multiple line back to single line for each FAH journal line.
The whole SQL can be found here.
WITH CTE1 AS (
-- SLA明細とサポート参照からコードと値を取得
SELECT XTH.TRANSACTION_NUMBER,
XAL.AE_LINE_NUM,
GCC.SEGMENT1 || '-' || GCC.SEGMENT2 || '-' || GCC.SEGMENT3 || '-' || GCC.SEGMENT4 CODE_COMBINATION,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.SR26,
XAL.SR31,
XAL.SR32,
XAL.SR33,
XAL.SR34,
XAL.SR35,
XSRC.SUP_REF_CODE26,
XSRC.SUP_REF_CODE31,
XSRC.SUP_REF_CODE32,
XSRC.SUP_REF_CODE33,
XSRC.SUP_REF_CODE34,
XSRC.SUP_REF_CODE35
FROM XLA_TRANSACTION_HEADERS XTH,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
GL_CODE_COMBINATIONS GCC,
XLA_SUP_REF_COMBINATIONS XSRC
WHERE 1 = 1
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND XAL.SUPP_REF_COMBINATION_ID = XSRC.SUPP_REF_COMBINATION_ID
AND XTH.APPLICATION_ID = XAH.APPLICATION_ID
AND XTH.EVENT_ID = XAH.EVENT_ID
AND XTH.TRANSACTION_NUMBER = 'CL-004'
),
CTE2 AS (
-- CTE1で取得された列を行に変換、サポート参照コードを単位で複数行を戻す
SELECT TRANSACTION_NUMBER,
AE_LINE_NUM,
CODE_COMBINATION,
ENTERED_DR,
ENTERED_CR,
SR_CODE,
SR_VALUE
FROM CTE1
UNPIVOT (
(SR_CODE, SR_VALUE) FOR CODE IN (
(SUP_REF_CODE26, SR26) AS 'SR26',
(SUP_REF_CODE31, SR31) AS 'SR31',
(SUP_REF_CODE32, SR32) AS 'SR32',
(SUP_REF_CODE33, SR33) AS 'SR33',
(SUP_REF_CODE34, SR34) AS 'SR34',
(SUP_REF_CODE35, SR35) AS 'SR35'
)
)
)
-- 対象サポート参照を指定して、必要な列が一つレコードに戻す
SELECT TRANSACTION_NUMBER,
AE_LINE_NUM,
CODE_COMBINATION,
ENTERED_DR,
ENTERED_CR,
LN_OFFICER,
LOAN_AMOUNT,
LOAN_NUMBER,
LOAN_SCHD_PAY_DATE,
LOAN_TYPE,
MORTGATE_OWNER
FROM CTE2
PIVOT (
MIN(SR_VALUE) FOR SR_CODE IN (
'08LN_OFFICER' LN_OFFICER,
'08LOAN_AMOUNT' LOAN_AMOUNT,
'08LOAN_NUMBER' LOAN_NUMBER,
'08LOAN_SCHD_PAY_DATE' LOAN_SCHD_PAY_DATE,
'08LOAN_TYPE' LOAN_TYPE,
'08MORTGATE_OWNER' MORTGATE_OWNER
)
)For the sake of simplicity, I am not including all 60 supporting references fields. In real world, you may need to add as much supporting reference fields as required, of course up to 60 only. Hope this can provide a vaible solution for this kind of report demand.
