Get all operations with final confirmation from AFRU

[Lingya]

Hi,

We try to find all operations that are tagged with a final confirmation in EAM app. We have data from AFRU. In this table, for same operations, there are multiple records with different RMHL, the records with maxium RMZHL is the latest one? but it might be either canceled or reversed. 

If we need to find the current final confirmation displayed in EAM, is the one with maxium RMZHL, excuding Reversed records (STOKZ = 'X') and Canceled records (STZHL >=1), noticed STZHL has many values, Null, 0, 1, 2, 3,...? the valid records is WHERE STOKZ <> 'X'  AND  (STZHL < 1)? Then final confirmation from the one with max RMZH? if the value is 'X', it is tagged with final confirmation in EAM workorder app?

e.g,

WITH FinalConfirmation AS (
SELECT
AFRU.* -- Include all AFRU fields
FROM AFRU
INNER JOIN (
SELECT
AUFPL, -- Operation plan number
APLZL, -- Sub-operation number
MAX(RMZHL) AS MaxCounter_RMZHL -- Maximum confirmation counter
FROM AFRU
WHERE STOKZ <> 'X' -- Exclude reversed confirmations
AND  (STZHL < 1)-- Exclude canceled confirmations
GROUP BY AUFPL, APLZL
) MaxRMZHL
ON AFRU.AUFPL = MaxRMZHL.AUFPL
AND AFRU.APLZL = MaxRMZHL.APLZL
AND AFRU.RMZHL = MaxRMZHL.MaxCounter_RMZHL
WHERE AFRU.AUERU = 'X' -- Only include final confirmations
);

Also, I’m wondering when operations will be added to the AFRU table. Some operations appear in the table with Processing% = 0, but others are not included at all? 

SAP EAM Work Order