IT Life 資訊人生

2026-05-15

[Oracle]R12 客戶信用額度

Credit Limit(信用額度)

針對信用風險狀況，當保留時顯示Credit check hold applied. Overall limit exceeded. Using customer credit profile.

Order Credit Limit(訂單信用額度)

單筆最大訂單金額，當保留時顯示Credit check hold applied. Order limit exceeded. Using customer credit profile.

[Oracle]客戶修改名稱出現錯誤(This tax registration number is already in use for party type: Third Party and party name: XXX for the same period.)

參閱文件編號(KB780042)R12: AP/AR/ZX: How to Create More Than One Party With the Same VAT Registration Number to Avoid the Error 'This tax registration number is already in use for party type: Third Party and party name: XXXXXX for the same period.'

SELECT * FROM APPS.HZ_PARTIES HP WHERE HP.PARTY_NUMBER IN (472473,471303,472474)，發現這個名稱關係如下

依照文件研究了一下後，決定把Tax Managers > Parties > Party Tax Profiles，查詢出要異動的資料把Default Reporting Registration Number清空，大概問題就是同一個Tax Number，不能建立於多個地方，

20260429 有測試打勾重覆Tax Number沒有作用的原因，大概是因為客戶主檔的Tax Profile有兩個地方Party與地址，我一直測試地址

[Oracle]ORA-20001: Organization Unit Type is Invalid, ORA-06512 At HR_ORGANIZATION_API and GL_LEDGERS_PKG

R12 ASM When Complete the Accounting Setup it Fails with ORA-20001: Organization Unit Type is Invalid, ORA-06512 At HR_ORGANIZATION_API and GL_LEDGERS_PKG (Doc ID 560007.1)

SELECT HAOU.ORGANIZATION_ID,
       HAOU.DATE_FROM,
       HAOU.NAME,
       HAOU.TYPE,
       HAOU.INTERNAL_EXTERNAL_FLAG,
       HAOU.LOCATION_ID,
       HOU.SET_OF_BOOKS_ID,
       NULL USABLE_FLAG,
       HOU.SHORT_CODE,
       HOU.DEFAULT_LEGAL_CONTEXT_ID,
       HAOU.OBJECT_VERSION_NUMBER
  FROM HR_OPERATING_UNITS HOU, HR_ALL_ORGANIZATION_UNITS HAOU
 WHERE SET_OF_BOOKS_ID = 1003
   AND HAOU.ORGANIZATION_ID = HOU.ORGANIZATION_ID;

SELECT *
  FROM HR_LOOKUPS
 WHERE LOOKUP_TYPE = 'ORG_TYPE'
   AND LOOKUP_CODE = 'OU'; --將第一個SQL查詢出來TYPE填入

SELECT LOOKUP_CODE,ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE
  FROM HR_LOOKUPS
 WHERE LOOKUP_TYPE = 'ORG_TYPE'
   AND LOOKUP_CODE IN ('OU', 'BG');

最後確認第一個SQL的HAOU.DATE_FROM與第三個SQL START_DATE_ACTIVE時間是否一致，如果沒有一致，修改成一致

Global HR Manager > Other Definitions -> Application Utilities Lookups

[Oracle]Document Number no Working

Profile設定Sequential Numbering:Always Used

Not Used : 所有Category不用sequence
Always Used : 所有Category必须要Assign用sequence，没有將Category assign sequence，開單就會報錯
Partially Used : 部份使用，Category有assign sequence就用，没有Assign就不用

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