Three Oracle SQL queries for ECC: G/L accounts, cost elements, and the two combined. Each section goes query → result → notes.
Unlike S/4HANA, ECC keeps G/L accounts (SKA1) and cost elements (CSKA/CSKB) as separate masters. SKA1 has no GLACCOUNT_TYPE field; whether an account is a cost element is decided by whether a CSKB row exists.
Verified on IDES ECC 6.0 EhP7 / client 800 / company code 1000 (IDES AG, chart of accounts INT, EUR) / controlling area 1000 (CO Europe). The result values were read in SE16N. Replace the schema prefix SAPSR3 with the one on your system.
1. G/L accounts — query
| Case | G/L accounts used by a company code (T001 + SKA1 + SKB1 + SKAT) |
|---|
| What it does | The chart-of-accounts master (SKA1) joined to the company code segment (SKB1) and the texts (SKAT). The INNER JOIN on SKB1 keeps only accounts that were actually extended to the company code. |
| Result | 1,400 rows for SKB1 BUKRS = 1000 (SE16N Number of Entries) |
SELECT t.BUKRS,
t.KTOPL,
LTRIM(a.SAKNR, '0') AS GL_ACCOUNT,
s.TXT20 AS GL_TEXT_SHORT,
s.TXT50 AS GL_TEXT_LONG,
a.XBILK AS IS_BALANCE_SHEET,
a.GVTYP AS PL_STATEMENT_TYPE,
a.KTOKS AS ACCOUNT_GROUP,
b.WAERS AS ACCOUNT_CURRENCY,
b.MWSKZ AS TAX_CATEGORY,
b.XOPVW AS OPEN_ITEM_MGMT,
b.XKRES AS LINE_ITEM_DISPLAY,
b.FSTAG AS FIELD_STATUS_GROUP,
b.XSPEB AS BLOCKED_FOR_POSTING
FROM SAPSR3.T001 t
JOIN SAPSR3.SKA1 a ON a.MANDT = t.MANDT
AND a.KTOPL = t.KTOPL
JOIN SAPSR3.SKB1 b ON b.MANDT = t.MANDT
AND b.BUKRS = t.BUKRS
AND b.SAKNR = a.SAKNR
LEFT JOIN SAPSR3.SKAT s ON s.MANDT = t.MANDT
AND s.KTOPL = a.KTOPL
AND s.SAKNR = a.SAKNR
AND s.SPRAS = 'E'
WHERE t.MANDT = '800'
AND t.BUKRS = '1000'
ORDER BY a.SAKNR;
Result — sample of the 1,400 rows (company code 1000)
GL_ACCOUNT | TEXT (TXT20 / TXT50) | TYPE | OTHER (WAERS, MWSKZ, FSTAG, XOPVW)
-----------+----------------------------------+-------------------------+-----------------------------------
1 | CE costi / Conto Economico costi | P&L (GVTYP=X) | EUR, tax <, FSGS, open item mgmt X
23 | cont / cont sap | P&L (GVTYP=X) | EUR, tax <, FCEJ
24 | CONT PAT / CONT PAT 2 | Balance sheet (XBILK=X) | EUR, tax *, 63FS
25 | CONT EC / CONT EC SAP | P&L (GVTYP=X) | EUR, tax >, G001
50 | Costi Materie Prime | P&L (GVTYP=A) | EUR, tax <, G001
1-1. Notes on the query
| Group | Item | Detail |
|---|
| Table | T001 | Company code master. Supplies the chart of accounts (KTOPL) that links to SKA1. Company code 1000 → KTOPL = INT. |
| Table | SKA1 | Account master at chart-of-accounts level. Key = MANDT + KTOPL + SAKNR. XBILK = X means balance sheet account; blank XBILK with a filled GVTYP means P&L account. |
| Table | SKB1 | Company code segment. Key = MANDT + BUKRS + SAKNR. Posting-related settings live here: currency, tax category, open item management, field status group. |
| Table | SKAT | Account texts. Key = MANDT + SPRAS + KTOPL + SAKNR. Without the language condition (SPRAS) the row count multiplies by the number of languages. |
| Watch out | MANDT condition | Most SAP tables hold data per client. Without MANDT in the join conditions and the WHERE clause, other clients' data gets mixed in. |
| Watch out | Leading zeros | SAKNR is CHAR(10), right-aligned, so the database stores '0000000050'. LTRIM(SAKNR, '0') gives the same form as the screen. |
2. Cost elements — query
| Case | Cost elements of a controlling area (TKA01 + CSKB + CSKA + CSKU) |
|---|
| What it does | Cost elements are keyed by controlling area (KOKRS), not company code. TKA01 supplies the chart of accounts of that area, which links CSKA and CSKU. |
| Result | 440 rows for CSKB KOKRS = 1000 with DATBI = 31.12.9999. Without the validity condition the rows repeat once per validity period. |
SELECT k.KOKRS,
k.KTOPL,
LTRIM(cb.KSTAR, '0') AS COST_ELEMENT,
cu.KTEXT AS CE_TEXT_SHORT,
cu.LTEXT AS CE_TEXT_LONG,
cb.KATYP AS CE_CATEGORY,
CASE cb.KATYP
WHEN '01' THEN 'Primary cost / cost-reducing revenue'
WHEN '03' THEN 'Accrual (percentage method)'
WHEN '04' THEN 'Accrual (target=actual method)'
WHEN '11' THEN 'Revenue'
WHEN '12' THEN 'Sales deduction'
WHEN '21' THEN 'Internal settlement'
WHEN '22' THEN 'External settlement'
WHEN '31' THEN 'Order/project results analysis'
WHEN '41' THEN 'Overhead'
WHEN '42' THEN 'Assessment'
WHEN '43' THEN 'Internal activity allocation'
WHEN '90' THEN 'Statistical cost element (balance sheet account)'
ELSE NULL
END AS CE_CATEGORY_TEXT,
cb.DATAB AS VALID_FROM,
cb.DATBI AS VALID_TO,
cb.KOSTL AS DEFAULT_COST_CENTER
FROM SAPSR3.TKA01 k
JOIN SAPSR3.CSKB cb ON cb.MANDT = k.MANDT
AND cb.KOKRS = k.KOKRS
LEFT JOIN SAPSR3.CSKA ca ON ca.MANDT = k.MANDT
AND ca.KTOPL = k.KTOPL
AND ca.KSTAR = cb.KSTAR
LEFT JOIN SAPSR3.CSKU cu ON cu.MANDT = k.MANDT
AND cu.KTOPL = k.KTOPL
AND cu.KSTAR = cb.KSTAR
AND cu.SPRAS = 'E'
WHERE k.MANDT = '800'
AND k.KOKRS = '1000'
AND cb.DATBI = '99991231'
ORDER BY cb.KSTAR;
Result — sample of the 440 rows (controlling area 1000)
COST_ELEMENT | TEXT | CATEGORY (KATYP) | OTHER
-------------+---------------------+---------------------------------+-----------------------------
2 | (no text) | 01 Primary cost | 01.06.2023 ~ 31.12.9999
1004 | (no text) | 21 Internal settlement | 01.06.2023 ~ 31.12.9999
1421 | Labor | 43 Internal activity allocation | 01.01.1994 ~ 31.12.2400
11000 | Plant and Machinery | 90 Statistical cost element | for a balance sheet account
12322 | Energy consumption | 42 Assessment | 15.06.2021 ~ 31.12.9999
90001 | Labour Hours | 43 Internal activity allocation | secondary cost element
211100 | test | 01 Primary cost | default cost center T-PM4320
2-1. Notes on the query
| Group | Item | Detail |
|---|
| Table | CSKB | Controlling-area-dependent data of the cost element. Key = MANDT + KOKRS + KSTAR + DATBI. Because the valid-to date (DATBI) is part of the key, one cost element has one row per validity period. |
| Table | CSKA | Chart-of-accounts-dependent data of the cost element. Key = MANDT + KTOPL + KSTAR. Holds attributes that do not depend on the controlling area. |
| Table | CSKU | Cost element texts. Key = MANDT + SPRAS + KTOPL + KSTAR. Keyed by chart of accounts, so the join is on KTOPL, not KOKRS. |
| Field | KATYP | Cost element category. 01 = primary cost, 11 = revenue, 12 = sales deduction, 21 = internal settlement, 22 = external settlement, 31 = results analysis, 41 = overhead, 42 = assessment, 43 = internal activity allocation, 90 = statistical (balance sheet account). 41, 42 and 43 are secondary cost elements. |
| Watch out | DATBI condition | Without DATBI = '99991231' the rows of past validity periods come along and the same cost element appears several times. For a key date use DATAB <= key date AND DATBI >= key date instead. |
| Watch out | Not by company code | CSKB has no BUKRS field. To go from a company code, pass through TKA02 (company code → controlling area). |
3. G/L accounts + cost elements combined — query
| Case | Accounts of one company code with a cost element flag (T001 + TKA02 + SKA1 + SKB1 + SKAT + CSKB + CSKU) |
|---|
| What it does | G/L accounts are the driving table and the cost element is LEFT JOINed. Accounts that are not cost elements stay in the list, and IS_COST_ELEMENT shows Y/N directly. |
| Result | One row per account extended to company code 1000 (same 1,400 accounts as section 1) with the cost element columns filled where a CSKB row exists |
SELECT t.BUKRS,
ka.KOKRS,
t.KTOPL,
LTRIM(a.SAKNR, '0') AS GL_ACCOUNT,
s.TXT20 AS GL_TEXT,
a.XBILK AS IS_BALANCE_SHEET,
a.GVTYP AS PL_STATEMENT_TYPE,
CASE WHEN cb.KSTAR IS NULL THEN 'N' ELSE 'Y' END AS IS_COST_ELEMENT,
cb.KATYP AS CE_CATEGORY,
cu.KTEXT AS CE_TEXT,
cb.DATAB AS CE_VALID_FROM,
cb.DATBI AS CE_VALID_TO
FROM SAPSR3.T001 t
JOIN SAPSR3.TKA02 ka ON ka.MANDT = t.MANDT
AND ka.BUKRS = t.BUKRS
AND ka.GSBER = ' '
JOIN SAPSR3.SKA1 a ON a.MANDT = t.MANDT
AND a.KTOPL = t.KTOPL
JOIN SAPSR3.SKB1 b ON b.MANDT = t.MANDT
AND b.BUKRS = t.BUKRS
AND b.SAKNR = a.SAKNR
LEFT JOIN SAPSR3.SKAT s ON s.MANDT = t.MANDT
AND s.KTOPL = a.KTOPL
AND s.SAKNR = a.SAKNR
AND s.SPRAS = 'E'
LEFT JOIN SAPSR3.CSKB cb ON cb.MANDT = t.MANDT
AND cb.KOKRS = ka.KOKRS
AND cb.KSTAR = a.SAKNR
AND cb.DATBI = '99991231'
LEFT JOIN SAPSR3.CSKU cu ON cu.MANDT = t.MANDT
AND cu.KTOPL = t.KTOPL
AND cu.KSTAR = a.SAKNR
AND cu.SPRAS = 'E'
WHERE t.MANDT = '800'
AND t.BUKRS = '1000'
ORDER BY a.SAKNR;
| Case | Filtering — add one line to the WHERE clause |
|---|
| What it does | Append one of the following to the end of the WHERE clause of the query above. |
| Watch out | Filter (c) returns nothing in this G/L-driven query — see the notes below. |
-- (a) only accounts that are cost elements
AND cb.KSTAR IS NOT NULL
-- (b) only accounts that are not cost elements (balance sheet accounts etc.)
AND cb.KSTAR IS NULL
-- (c) only secondary cost elements (41/42/43) -- these do not exist as G/L accounts
AND cb.KATYP IN ('41', '42', '43')
Result — how the combined query classifies four sample objects
ACCOUNT / COST ELEMENT | TEXT | WHERE IT EXISTS | NOTE
-----------------------+------------------------+--------------------+---------------------------------------------
Account 2 | CE costi (P&L account) | exists in both → Y | in CSKB with KATYP = 01
Account 3 | debiti vs fornitori | G/L only → N | XBILK = X balance sheet account, as expected
Cost element 90001 | Labour Hours | cost element only | KATYP = 43 secondary cost element
Cost element 12322 | Energy consumption | cost element only | KATYP = 42 assessment
3-1. Notes on the query
| Group | Item | Detail |
|---|
| Design | G/L as the base | G/L accounts are on the left and cost elements are LEFT JOINed. Accounts without a cost element do not disappear, so the output is "all accounts of the company code + cost element flag". |
| Design | Via TKA02 | To get from a company code to cost elements you need the controlling area from TKA02. T001 has no KOKRS field at all. |
| Watch out | TKA02 duplicates | The TKA02 key includes the business area (GSBER). On systems that assign by business area the rows multiply, hence the GSBER = ' ' condition. |
| Watch out | Secondary cost elements are missing | KATYP 41 / 42 / 43 are CO-only objects with no G/L account, so a G/L-driven join never shows them. Run the section 2 query separately or switch to a FULL OUTER JOIN. |
| Reference | Difference to S/4HANA | In S/4HANA the account and the cost element are merged and SKA1-GLACCOUNT_TYPE (P = primary, S = secondary) is the single distinguishing field. ECC has no such field, so the CSKB row existence is the test. |
Comments
Post a Comment