# SAP Equipment and Functional Location SQL Query Generator

> Source: https://netray.co/tools/sap-pm-equipment-and-floc-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generates a SQL query that lists equipment installed under a functional location, joining the master, time segment, location and text tables.

## Category

sap

## Erp

SAP ECC / S/4HANA

## Audience

- SAP PM consultant
- Data migration lead
- Reliability engineer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Client (MANDT)

**Key:** client

**Type:** text

**Default:** 100

#### Maintenance plant (ILOA-SWERK)

**Key:** plant

**Type:** text

**Default:** 1000

#### Functional location prefix

**Key:** floc_prefix

**Type:** text

**Default:** PLANT1-LINE2

#### Language key

**Key:** language

**Type:** text

**Default:** E

#### Equipment category (EQTYP)

**Key:** category

**Type:** text

**Default:** M

**Blocks:**

#### Equipment under a functional location

**Body:** SELECT e.EQUNR,
       k.EQKTX AS DESCRIPTION,
       e.EQTYP,
       e.EQART,
       l.TPLNR AS FUNCTIONAL_LOCATION,
       l.SWERK AS MAINT_PLANT
FROM EQUI e
INNER JOIN EQUZ z
        ON z.MANDT = e.MANDT
       AND z.EQUNR = e.EQUNR
       AND z.DATBI = '99991231'
INNER JOIN ILOA l
        ON l.MANDT = z.MANDT
       AND l.ILOAN = z.ILOAN
LEFT JOIN EQKT k
        ON k.MANDT = e.MANDT
       AND k.EQUNR = e.EQUNR
       AND k.SPRAS = '{{language}}'
WHERE e.MANDT = '{{client}}'
  AND e.EQTYP = '{{category}}'
  AND l.SWERK = '{{plant}}'
  AND l.TPLNR LIKE '{{floc_prefix}}%'
ORDER BY l.TPLNR, e.EQUNR;

#### Equipment with no functional location (data quality check)

**Body:** SELECT e.EQUNR, k.EQKTX AS DESCRIPTION, l.SWERK
FROM EQUI e
INNER JOIN EQUZ z
        ON z.MANDT = e.MANDT AND z.EQUNR = e.EQUNR AND z.DATBI = '99991231'
INNER JOIN ILOA l
        ON l.MANDT = z.MANDT AND l.ILOAN = z.ILOAN
LEFT JOIN EQKT k
        ON k.MANDT = e.MANDT AND k.EQUNR = e.EQUNR AND k.SPRAS = '{{language}}'
WHERE e.MANDT = '{{client}}'
  AND l.SWERK = '{{plant}}'
  AND (l.TPLNR IS NULL OR l.TPLNR = '')
ORDER BY e.EQUNR;

#### Count of equipment per functional location

**Body:** SELECT l.TPLNR, COUNT(*) AS EQUIPMENT_COUNT
FROM EQUZ z
INNER JOIN ILOA l
        ON l.MANDT = z.MANDT AND l.ILOAN = z.ILOAN
WHERE z.MANDT = '{{client}}'
  AND z.DATBI = '99991231'
  AND l.SWERK = '{{plant}}'
  AND l.TPLNR LIKE '{{floc_prefix}}%'
GROUP BY l.TPLNR
ORDER BY EQUIPMENT_COUNT DESC;

**Notes:** Tested logic for the classic PM data model (EQUI, EQUZ, ILOA, EQKT). Table names may need a schema prefix on HANA (for example SAPHANADB or SAPABAP1). Equipment numbers are stored with leading zeros in EQUNR. Run read-only, and verify field names in SE11 for your release.

## Content

**Intro:** Equipment lists by location are the first extract every PM data migration, audit and reliability project asks for. This generator builds the correct four-table join so you do not have to remember how equipment links to its functional location.

**Sections:**

#### How to use the generator

**Body:** Enter the client, maintenance plant, language and the prefix of the functional location structure you want, such as a plant and line code. Copy the SQL into your HANA studio, DBeaver, or an ABAP SQL console. The first query lists equipment with description and location, the second finds equipment with no location assigned, and the third counts equipment per location to expose empty or overloaded nodes.

#### Why the join looks the way it does

**Body:** Equipment is stored in EQUI, but its installation data sits in the time segment table EQUZ. That table can have several rows over time, so DATBI must be the open end date 99991231 to get the current one. The location and account assignment are not in EQUZ either, but in ILOA, reached through ILOAN. Finally, EQKT holds the description by language. Skipping the DATBI filter is the single most common reason for duplicated equipment rows.

**Bullets:**

- EQUI to EQUZ by EQUNR with DATBI = 99991231
- EQUZ to ILOA by ILOAN
- EQKT for the text, filtered by SPRAS

#### Using the results

**Body:** Use the missing-location query as a data quality gate before a migration or a preventive maintenance rollout. Equipment with no functional location cannot be reported by area and often has no cost center, which breaks cost reports. The counts query helps you spot structure indicators with a single piece of equipment, or nodes with hundreds that should probably be split. Export the results to Excel and agree clean-up owners by location.

**Cta:** If you want these checks run continuously with a plain-language question instead of hand-written SQL, we can show it working over your SAP data, read-only.

**Faq:**

#### How do I find the functional location of an equipment in SAP tables?

**Answer:** Join EQUI to EQUZ on EQUNR with the open DATBI, then join ILOA on ILOAN. The functional location is ILOA-TPLNR. The same ILOA table also gives the maintenance plant, cost center and room or location data.

#### Why do I get duplicate equipment rows?

**Answer:** EQUZ is time segmented, so each equipment can have several rows with different validity. Filter DATBI to 99991231 to get only the current row. Duplicates can also come from joining EQKT without filtering the language key.

#### Does this work on S/4HANA?

**Answer:** The on-premise PM data model keeps these tables, so the join generally works, subject to your schema name. Check for customer specific fields or changes in your release, and consider the standard CDS views if you extend the query a lot.

#### How do I include the superior equipment or hierarchy?

**Answer:** Superior equipment is stored in EQUZ in the HEQUI field, which you can join back to EQUI to walk up. For the functional location hierarchy, IFLOT holds the superior location, so a recursive query or the standard structure list transaction is easier.

## Search terms

sap equipment list sql, equi equz iloa join, sap equipment by functional location query, sap pm master data extract sql, hana sql equipment master, sap equipment table query, eqkt equi join, sap pm data migration extract

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json