给你Oracle EBS R12 GL 核心物理表「可落地参考 DDL 字段清单」。说明先行避免误用以下是EBS R1212.1/12.2GL Schema 下核心堆表 的精简可建 DDL去掉了 Oracle 原厂索引/同义词/审计触发器/多租户列字段名、类型、NOT NULL、含义与GL_*原表一致可直接用于沙箱/文档/迁移映射。真实 EBS 环境请用DESCRIBE或查DBA_TAB_COLUMNS取精确精度不要在生产直建这些表已有 APPS 视图GL_JE_BATCHES等。分类①账簿主数据 ②科目弹性域 ③期间/汇率 ④凭证批-头-行 ⑤余额 ⑥接口表 ⑦子账追溯(XLA) ⑧辅助来源/分类。Who 字段约定LAST_UPDATE_DATE DATE, LAST_UPDATED_BY NUMBER, CREATION_DATE DATE, CREATED_BY NUMBER, LAST_UPDATE_LOGIN NUMBER。1. 账簿与账套1.1 GL_LEDGERSR12 主账簿表替代旧 GL_SETS_OF_BOOKSCREATE TABLE GL_LEDGERS ( LEDGER_ID NUMBER NOT NULL, NAME VARCHAR2(100) NOT NULL, SHORT_NAME VARCHAR2(20), DESCRIPTION VARCHAR2(240), LEDGER_CATEGORY_CODE VARCHAR2(30), OBJECT_TYPE_CODE VARCHAR2(30), CHART_OF_ACCOUNTS_ID NUMBER, CURRENCY_CODE VARCHAR2(15) NOT NULL, PERIOD_SET_NAME VARCHAR2(15), ACCOUNTED_PERIOD_TYPE VARCHAR2(15), FIRST_LEDGER_PERIOD_NAME VARCHAR2(15), RET_EARN_CODE_COMBINATION_ID NUMBER, SUSPENSE_ALLOWED_FLAG VARCHAR2(1), ENABLE_BUDGETARY_CONTROL_FLAG VARCHAR2(1), ENABLE_JE_APPROVAL_FLAG VARCHAR2(1), SLA_ACCOUNTING_METHOD_CODE VARCHAR2(30), COMPLETE_FLAG VARCHAR2(1), LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER NOT NULL, LAST_UPDATE_LOGIN NUMBER, ATTRIBUTE_CATEGORY VARCHAR2(150), ATTRIBUTE1 VARCHAR2(150), ATTRIBUTE2 VARCHAR2(150), CONSTRAINT GL_LEDGERS_PK PRIMARY KEY (LEDGER_ID) );要点LEDGER_ID是 R12 后统一主键SET_OF_BOOKS_ID在旧表GL_SETS_OF_BOOKS中R12 程序内部做映射。2. 会计科目弹性域组合2.1 GL_CODE_COMBINATIONSCCID 物理表CREATE TABLE GL_CODE_COMBINATIONS ( CODE_COMBINATION_ID NUMBER NOT NULL, CHART_OF_ACCOUNTS_ID NUMBER NOT NULL, SEGMENT1 VARCHAR2(25), SEGMENT2 VARCHAR2(25), SEGMENT3 VARCHAR2(25), SEGMENT4 VARCHAR2(25), SEGMENT5 VARCHAR2(25), SEGMENT6 VARCHAR2(25), SEGMENT7 VARCHAR2(25), -- SEGMENT8..SEGMENT30 视 COA 结构动态启用 SUMMARY_FLAG VARCHAR2(1), ENABLED_FLAG VARCHAR2(1), START_DATE_ACTIVE DATE, END_DATE_ACTIVE DATE, DETAIL_POSTING_ALLOWED_FLAG VARCHAR2(1), DETAIL_BUDGETING_ALLOWED_FLAG VARCHAR2(1), ACCOUNT_TYPE VARCHAR2(1), LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER NOT NULL, LAST_UPDATE_LOGIN NUMBER, ATTRIBUTE_CATEGORY VARCHAR2(150), ATTRIBUTE1 VARCHAR2(150), ATTRIBUTE2 VARCHAR2(150), CONSTRAINT GL_CODE_COMBINATIONS_PK PRIMARY KEY (CODE_COMBINATION_ID) );哪个 SEGMENTn 对应“公司/成本中心/科目”由FND_ID_FLEX_SEGMENTS决定不是固定第 3 段就是自然科目。2.2 FND_ID_FLEX_STRUCTURES / FND_ID_FLEX_SEGMENTS弹性域结构必带仅列关键列CREATE TABLE FND_ID_FLEX_STRUCTURES ( APPLICATION_ID NUMBER, ID_FLEX_CODE VARCHAR2(30), ID_FLEX_NUM NUMBER, ID_FLEX_STRUCTURE_NAME VARCHAR2(240), DESCRIPTION VARCHAR2(240), CONCATENATED_SEGMENT_DELIMITER VARCHAR2(1) ); CREATE TABLE FND_ID_FLEX_SEGMENTS ( APPLICATION_ID NUMBER, ID_FLEX_CODE VARCHAR2(30), ID_FLEX_NUM NUMBER, SEGMENT_NUM NUMBER, APPLICATION_COLUMN_NAME VARCHAR2(30), -- 如 SEGMENT3 FLEX_VALUE_SET_ID NUMBER );3. 会计期间与汇率3.1 GL_PERIOD_SETSCREATE TABLE GL_PERIOD_SETS ( PERIOD_SET_NAME VARCHAR2(15) NOT NULL, PERIOD_SET_DESCRIPTION VARCHAR2(240), LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER NOT NULL );3.2 GL_PERIODSCREATE TABLE GL_PERIODS ( PERIOD_SET_NAME VARCHAR2(15) NOT NULL, PERIOD_NAME VARCHAR2(15) NOT NULL, PERIOD_TYPE VARCHAR2(15), START_DATE DATE, END_DATE DATE, FISCAL_YEAR NUMBER, PERIOD_NUM NUMBER, QUARTER_NUM NUMBER, YEAR_START_DATE DATE, CONSTRAINT GL_PERIODS_PK PRIMARY KEY (PERIOD_SET_NAME, PERIOD_NAME) );3.3 GL_PERIOD_STATUSES期间开关控制CREATE TABLE GL_PERIOD_STATUSES ( LEDGER_ID NUMBER NOT NULL, PERIOD_NAME VARCHAR2(15) NOT NULL, PERIOD_SET_NAME VARCHAR2(15), START_DATE DATE, END_DATE DATE, CLOSING_STATUS VARCHAR2(1), -- O open / C closed / F future / P perm closed LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, CONSTRAINT GL_PERIOD_STATUSES_PK PRIMARY KEY (LEDGER_ID, PERIOD_NAME) );3.4 GL_DAILY_RATESCREATE TABLE GL_DAILY_RATES ( FROM_CURRENCY VARCHAR2(15) NOT NULL, TO_CURRENCY VARCHAR2(15) NOT NULL, CONVERSION_DATE DATE NOT NULL, CONVERSION_TYPE VARCHAR2(30) NOT NULL, CONVERSION_RATE NUMBER NOT NULL, LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, CONSTRAINT GL_DAILY_RATES_PK PRIMARY KEY (FROM_CURRENCY, TO_CURRENCY, CONVERSION_DATE, CONVERSION_TYPE) );4. 日记账批 / 头 / 行核心交易表4.1 GL_JE_BATCHESCREATE TABLE GL_JE_BATCHES ( JE_BATCH_ID NUMBER NOT NULL, NAME VARCHAR2(100), DESCRIPTION VARCHAR2(240), STATUS VARCHAR2(1), -- U/P/S/I STATUS_VERIFIED VARCHAR2(1), ACTUAL_FLAG VARCHAR2(1), -- A/B/E DEFAULT_PERIOD_NAME VARCHAR2(15), DEFAULT_EFFECTIVE_DATE DATE, CONTROL_TOTAL NUMBER, RUNNING_TOTAL_DR NUMBER, RUNNING_TOTAL_CR NUMBER, RUNNING_TOTAL_ACCOUNTED_DR NUMBER, RUNNING_TOTAL_ACCOUNTED_CR NUMBER, POSTED_DATE DATE, APPROVAL_STATUS_CODE VARCHAR2(1), JE_SOURCE VARCHAR2(25), GROUP_ID NUMBER, LEDGER_ID NUMBER, LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER NOT NULL, LAST_UPDATE_LOGIN NUMBER, ATTRIBUTE_CATEGORY VARCHAR2(150), ATTRIBUTE1 VARCHAR2(150), ATTRIBUTE2 VARCHAR2(150), CONSTRAINT GL_JE_BATCHES_PK PRIMARY KEY (JE_BATCH_ID) );4.2 GL_JE_HEADERSCREATE TABLE GL_JE_HEADERS ( JE_HEADER_ID NUMBER NOT NULL, LEDGER_ID NUMBER, JE_BATCH_ID NUMBER, JE_CATEGORY VARCHAR2(25), JE_SOURCE VARCHAR2(25), PERIOD_NAME VARCHAR2(15), NAME VARCHAR2(100), DESCRIPTION VARCHAR2(240), STATUS VARCHAR2(1), -- U/P CURRENCY_CODE VARCHAR2(15), CURRENCY_CONVERSION_TYPE VARCHAR2(30), CURRENCY_CONVERSION_DATE DATE, EFFECTIVE_DATE DATE, POSTED_DATE DATE, ACTUAL_FLAG VARCHAR2(1), CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER NOT NULL, LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, LAST_UPDATE_LOGIN NUMBER, ATTRIBUTE_CATEGORY VARCHAR2(150), ATTRIBUTE1 VARCHAR2(150), CONSTRAINT GL_JE_HEADERS_PK PRIMARY KEY (JE_HEADER_ID) );R12 起头表用LEDGER_ID老程序/视图里还能看到SET_OF_BOOKS_ID通过GL_LEDGERS回溯。4.3 GL_JE_LINES最常用取数表CREATE TABLE GL_JE_LINES ( JE_HEADER_ID NUMBER NOT NULL, JE_LINE_NUM NUMBER NOT NULL, CODE_COMBINATION_ID NUMBER NOT NULL, LEDGER_ID NUMBER, PERIOD_NAME VARCHAR2(15), EFFECTIVE_DATE DATE, STATUS VARCHAR2(1), ENTERED_DR NUMBER, ENTERED_CR NUMBER, ACCOUNTED_DR NUMBER, ACCOUNTED_CR NUMBER, CURRENCY_CODE VARCHAR2(15), DR_CR_FLAG VARCHAR2(1), -- D/C DESCRIPTION VARCHAR2(240), REFERENCE_1 VARCHAR2(240), REFERENCE_2 VARCHAR2(240), REFERENCE_3 VARCHAR2(240), REFERENCE_4 VARCHAR2(240), STAT_AMOUNT NUMBER, SUBLEDGER_DOC_SEQUENCE_ID NUMBER, XLA_AE_HEADER_ID NUMBER, -- 追溯 XLA CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER NOT NULL, LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, LAST_UPDATE_LOGIN NUMBER, CONSTRAINT GL_JE_LINES_PK PRIMARY KEY (JE_HEADER_ID, JE_LINE_NUM) );ENTERED_*原币ACCOUNTED_*本位币借贷看DR_CR_FLAG或哪边非 NULL。5. 余额表5.1 GL_BALANCESCREATE TABLE GL_BALANCES ( LEDGER_ID NUMBER NOT NULL, CODE_COMBINATION_ID NUMBER NOT NULL, CURRENCY_CODE VARCHAR2(15) NOT NULL, PERIOD_NAME VARCHAR2(15) NOT NULL, ACTUAL_FLAG VARCHAR2(1) NOT NULL, -- A/B/E BUDGET_VERSION_ID NUMBER, ENCUMBRANCE_TYPE_ID NUMBER, TRANSLATED_FLAG VARCHAR2(1), BEGIN_BALANCE_DR NUMBER, BEGIN_BALANCE_CR NUMBER, PERIOD_NET_DR NUMBER, PERIOD_NET_CR NUMBER, END_BALANCE_DR NUMBER, END_BALANCE_CR NUMBER, LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER NOT NULL, LAST_UPDATE_LOGIN NUMBER, CONSTRAINT GL_BALANCES_PK PRIMARY KEY ( LEDGER_ID, CODE_COMBINATION_ID, CURRENCY_CODE, PERIOD_NAME, ACTUAL_FLAG, NVL(BUDGET_VERSION_ID,-1), NVL(ENCUMBRANCE_TYPE_ID,-1)) );期末余额 期初 本期净发生PTD/YTD 计算见前文公式。6. 接口表Journal Import6.1 GL_INTERFACECREATE TABLE GL_INTERFACE ( STATUS VARCHAR2(20), -- NEW / S / E / W LEDGER_ID NUMBER, SET_OF_BOOKS_ID NUMBER, -- 兼容老接口 USER_JE_SOURCE_NAME VARCHAR2(25), USER_JE_CATEGORY_NAME VARCHAR2(25), ACCOUNTING_DATE DATE, CURRENCY_CODE VARCHAR2(15), DATE_CREATED DATE, CREATED_BY NUMBER, ACTUAL_FLAG VARCHAR2(1), ENTERED_DR NUMBER, ENTERED_CR NUMBER, ACCOUNTED_DR NUMBER, ACCOUNTED_CR NUMBER, CODE_COMBINATION_ID NUMBER, SEGMENT1 VARCHAR2(25), SEGMENT2 VARCHAR2(25), SEGMENT3 VARCHAR2(25), SEGMENT4 VARCHAR2(25), SEGMENT5 VARCHAR2(25), PERIOD_NAME VARCHAR2(15), REFERENCE1 VARCHAR2(240), REFERENCE2 VARCHAR2(240), REFERENCE3 VARCHAR2(240), GROUP_ID NUMBER, CONVERSION_TYPE VARCHAR2(30), CONVERSION_DATE DATE, CONVERSION_RATE NUMBER );跑“Journal Import”并发程序后数据清掉转成批-头-行。6.2 GL_INTERFACE_CONTROLCREATE TABLE GL_INTERFACE_CONTROL ( GROUP_ID NUMBER, SOURCE_NAME VARCHAR2(25), REQUEST_ID NUMBER, STATUS VARCHAR2(1) );7. 子账追溯SLA 物理衔接7.1 GL_IMPORT_REFERENCESCREATE TABLE GL_IMPORT_REFERENCES ( JE_HEADER_ID NUMBER, JE_LINE_NUM NUMBER, XLA_AE_HEADER_ID NUMBER, XLA_AE_LINE_NUM NUMBER, SOURCE_TYPE VARCHAR2(30), REFERENCE_1 VARCHAR2(100), REFERENCE_2 VARCHAR2(100), REFERENCE_3 VARCHAR2(100), LAST_UPDATE_DATE DATE, LAST_UPDATED_BY NUMBER );7.2 XLA_AE_HEADERS / XLA_AE_LINESAP/AR/FA 先落这里关键列速览CREATE TABLE XLA_AE_HEADERS ( XLA_AE_HEADER_ID NUMBER NOT NULL, APPLICATION_ID NUMBER, LEDGER_ID NUMBER, JE_CATEGORY_NAME VARCHAR2(25), ACCOUNTING_DATE DATE, EVENT_TYPE_CODE VARCHAR2(30), GL_JE_HEADER_ID NUMBER, LAST_UPDATE_DATE DATE ); CREATE_TABLE XLA_AE_LINES ( XLA_AE_HEADER_ID NUMBER NOT NULL, XLA_AE_LINE_NUM NUMBER NOT NULL, CODE_COMBINATION_ID NUMBER, ENTERED_DR NUMBER, ENTERED_CR NUMBER, ACCOUNTED_DR NUMBER, ACCOUNTED_CR NUMBER, CURRENCY_CODE VARCHAR2(15) );实际还有XLA_DISTRIBUTION_LINKS等做 AP→GL 追溯必 JOIN8. 辅助配置表CREATE TABLE GL_JE_SOURCES ( JE_SOURCE VARCHAR2(25) NOT NULL, USER_JE_SOURCE_NAME VARCHAR2(25), DESCRIPTION VARCHAR2(240) ); CREATE TABLE GL_JE_CATEGORIES ( JE_CATEGORY VARCHAR2(25) NOT NULL, USER_JE_CATEGORY_NAME VARCHAR2(25), DESCRIPTION VARCHAR2(240) );9. 一张图记住主外键链GL_LEDGERS.LEDGER_ID └─ GL_PERIOD_STATUSES.LEDGER_ID └─ GL_JE_BATCHES.LEDGER_ID └─ GL_JE_HEADERS.JE_BATCH_ID (LEDGER_ID) └─ GL_JE_LINES.JE_HEADER_ID → GL_CODE_COMBINATIONS.CODE_COMBINATION_ID → XLA_AE_HEADERS/XLA_AE_LINES (via GL_IMPORT_REFERENCES) └─ GL_BALANCES.LEDGER_ID CODE_COMBINATION_ID PERIOD_NAME GL_CODE_COMBINATIONS.CHART_OF_ACCOUNTS_ID → FND_ID_FLEX_STRUCTURES10. 取数最常用骨架SELECT l.name ledger, b.period_name, gcc.segment1||.||gcc.segment3||.||gcc.segment4 ccid_desc, SUM(jl.accounted_dr) dr, SUM(jl.accounted_cr) cr FROM gl_je_headers jh JOIN gl_je_lines jl ON jl.je_header_id jh.je_header_id JOIN gl_je_batches jb ON jb.je_batch_id jh.je_batch_id JOIN gl_ledgers l ON l.ledger_id jh.ledger_id JOIN gl_code_combinations gcc ON gcc.code_combination_id jl.code_combination_id JOIN gl_periods p ON p.period_name jh.period_name WHERE jh.status P GROUP BY l.name, b.period_name, gcc.segment1, gcc.segment3, gcc.segment4;
