SAP Schema Expert
Quick start
- Search
assets/TABLE_INDEX.yaml for the table by name or description keyword.
- Read
assets/schemas/<TABLENAME>.yaml for full field definitions.
- If the schema file is absent, look up the URL in
assets/TABLE_INDEX.yaml and use fetch_webpage.
Workflows
Write a HANA SQL query
- Find the header table in
assets/TABLE_INDEX.yaml (e.g. OINV = A/R Invoice).
- Lines table = strip leading
O + append 1 → OINV → INV1.
- Read field names from
assets/schemas/OINV.yaml and assets/schemas/INV1.yaml.
- Build query — double-quote all identifiers (HANA is case-sensitive):
SELECT h."DocEntry", h."CardCode", h."DocDate", h."DocTotal",
l."LineNum", l."ItemCode", l."Dscription", l."Quantity", l."Price"
FROM "OINV" h
INNER JOIN "INV1" l ON l."DocEntry" = h."DocEntry"
WHERE h."DocStatus" = 'O' AND h."CANCELED" = 'N'
ORDER BY h."DocEntry", l."LineNum"
Map a business concept to a table
- Grep
assets/TABLE_INDEX.yaml for a keyword (e.g. delivery, invoice, partner).
- Cross-check the ObjType code via
assets/OBJ_TYPE_MAP.yaml (common: 13=OINV, 17=ORDR, 15=ODLN, 22=OPOR).
Key encoded values
| Column |
Table |
Values |
CardType |
OCRD |
C=Customer, S=Supplier, L=Lead |
DocStatus |
Most doc tables |
O=Open, C=Closed |
Canceled |
Most doc tables |
Y=Yes, N=No |
TreeType |
OITM |
N=No BOM, S=Sales BOM, A=Assembly |
Advanced features
See REFERENCE.md for:
- Full table naming conventions and all header/lines pairs
- Complete ObjType code table (17 common codes)
- Finding related tables via
REVERSE_REFS.yaml and TABLE_INDEX.yaml
- Regenerating derived asset files after schema updates
Related skills
| Skill |
When to use |
sap-service-layer-expert |
Read/write SAP B1 data via REST API |
sap-di-api-expert |
Read/write SAP B1 data via COM automation (DI API) |
sap-dtw-expert |
Bulk import/export via Data Transfer Workbench TSV files |
⚠️ DB column names ≠ COM property names
The DI API, Service Layer, and DTW all use COM property names (from the SAPbobsCOM type library). These are not always identical to the underlying HANA database column names used in raw SQL.
| Layer |
Authoritative source |
Name style |
| HANA SQL / Schema |
assets/schemas/<TABLE>.yaml (this skill) |
Raw DB column names |
| DI API |
assets/docs/class/ in sap-di-api-expert |
COM property names |
| Service Layer |
assets/spec/paths/ in sap-service-layer-expert |
COM property names |
| DTW |
Row 2 of TSV template in sap-dtw-expert |
COM property names |
Known divergences (non-exhaustive):
| DB column (HANA SQL) |
COM property name (DI API / Service Layer / DTW) |
OCRD.LicTradNum |
FederalTaxID |
OCRD.FatherCard |
Mother |
OINV.TradeDisc |
DiscountPercent |
OITM.InvntryUom |
InventoryUOM |
When writing HANA SQL, use DB column names from assets/schemas/. When working with DI API, Service Layer, or DTW, use COM property names from their respective reference docs.
1---2name: sap-schema-expert3description: SAP Business One 10.0 table schema reference for writing HANA SQL queries. Use when working with SAP B1 tables, looking up column names, understanding header/lines document structure, writing or reviewing HANA SQL that joins SAP B1 tables, or mapping a business concept to the correct SAP table name.4---56# SAP Schema Expert78## Quick start9101. Search `assets/TABLE_INDEX.yaml` for the table by name or description keyword.112. Read `assets/schemas/<TABLENAME>.yaml` for full field definitions.123. If the schema file is absent, look up the URL in `assets/TABLE_INDEX.yaml` and use `fetch_webpage`.1314## Workflows1516### Write a HANA SQL query17181. Find the header table in `assets/TABLE_INDEX.yaml` (e.g. `OINV` = A/R Invoice).192. Lines table = strip leading `O` + append `1` → `OINV` → `INV1`.203. Read field names from `assets/schemas/OINV.yaml` and `assets/schemas/INV1.yaml`.214. Build query — **double-quote all identifiers** (HANA is case-sensitive):2223```sql24SELECT h."DocEntry", h."CardCode", h."DocDate", h."DocTotal",25 l."LineNum", l."ItemCode", l."Dscription", l."Quantity", l."Price"26FROM "OINV" h27INNER JOIN "INV1" l ON l."DocEntry" = h."DocEntry"28WHERE h."DocStatus" = 'O' AND h."CANCELED" = 'N'29ORDER BY h."DocEntry", l."LineNum"30```3132### Map a business concept to a table33341. Grep `assets/TABLE_INDEX.yaml` for a keyword (e.g. `delivery`, `invoice`, `partner`).352. Cross-check the ObjType code via `assets/OBJ_TYPE_MAP.yaml` (common: `13`=OINV, `17`=ORDR, `15`=ODLN, `22`=OPOR).3637## Key encoded values3839| Column | Table | Values |40|---|---|---|41| `CardType` | `OCRD` | `C`=Customer, `S`=Supplier, `L`=Lead |42| `DocStatus` | Most doc tables | `O`=Open, `C`=Closed |43| `Canceled` | Most doc tables | `Y`=Yes, `N`=No |44| `TreeType` | `OITM` | `N`=No BOM, `S`=Sales BOM, `A`=Assembly |4546## Advanced features4748See [REFERENCE.md](REFERENCE.md) for:49- Full table naming conventions and all header/lines pairs50- Complete ObjType code table (17 common codes)51- Finding related tables via `REVERSE_REFS.yaml` and `TABLE_INDEX.yaml`52- Regenerating derived asset files after schema updates5354---5556## Related skills5758| Skill | When to use |59|---|---|60| `sap-service-layer-expert` | Read/write SAP B1 data via REST API |61| `sap-di-api-expert` | Read/write SAP B1 data via COM automation (DI API) |62| `sap-dtw-expert` | Bulk import/export via Data Transfer Workbench TSV files |6364### ⚠️ DB column names ≠ COM property names6566The **DI API, Service Layer, and DTW all use COM property names** (from the SAPbobsCOM type library). These are **not always identical** to the underlying HANA database column names used in raw SQL.6768| Layer | Authoritative source | Name style |69|---|---|---|70| **HANA SQL / Schema** | `assets/schemas/<TABLE>.yaml` (this skill) | Raw DB column names |71| **DI API** | `assets/docs/class/` in `sap-di-api-expert` | COM property names |72| **Service Layer** | `assets/spec/paths/` in `sap-service-layer-expert` | COM property names |73| **DTW** | Row 2 of TSV template in `sap-dtw-expert` | COM property names |7475Known divergences (non-exhaustive):7677| DB column (HANA SQL) | COM property name (DI API / Service Layer / DTW) |78|---|---|79| `OCRD.LicTradNum` | `FederalTaxID` |80| `OCRD.FatherCard` | `Mother` |81| `OINV.TradeDisc` | `DiscountPercent` |82| `OITM.InvntryUom` | `InventoryUOM` |8384When writing HANA SQL, use DB column names from `assets/schemas/`. When working with DI API, Service Layer, or DTW, use COM property names from their respective reference docs.85
kehwar