{"id":561,"date":"2021-05-05T23:06:07","date_gmt":"2021-05-05T15:06:07","guid":{"rendered":"https:\/\/www.linyinghao.cn\/wordpress\/?p=561"},"modified":"2022-02-04T22:53:35","modified_gmt":"2022-02-04T14:53:35","slug":"oracle-financial-accounting-hub-get-supporting-reference-by-sql","status":"publish","type":"post","link":"https:\/\/www.linyinghao.cn\/wordpress\/?p=561","title":{"rendered":"Oracle Financial Accounting Hub – Getting Supporting Reference by SQL"},"content":{"rendered":"\n

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. <\/p>\n\n\n\n

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<\/a> for setup) and it dynamically picking up backend table columns, it is difficult to extract them accurately by SQL query.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Let’s have a look at following example. In this FAH journal line of cash account, there are 5 supporting references imported.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

If we take a look at data format in backend tables, we may notice these supporting references are stored in two tables:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n
  1. XLA_AE_LINES – This table stores values in columns like SR1, SR2, …, SR60.<\/li>
  2. XLA_SUP_REF_COMBINATIONS – This table stores supporting reference short names in columns like SUP_REF_CODE1, SUP_REF_CODE2, …, SUP_REF_CODE60.<\/li><\/ol>\n\n\n\n

    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<\/a>.<\/p>\n\n\n\n

    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:<\/p>\n\n\n\n

    \"\"<\/a><\/figure>\n\n\n\n

    Secondly, we take advantage of UNPIVOT to transpose the single line into multiple line, with pairing the related names and values.<\/p>\n\n\n\n

    \"\"<\/a><\/figure>\n\n\n\n

    Thirdly, we take advantage of PIVOT to transpose again the multiple line back to single line for each FAH journal line.<\/p>\n\n\n\n

    \"\"<\/a><\/figure>\n\n\n\n

    The whole SQL can be found here.<\/p>\n\n\n\n

    WITH CTE1 AS (\n    -- SLA\u660e\u7d30\u3068\u30b5\u30dd\u30fc\u30c8\u53c2\u7167\u304b\u3089\u30b3\u30fc\u30c9\u3068\u5024\u3092\u53d6\u5f97\n    SELECT XTH.TRANSACTION_NUMBER,\n\t\t   XAL.AE_LINE_NUM,\n\t\t   GCC.SEGMENT1 || '-' || GCC.SEGMENT2 || '-' || GCC.SEGMENT3 || '-' || GCC.SEGMENT4 CODE_COMBINATION,\n\t\t   XAL.ENTERED_DR,\n\t\t   XAL.ENTERED_CR,\n           XAL.SR26,\n           XAL.SR31,\n           XAL.SR32,\n           XAL.SR33,\n           XAL.SR34,\n           XAL.SR35,\n           XSRC.SUP_REF_CODE26,\n           XSRC.SUP_REF_CODE31,\n           XSRC.SUP_REF_CODE32,\n           XSRC.SUP_REF_CODE33,\n           XSRC.SUP_REF_CODE34,\n           XSRC.SUP_REF_CODE35\n    FROM   XLA_TRANSACTION_HEADERS XTH,\n           XLA_AE_HEADERS XAH,\n           XLA_AE_LINES XAL,\n\t\t   GL_CODE_COMBINATIONS GCC,\n           XLA_SUP_REF_COMBINATIONS XSRC\n    WHERE  1 = 1\n\tAND    XAH.AE_HEADER_ID = XAL.AE_HEADER_ID\n\tAND    XAH.APPLICATION_ID = XAL.APPLICATION_ID\n\tAND    XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID\n\tAND    XAL.SUPP_REF_COMBINATION_ID = XSRC.SUPP_REF_COMBINATION_ID\n\tAND    XTH.APPLICATION_ID = XAH.APPLICATION_ID\n\tAND    XTH.EVENT_ID = XAH.EVENT_ID\n\tAND    XTH.TRANSACTION_NUMBER = 'CL-004'\n),\n\nCTE2 AS (\n    -- CTE1\u3067\u53d6\u5f97\u3055\u308c\u305f\u5217\u3092\u884c\u306b\u5909\u63db\u3001\u30b5\u30dd\u30fc\u30c8\u53c2\u7167\u30b3\u30fc\u30c9\u3092\u5358\u4f4d\u3067\u8907\u6570\u884c\u3092\u623b\u3059\n    SELECT TRANSACTION_NUMBER,\n           AE_LINE_NUM,\n\t\t   CODE_COMBINATION,\n\t\t   ENTERED_DR,\n\t\t   ENTERED_CR,\n           SR_CODE,\n           SR_VALUE\n    FROM   CTE1\n    UNPIVOT (\n        (SR_CODE, SR_VALUE) FOR CODE IN (\n            (SUP_REF_CODE26, SR26) AS 'SR26',\n            (SUP_REF_CODE31, SR31) AS 'SR31',\n            (SUP_REF_CODE32, SR32) AS 'SR32',\n            (SUP_REF_CODE33, SR33) AS 'SR33',\n            (SUP_REF_CODE34, SR34) AS 'SR34',\n            (SUP_REF_CODE35, SR35) AS 'SR35'\n        )\n    )\n)\n\n-- \u5bfe\u8c61\u30b5\u30dd\u30fc\u30c8\u53c2\u7167\u3092\u6307\u5b9a\u3057\u3066\u3001\u5fc5\u8981\u306a\u5217\u304c\u4e00\u3064\u30ec\u30b3\u30fc\u30c9\u306b\u623b\u3059\nSELECT TRANSACTION_NUMBER,\n       AE_LINE_NUM,\n\t   CODE_COMBINATION,\n\t   ENTERED_DR,\n\t   ENTERED_CR,\n       LN_OFFICER,\n       LOAN_AMOUNT,\n       LOAN_NUMBER,\n       LOAN_SCHD_PAY_DATE,\n       LOAN_TYPE,\n       MORTGATE_OWNER\nFROM   CTE2\nPIVOT (\n    MIN(SR_VALUE) FOR SR_CODE IN (\n        '08LN_OFFICER' LN_OFFICER,\n        '08LOAN_AMOUNT' LOAN_AMOUNT,\n        '08LOAN_NUMBER' LOAN_NUMBER,\n        '08LOAN_SCHD_PAY_DATE' LOAN_SCHD_PAY_DATE,\n        '08LOAN_TYPE' LOAN_TYPE,\n        '08MORTGATE_OWNER' MORTGATE_OWNER\n    )\n)<\/code><\/pre>\n\n\n\n
    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.<\/p>\n","protected":false},"excerpt":{"rendered":"
    This article describes a SQL solution to get supporting reference in a suitable manner for BIP report.<\/p>\n","protected":false},"author":1,"featured_media":841,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[13],"class_list":["post-561","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-2","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/561","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=561"}],"version-history":[{"count":7,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/561\/revisions"}],"predecessor-version":[{"id":580,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/561\/revisions\/580"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/841"}],"wp:attachment":[{"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}