IT Life 資訊人生

2026-05-13

[Oracle]Oracle 3C與4C的概念

Chart of accounts
Functional Currency
Accounting Calendar
Accounting Convention(R12概念)

[Oracle]查詢YTD

SELECT GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' ||
       GCC.SEGMENT4 ACCOUNTING,
       SUM(GB.PERIOD_NET_DR),
       SUM(GB.PERIOD_NET_CR),
       SUM(GB.PERIOD_NET_DR) - SUM(GB.PERIOD_NET_CR)
  FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC
 WHERE 1 = 1
   AND GB.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
      --AND PERIOD_NAME = '04-21'
      --AND GCC.SEGMENT2 = 220019
      --AND GCC.SEGMENT3 = 2241
      -- AND GCC.SEGMENT4 = 011400
   AND PERIOD_NAME NOT IN ('05-21', '06-21', '07-21')
 GROUP BY GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' ||
          GCC.SEGMENT4,
          GB.CODE_COMBINATION_ID

[Oracle]檢查科目狀況含餘額

SELECT GCC.CODE_COMBINATION_ID CCID,
       SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 || '.' ||
       SEGMENT5 || '.' || SEGMENT6 GL_ACCOUNT_SEGMENT,
       GL.DESCRIPTION GL_ACCOUNT_TYPE,
       FFV.FLEX_VALUE,
       GL2.DESCRIPTION FLEX_ACCOUNT_TYPE,
	   SUM(NVL(GB.PERIOD_NET_DR, 0) - NVL(GB.PERIOD_NET_CR, 0)),
       SUM(NVL(GB.BEGIN_BALANCE_DR, 0) - NVL(GB.BEGIN_BALANCE_CR, 0) +
           NVL(GB.PERIOD_NET_DR, 0) - NVL(GB.PERIOD_NET_CR, 0)) YTD_AMOUNT
  FROM GL_BALANCES          GB,
       GL_CODE_COMBINATIONS GCC,
       GL_LOOKUPS           GL,
       FND_FLEX_VALUES_VL   FFV,
       GL_LOOKUPS           GL2,
       GL_SETS_OF_BOOKS     GSOB
 WHERE 1 = 1
   AND GSOB.SET_OF_BOOKS_ID = GB.LEDGER_ID
   AND GB.TRANSLATED_FLAG IS NULL
   AND GB.CODE_COMBINATION_ID(+) = GCC.CODE_COMBINATION_ID
   AND GCC.ACCOUNT_TYPE = GL.LOOKUP_CODE(+)
   AND GL.LOOKUP_TYPE = 'ACCOUNT TYPE'
   AND GL.ENABLED_FLAG <> 'N'
   AND GCC.CHART_OF_ACCOUNTS_ID = GSOB.CHART_OF_ACCOUNTS_ID
   AND GCC.SEGMENT3 = FFV.FLEX_VALUE(+)
   AND SUBSTR(FFV.COMPILED_VALUE_ATTRIBUTES, 5, 1) = GL2.LOOKUP_CODE(+)
   AND GL2.LOOKUP_TYPE(+) = 'ACCOUNT TYPE'
   AND GL2.ENABLED_FLAG(+) <> 'N'
   AND GB.PERIOD_NUM = 12 --TW、LY、AEG 15、TJ 12
   AND GB.LEDGER_ID = 1001 --TW 1003、LY 3003、AEG 4051、TJ 1001
   AND FFV.FLEX_VALUE_SET_ID(+) = 1009677 --TW 1010846、LY 1011571、AEG 1019500、TJ 1009677
   AND GB.PERIOD_YEAR = '2022'
--AND GCC.SEGMENT3 = 1101
 HAVING
 SUM(NVL(GB.BEGIN_BALANCE_DR, 0) - NVL(GB.BEGIN_BALANCE_CR, 0) +
           NVL(GB.PERIOD_NET_DR, 0) - NVL(GB.PERIOD_NET_CR, 0)) <> 0
 GROUP BY GCC.CODE_COMBINATION_ID,
          SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' || SEGMENT4 || '.' ||
          SEGMENT5 || '.' || SEGMENT6,
          GL.DESCRIPTION,
          FFV.FLEX_VALUE,
          GL2.DESCRIPTION
 ORDER BY GL.DESCRIPTION, FFV.FLEX_VALUE

[Oracle]R12 為什麼總帳Effective Date默認期間最後一天

觀念這個設定沒有對錯，這是一種標準作法

修改步驟

Profile:GL Journal Import: Separate Journals by Accounting Date to Yes

Financials -> Accounting Setup Manager -> Accounting Setups
Query your ledger and click on the "Update" button
Then click on the update button of the "Subledger Accounting Options"
Query for subledger and click on the "Update Accounting Options"
In the field "General Ledger Journal Entry Summarization" select the "No Summarization" option

[Oracle]R12 新增LE時，Territory選不到對應國家

How to Define Territory for Legal Entity Definition in Release 12? (Doc ID 551078.1)

目前是參考Taiwan設定

Log into the responsibility Legal Entity Manager which belongs to application Legal Entity Configurator.
Go to Setup -> Jurisdictions -> Create Jurisdiction
Select the required Legislative Category and click Go.
In the next page select the required Territory and complete the Setup