POMEMBNO! 
Znotraj tega imaš HR Dashboard.dc.html kjer boš dobil izhodišče kako se zadeva izpisuje, kaj je namen, kaj kam paše … Podatkovni model.dc.html pa kako do tega prideti da bo to delovlo (katre podatke za to potrebuješ). Nujno je, da je zadeva v PHP 7.3 (SAP HANA baza), CSS in JavaScript. 

Znotraj tega imaš direktorij MILESTONES kjer vidiš primer osnove (dobre prakse) vezano na to kako se zadeva poveže na bazo, kako segmentiraš query-je, razdelaš index, css, js... 
Kar se tiče podatkovnega modela. Vsi dogodki so ovrednoteni v časovni enoti minuta, ne poznam kaj potrebuješ "cases" in kako se bo definiralo is_closed. običajno kadrovska služba do 6. v mesecu popravi vse zadeva za nazaj (za pretekli mesec). 
Vse ostale podatke ki jih imaš pa bi moral imeti. V kolikor potrebuješ samo ZAPOSLENE (brez ostalih oz. če boš to segmentiral je še pri osebah FROM "BXBI"."vMD HR Person V2"
...
WHERE 1=1 
AND "Employment Status ID" = '3' <- samo aktivni v času poizvedbe
AND "Person Group ID" = '1' <-- to pomeni samo "Zaposleni" brez Najetih, Pogodenih in študentov


Ostale podatke imaš v vseh poizvedbah tako da lahko narediš kombinacijo ... 


1. dogodki iz sistema, pazi PERNR = Person ID in pazi na LTRIM (da je povsod brez 0!), pazi tudi da so zadeve v minutah, datumi tudi v formatu 0700) + testni izvoz (predogled) podatkov
SELECT * FROM "SAPABAP1"."ZHR_SPICA_EVENTS"
MANDT;PERNR;EVENT_ID;START_DATE;START_TIME;START_TIMESTAMP;END_DATE;END_TIME;END_TIMESTAMP;TIME;UNIT;LOCATION;LOCATION_DESCRIPTION;PLACE;OBJECT
400;00000257;01;20200218;070000;20.200.218.070.000;20200218;150000;20.200.218.150.000;480;MIN;0000;;;
####

2.query za izpis skupine dogodkov (mapirna tabele) + testni izvoz (predogled) podatkov
SELECT * FROM "SAPABAP1"."ZHR_SPC_EVT_TYP" WHERE 1=1 AND EVENT_GROUP IN('DOP','NAD','BOL')
MANDT;EVENT_ID;LONG_NAME;SHORT_NAME;EVENT_GROUP
400;02;Prenos dopusta;PDO;DOP
###

3.omejitev samo na aktivne osebe? vprašanje kako primerjati podatke, ko se primerja za preteklo leto? prejšnji mesec in je oseba dala odpoved? FTE je običajno povsod 1 (polna zaposlitev), dodan tudi employment_type ki ga je možno tudi filtrirat, grupirat... + testni izvoz (predogled) podatkov
SELECT 
  "Person ID" 					as "employee_id"
, "Person Name" 				as "full_name"
, "Job Title" 					as "job_title"
, "Superior" 					as "manager_name"
, "Person Cost Center ID Name" 	as "cost_center"
, "Location" 					as "location"
, 1 							as "fte"
, "Person Group Name" 			as "employment_type"
, "Personnel Subarea ID Name" 	as "hr_subarea"

FROM "BXBI"."vMD HR Person V2"
WHERE 1=1 AND "Employment Status ID" = '3'

### 

4.Dopusti (razpolozljivo, planirano, uporabljeno, pravica) -> pazi pri osebi (PERNR da ko mapiraš je brez 0 (trenutno so še spredaj ničle) + testni izvoz (predogled) podatkov
WITH quota AS (
    SELECT
        "PERNR",
        "KTART",
        "BEGDA",
        "ENDDA",
        "DESTA",
        "DEEND",
        "ANZHL",
        "KVERB"
    FROM "SAPABAP1"."PA2006"
    WHERE 1=1 --AND LTRIM("PERNR", '0') = '2413'
      AND "KTART" = '20'
      AND CURRENT_DATE BETWEEN TO_DATE("BEGDA", 'YYYYMMDD')
                           AND TO_DATE("ENDDA", 'YYYYMMDD')
),
planned AS (
    SELECT
        q."PERNR",
        q."KTART",
        q."BEGDA",
        q."ENDDA",
        SUM(a."ABWTG") AS "PLANIRANO"
    FROM quota q
    LEFT JOIN "SAPABAP1"."PA2001" a
        ON a."PERNR" = q."PERNR"
       AND a."SUBTY" = '0100'
       AND COALESCE(a."SPRPS", '') <> 'X'
       AND TO_DATE(a."BEGDA", 'YYYYMMDD') > CURRENT_DATE
       AND TO_DATE(a."BEGDA", 'YYYYMMDD') BETWEEN TO_DATE(q."DESTA", 'YYYYMMDD')
                                              AND TO_DATE(q."DEEND", 'YYYYMMDD')
    GROUP BY
        q."PERNR",
        q."KTART",
        q."BEGDA",
        q."ENDDA"
)
SELECT
    q."PERNR",
    q."KTART",
    TO_DATE(q."DESTA", 'YYYYMMDD') AS "OD",
    TO_DATE(q."DEEND", 'YYYYMMDD') AS "DO",
    q."ANZHL" - q."KVERB" AS "RAZPOLOZLJIVO",
    COALESCE(p."PLANIRANO", 0) AS "PLANIRANO",
    q."KVERB" - COALESCE(p."PLANIRANO", 0) AS "UPORABLJENO",
    q."ANZHL" AS "PRAVICA"
FROM quota q
LEFT JOIN planned p
    ON p."PERNR" = q."PERNR"
   AND p."KTART" = q."KTART"
   AND p."BEGDA" = q."BEGDA"
   AND p."ENDDA" = q."ENDDA";

PERNR;KTART;OD;DO;RAZPOLOZLJIVO;PLANIRANO;UPORABLJENO;PRAVICA
00003503;20;1.1.2026;30.6.2027;15;0;0;15

5.Organizacijska struktura (nadrejeni ipd) vsi nivoji (en podatek za vse osebe) ki naj bi bile aktivne.  + testni izvoz (predogled) podatkov
WITH
params AS (
    SELECT CAST('400' AS NVARCHAR(3)) AS MANDT,
           TO_DATS(CURRENT_DATE)      AS KEYDATE
    FROM DUMMY
),

/* 1. VSE AKTIVNE OSEBE */
seed_person AS (
    SELECT DISTINCT CAST(h.OBJID AS NVARCHAR(20)) AS PERNR
    FROM "SAPABAP1"."HRP1001" h
    JOIN params p
      ON h.MANDT = p.MANDT
    WHERE h.OTYPE = 'P'
      AND h.ISTAT = '1'
      AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

/* 2. OSEBA -> POZICIJA -> ORG. ENOTA */
seed AS (
    SELECT PERNR, POSITION_ID, ORGEH
    FROM (
        SELECT sp.PERNR,
               CAST(rel_s.SOBID AS NVARCHAR(20)) AS POSITION_ID,
               CAST(rel_o.SOBID AS NVARCHAR(20)) AS ORGEH,
               ROW_NUMBER() OVER (
                   PARTITION BY sp.PERNR
                   ORDER BY rel_s.SOBID
               ) AS rn
        FROM seed_person sp
        JOIN params p
          ON 1 = 1

        JOIN "SAPABAP1"."HRP1001" rel_s
          ON rel_s.MANDT = p.MANDT
         AND rel_s.OTYPE = 'P'
         AND CAST(rel_s.OBJID AS NVARCHAR(20)) = sp.PERNR
         AND rel_s.RSIGN = 'B'
         AND rel_s.RELAT = '008'
         AND rel_s.SCLAS = 'S'
         AND rel_s.ISTAT = '1'
         AND p.KEYDATE BETWEEN rel_s.BEGDA AND rel_s.ENDDA

        JOIN "SAPABAP1"."HRP1001" rel_o
          ON rel_o.MANDT = p.MANDT
         AND rel_o.OTYPE = 'S'
         AND CAST(rel_o.OBJID AS NVARCHAR(20)) = CAST(rel_s.SOBID AS NVARCHAR(20))
         AND rel_o.RSIGN = 'A'
         AND rel_o.RELAT = '003'
         AND rel_o.SCLAS = 'O'
         AND rel_o.ISTAT = '1'
         AND p.KEYDATE BETWEEN rel_o.BEGDA AND rel_o.ENDDA
    )
    WHERE rn = 1
),

/* 3. UNIKATNE ORG. ENOTE, DA HIERARHIJE NE RAČUNAMO ZA VSAKO OSEBO POSEBEJ */
unique_org AS (
    SELECT DISTINCT ORGEH
    FROM seed
    WHERE ORGEH IS NOT NULL
),

/* 4. ORG. POT NAVZGOR: LB=0 JE ORG. ENOTA OSEBE, VIŠJI LB SO NADREJENE ENOTE */
l0 AS (
    SELECT ORGEH AS BASE_ORGEH,
           0 AS LB,
           ORGEH,
           CAST(NULL AS NVARCHAR(20)) AS CHILD_ORGEH
    FROM unique_org
),

l1 AS (
    SELECT l0.BASE_ORGEH,
           1 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l0.ORGEH AS CHILD_ORGEH
    FROM l0
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l0.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l2 AS (
    SELECT l1.BASE_ORGEH,
           2 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l1.ORGEH AS CHILD_ORGEH
    FROM l1
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l1.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l3 AS (
    SELECT l2.BASE_ORGEH,
           3 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l2.ORGEH AS CHILD_ORGEH
    FROM l2
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l2.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l4 AS (
    SELECT l3.BASE_ORGEH,
           4 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l3.ORGEH AS CHILD_ORGEH
    FROM l3
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l3.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l5 AS (
    SELECT l4.BASE_ORGEH,
           5 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l4.ORGEH AS CHILD_ORGEH
    FROM l4
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l4.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l6 AS (
    SELECT l5.BASE_ORGEH,
           6 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l5.ORGEH AS CHILD_ORGEH
    FROM l5
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l5.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l7 AS (
    SELECT l6.BASE_ORGEH,
           7 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l6.ORGEH AS CHILD_ORGEH
    FROM l6
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l6.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l8 AS (
    SELECT l7.BASE_ORGEH,
           8 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l7.ORGEH AS CHILD_ORGEH
    FROM l7
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l7.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l9 AS (
    SELECT l8.BASE_ORGEH,
           9 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l8.ORGEH AS CHILD_ORGEH
    FROM l8
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l8.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l10 AS (
    SELECT l9.BASE_ORGEH,
           10 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l9.ORGEH AS CHILD_ORGEH
    FROM l9
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l9.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l11 AS (
    SELECT l10.BASE_ORGEH,
           11 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l10.ORGEH AS CHILD_ORGEH
    FROM l10
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l10.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l12 AS (
    SELECT l11.BASE_ORGEH,
           12 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l11.ORGEH AS CHILD_ORGEH
    FROM l11
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l11.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l13 AS (
    SELECT l12.BASE_ORGEH,
           13 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l12.ORGEH AS CHILD_ORGEH
    FROM l12
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l12.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l14 AS (
    SELECT l13.BASE_ORGEH,
           14 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l13.ORGEH AS CHILD_ORGEH
    FROM l13
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l13.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

l15 AS (
    SELECT l14.BASE_ORGEH,
           15 AS LB,
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           l14.ORGEH AS CHILD_ORGEH
    FROM l14
    JOIN params p ON 1 = 1
    JOIN "SAPABAP1"."HRP1001" h
      ON h.MANDT = p.MANDT
     AND h.OTYPE = 'O'
     AND CAST(h.OBJID AS NVARCHAR(20)) = l14.ORGEH
     AND h.RSIGN = 'A'
     AND h.RELAT = '002'
     AND h.SCLAS = 'O'
     AND h.ISTAT = '1'
     AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

all_levels_raw AS (
    SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l0
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l1
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l2
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l3
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l4
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l5
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l6
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l7
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l8
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l9
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l10
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l11
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l12
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l13
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l14
    UNION ALL SELECT BASE_ORGEH, LB, ORGEH, CHILD_ORGEH FROM l15
),

org_path AS (
    SELECT BASE_ORGEH,
           LB,
           MIN(ORGEH) AS ORGEH,
           MIN(CHILD_ORGEH) AS CHILD_ORGEH
    FROM all_levels_raw
    WHERE ORGEH IS NOT NULL
    GROUP BY BASE_ORGEH, LB
),

/* 5. SLOVARJI */
org_name AS (
    SELECT OBJID, STEXT
    FROM (
        SELECT CAST(o.OBJID AS NVARCHAR(20)) AS OBJID,
               o.STEXT,
               ROW_NUMBER() OVER (
                   PARTITION BY o.OBJID
                   ORDER BY o.BEGDA DESC
               ) AS rn
        FROM "SAPABAP1"."HRP1000" o
        JOIN params p
          ON p.MANDT = o.MANDT
        WHERE o.OTYPE = 'O'
          AND o.ISTAT = '1'
          AND p.KEYDATE BETWEEN o.BEGDA AND o.ENDDA
    )
    WHERE rn = 1
),

pos_name AS (
    SELECT OBJID, STEXT
    FROM (
        SELECT CAST(o.OBJID AS NVARCHAR(20)) AS OBJID,
               o.STEXT,
               ROW_NUMBER() OVER (
                   PARTITION BY o.OBJID
                   ORDER BY o.BEGDA DESC
               ) AS rn
        FROM "SAPABAP1"."HRP1000" o
        JOIN params p
          ON p.MANDT = o.MANDT
        WHERE o.OTYPE = 'S'
          AND o.ISTAT = '1'
          AND p.KEYDATE BETWEEN o.BEGDA AND o.ENDDA
    )
    WHERE rn = 1
),

pa0002_v AS (
    SELECT PERNR, VORNA, NACHN
    FROM (
        SELECT CAST(x.PERNR AS NVARCHAR(20)) AS PERNR,
               x.VORNA,
               x.NACHN,
               ROW_NUMBER() OVER (
                   PARTITION BY x.PERNR
                   ORDER BY x.BEGDA DESC
               ) AS rn
        FROM "SAPABAP1"."PA0002" x
        JOIN params p
          ON p.MANDT = x.MANDT
        WHERE p.KEYDATE BETWEEN x.BEGDA AND x.ENDDA
    )
    WHERE rn = 1
),

pa0001_v AS (
    SELECT PERNR, KOKRS, KOSTL, PERSG, PERSK
    FROM (
        SELECT CAST(x.PERNR AS NVARCHAR(20)) AS PERNR,
               x.KOKRS,
               x.KOSTL,
               x.PERSG,
               x.PERSK,
               ROW_NUMBER() OVER (
                   PARTITION BY x.PERNR
                   ORDER BY x.BEGDA DESC
               ) AS rn
        FROM "SAPABAP1"."PA0001" x
        JOIN params p
          ON p.MANDT = x.MANDT
        WHERE p.KEYDATE BETWEEN x.BEGDA AND x.ENDDA
    )
    WHERE rn = 1
),

cost_center_name AS (
    SELECT KOKRS, KOSTL, KTEXT, LTEXT
    FROM (
        SELECT c.KOKRS,
               c.KOSTL,
               c.KTEXT,
               c.LTEXT,
               ROW_NUMBER() OVER (
                   PARTITION BY c.KOKRS, c.KOSTL
                   ORDER BY
                       CASE
                           WHEN c.SPRAS = '5' THEN 1
                           WHEN c.SPRAS = 'E' THEN 2
                           WHEN c.SPRAS = 'D' THEN 3
                           ELSE 9
                       END,
                       c.DATBI DESC
               ) AS rn
        FROM "SAPABAP1"."CSKT" c
        JOIN params p
          ON p.MANDT = c.MANDT
        WHERE c.SPRAS IN ('5', 'E', 'D')
          AND c.DATBI >= p.KEYDATE
    )
    WHERE rn = 1
),

person AS (
    SELECT s.PERNR,
           s.POSITION_ID,
           s.ORGEH,
           posn.STEXT AS POSITION_NAME,
           p2.VORNA AS FIRST_NAME,
           p2.NACHN AS LAST_NAME,
           pa1.KOKRS AS CONTROLLING_AREA,
           pa1.KOSTL AS COST_CENTER,
           ccn.KTEXT AS COST_CENTER_NAME,
           ccn.LTEXT AS COST_CENTER_LONG_NAME,
           pa1.PERSG AS EMPLOYEE_GROUP,
           pa1.PERSK AS EMPLOYEE_SUBGROUP
    FROM seed s
    LEFT JOIN pos_name posn
      ON posn.OBJID = s.POSITION_ID
    LEFT JOIN pa0002_v p2
      ON p2.PERNR = s.PERNR
    LEFT JOIN pa0001_v pa1
      ON pa1.PERNR = s.PERNR
    LEFT JOIN cost_center_name ccn
      ON ccn.KOKRS = pa1.KOKRS
     AND ccn.KOSTL = pa1.KOSTL
),

chain AS (
    SELECT op.BASE_ORGEH,
           op.LB,
           op.ORGEH,
           nm.STEXT AS ORG_NAME,
           op.CHILD_ORGEH
    FROM org_path op
    LEFT JOIN org_name nm
      ON nm.OBJID = op.ORGEH
),

/* 6. MANAGER - POZICIJSKO, NATO FALLBACK PREK VODJE ORG. ENOTE */
position_holder AS (
    SELECT DISTINCT
           CAST(h.OBJID AS NVARCHAR(20)) AS POSITION_ID,
           CAST(h.SOBID AS NVARCHAR(20)) AS PERNR
    FROM "SAPABAP1"."HRP1001" h
    JOIN params p
      ON p.MANDT = h.MANDT
    WHERE h.OTYPE = 'S'
      AND h.RSIGN = 'A'
      AND h.RELAT = '008'
      AND h.SCLAS = 'P'
      AND h.ISTAT = '1'
      AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA

    UNION

    SELECT DISTINCT
           CAST(h.SOBID AS NVARCHAR(20)) AS POSITION_ID,
           CAST(h.OBJID AS NVARCHAR(20)) AS PERNR
    FROM "SAPABAP1"."HRP1001" h
    JOIN params p
      ON p.MANDT = h.MANDT
    WHERE h.OTYPE = 'P'
      AND h.RSIGN = 'B'
      AND h.RELAT = '008'
      AND h.SCLAS = 'S'
      AND h.ISTAT = '1'
      AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

position_relation AS (
    SELECT DISTINCT
           CAST(h.OBJID AS NVARCHAR(20)) AS CHILD_POSITION_ID,
           CAST(h.SOBID AS NVARCHAR(20)) AS PARENT_POSITION_ID
    FROM "SAPABAP1"."HRP1001" h
    JOIN params p
      ON p.MANDT = h.MANDT
    WHERE h.OTYPE = 'S'
      AND h.RSIGN = 'A'
      AND h.RELAT = '002'
      AND h.SCLAS = 'S'
      AND h.ISTAT = '1'
      AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA

    UNION

    SELECT DISTINCT
           CAST(h.SOBID AS NVARCHAR(20)) AS CHILD_POSITION_ID,
           CAST(h.OBJID AS NVARCHAR(20)) AS PARENT_POSITION_ID
    FROM "SAPABAP1"."HRP1001" h
    JOIN params p
      ON p.MANDT = h.MANDT
    WHERE h.OTYPE = 'S'
      AND h.RSIGN = 'B'
      AND h.RELAT = '002'
      AND h.SCLAS = 'S'
      AND h.ISTAT = '1'
      AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

unique_pos AS (
    SELECT DISTINCT POSITION_ID
    FROM seed
    WHERE POSITION_ID IS NOT NULL
),

sp0 AS (
    SELECT POSITION_ID AS BASE_POS,
           0 AS LVL,
           POSITION_ID
    FROM unique_pos
),

sp1 AS (
    SELECT sp0.BASE_POS,
           1 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp0
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp0.POSITION_ID
),

sp2 AS (
    SELECT sp1.BASE_POS,
           2 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp1
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp1.POSITION_ID
),

sp3 AS (
    SELECT sp2.BASE_POS,
           3 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp2
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp2.POSITION_ID
),

sp4 AS (
    SELECT sp3.BASE_POS,
           4 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp3
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp3.POSITION_ID
),

sp5 AS (
    SELECT sp4.BASE_POS,
           5 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp4
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp4.POSITION_ID
),

sp6 AS (
    SELECT sp5.BASE_POS,
           6 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp5
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp5.POSITION_ID
),

sp7 AS (
    SELECT sp6.BASE_POS,
           7 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp6
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp6.POSITION_ID
),

sp8 AS (
    SELECT sp7.BASE_POS,
           8 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp7
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp7.POSITION_ID
),

sp9 AS (
    SELECT sp8.BASE_POS,
           9 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp8
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp8.POSITION_ID
),

sp10 AS (
    SELECT sp9.BASE_POS,
           10 AS LVL,
           pr.PARENT_POSITION_ID AS POSITION_ID
    FROM sp9
    JOIN position_relation pr
      ON pr.CHILD_POSITION_ID = sp9.POSITION_ID
),

superior_positions AS (
    SELECT BASE_POS, LVL, POSITION_ID FROM sp1
    UNION ALL SELECT BASE_POS, LVL, POSITION_ID FROM sp2
    UNION ALL SELECT BASE_POS, LVL, POSITION_ID FROM sp3
    UNION ALL SELECT BASE_POS, LVL, POSITION_ID FROM sp4
    UNION ALL SELECT BASE_POS, LVL, POSITION_ID FROM sp5
    UNION ALL SELECT BASE_POS, LVL, POSITION_ID FROM sp6
    UNION ALL SELECT BASE_POS, LVL, POSITION_ID FROM sp7
    UNION ALL SELECT BASE_POS, LVL, POSITION_ID FROM sp8
    UNION ALL SELECT BASE_POS, LVL, POSITION_ID FROM sp9
    UNION ALL SELECT BASE_POS, LVL, POSITION_ID FROM sp10
),

direct_manager_candidates AS (
    SELECT sp.BASE_POS,
           sp.LVL AS MANAGER_LEVEL,
           sp.POSITION_ID AS MANAGER_POSITION_ID,
           ph.PERNR AS MANAGER_PERNR,
           CAST('POSITION_HIERARCHY' AS NVARCHAR(30)) AS MANAGER_SOURCE,
           1 AS SOURCE_PRIORITY
    FROM superior_positions sp
    JOIN position_holder ph
      ON ph.POSITION_ID = sp.POSITION_ID
),

org_chief_position AS (
    SELECT DISTINCT
           CAST(h.OBJID AS NVARCHAR(20)) AS ORGEH,
           CAST(h.SOBID AS NVARCHAR(20)) AS CHIEF_POSITION_ID
    FROM "SAPABAP1"."HRP1001" h
    JOIN params p
      ON p.MANDT = h.MANDT
    WHERE h.OTYPE = 'O'
      AND h.RSIGN = 'B'
      AND h.RELAT = '012'
      AND h.SCLAS = 'S'
      AND h.ISTAT = '1'
      AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA

    UNION

    SELECT DISTINCT
           CAST(h.SOBID AS NVARCHAR(20)) AS ORGEH,
           CAST(h.OBJID AS NVARCHAR(20)) AS CHIEF_POSITION_ID
    FROM "SAPABAP1"."HRP1001" h
    JOIN params p
      ON p.MANDT = h.MANDT
    WHERE h.OTYPE = 'S'
      AND h.RSIGN = 'A'
      AND h.RELAT = '012'
      AND h.SCLAS = 'O'
      AND h.ISTAT = '1'
      AND p.KEYDATE BETWEEN h.BEGDA AND h.ENDDA
),

org_manager_candidates AS (
    SELECT ch.BASE_ORGEH,
           ch.LB AS MANAGER_LEVEL,
           ocp.CHIEF_POSITION_ID AS MANAGER_POSITION_ID,
           ph.PERNR AS MANAGER_PERNR,
           CAST('ORG_CHIEF' AS NVARCHAR(30)) AS MANAGER_SOURCE,
           2 AS SOURCE_PRIORITY
    FROM chain ch
    JOIN org_chief_position ocp
      ON ocp.ORGEH = ch.ORGEH
    JOIN position_holder ph
      ON ph.POSITION_ID = ocp.CHIEF_POSITION_ID
),

all_manager_candidates AS (
    SELECT s.PERNR,
           dmc.MANAGER_LEVEL,
           dmc.MANAGER_POSITION_ID,
           dmc.MANAGER_PERNR,
           dmc.MANAGER_SOURCE,
           dmc.SOURCE_PRIORITY
    FROM seed s
    JOIN direct_manager_candidates dmc
      ON dmc.BASE_POS = s.POSITION_ID
    WHERE s.PERNR <> dmc.MANAGER_PERNR

    UNION ALL

    SELECT s.PERNR,
           omc.MANAGER_LEVEL,
           omc.MANAGER_POSITION_ID,
           omc.MANAGER_PERNR,
           omc.MANAGER_SOURCE,
           omc.SOURCE_PRIORITY
    FROM seed s
    JOIN org_manager_candidates omc
      ON omc.BASE_ORGEH = s.ORGEH
    WHERE s.PERNR <> omc.MANAGER_PERNR
),

manager AS (
    SELECT PERNR,
           MANAGER_PERNR,
           MANAGER_FIRST_NAME,
           MANAGER_LAST_NAME,
           MANAGER_POSITION_ID,
           MANAGER_POSITION_NAME,
           MANAGER_SOURCE,
           MANAGER_LEVEL
    FROM (
        SELECT amc.PERNR,
               amc.MANAGER_PERNR,
               p2.VORNA AS MANAGER_FIRST_NAME,
               p2.NACHN AS MANAGER_LAST_NAME,
               amc.MANAGER_POSITION_ID,
               pn.STEXT AS MANAGER_POSITION_NAME,
               amc.MANAGER_SOURCE,
               amc.MANAGER_LEVEL,
               ROW_NUMBER() OVER (
                   PARTITION BY amc.PERNR
                   ORDER BY
                       amc.SOURCE_PRIORITY ASC,
                       amc.MANAGER_LEVEL ASC,
                       amc.MANAGER_POSITION_ID ASC,
                       amc.MANAGER_PERNR ASC
               ) AS rn
        FROM all_manager_candidates amc
        LEFT JOIN pa0002_v p2
          ON p2.PERNR = amc.MANAGER_PERNR
        LEFT JOIN pos_name pn
          ON pn.OBJID = amc.MANAGER_POSITION_ID
    )
    WHERE rn = 1
),

/* 7. ABSOLUTNI ORG. NIVOJI OD VRHA NAVZDOL */
org_depth AS (
    SELECT BASE_ORGEH,
           MAX(LB) AS MAX_LB,
           MAX(LB) + 1 AS ORG_DEPTH
    FROM chain
    GROUP BY BASE_ORGEH
),

chain_abs AS (
    SELECT ch.BASE_ORGEH,
           ch.LB,
           od.MAX_LB,
           od.ORG_DEPTH,
           od.MAX_LB - ch.LB + 1 AS ABS_LEVEL,
           ch.ORGEH,
           ch.ORG_NAME,
           ch.CHILD_ORGEH
    FROM chain ch
    JOIN org_depth od
      ON od.BASE_ORGEH = ch.BASE_ORGEH
),

flat AS (
    SELECT BASE_ORGEH,
           MAX(ORG_DEPTH) AS ORG_DEPTH,

           MAX(CASE WHEN ABS_LEVEL = 1 THEN ORGEH END) AS LEVEL1_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 1 THEN ORG_NAME END) AS LEVEL1_NAME,

           MAX(CASE WHEN ABS_LEVEL = 2 THEN ORGEH END) AS LEVEL2_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 2 THEN ORG_NAME END) AS LEVEL2_NAME,

           MAX(CASE WHEN ABS_LEVEL = 3 THEN ORGEH END) AS LEVEL3_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 3 THEN ORG_NAME END) AS LEVEL3_NAME,

           MAX(CASE WHEN ABS_LEVEL = 4 THEN ORGEH END) AS LEVEL4_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 4 THEN ORG_NAME END) AS LEVEL4_NAME,

           MAX(CASE WHEN ABS_LEVEL = 5 THEN ORGEH END) AS LEVEL5_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 5 THEN ORG_NAME END) AS LEVEL5_NAME,

           MAX(CASE WHEN ABS_LEVEL = 6 THEN ORGEH END) AS LEVEL6_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 6 THEN ORG_NAME END) AS LEVEL6_NAME,

           MAX(CASE WHEN ABS_LEVEL = 7 THEN ORGEH END) AS LEVEL7_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 7 THEN ORG_NAME END) AS LEVEL7_NAME,

           MAX(CASE WHEN ABS_LEVEL = 8 THEN ORGEH END) AS LEVEL8_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 8 THEN ORG_NAME END) AS LEVEL8_NAME,

           MAX(CASE WHEN ABS_LEVEL = 9 THEN ORGEH END) AS LEVEL9_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 9 THEN ORG_NAME END) AS LEVEL9_NAME,

           MAX(CASE WHEN ABS_LEVEL = 10 THEN ORGEH END) AS LEVEL10_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 10 THEN ORG_NAME END) AS LEVEL10_NAME,

           MAX(CASE WHEN ABS_LEVEL = 11 THEN ORGEH END) AS LEVEL11_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 11 THEN ORG_NAME END) AS LEVEL11_NAME,

           MAX(CASE WHEN ABS_LEVEL = 12 THEN ORGEH END) AS LEVEL12_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 12 THEN ORG_NAME END) AS LEVEL12_NAME,

           MAX(CASE WHEN ABS_LEVEL = 13 THEN ORGEH END) AS LEVEL13_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 13 THEN ORG_NAME END) AS LEVEL13_NAME,

           MAX(CASE WHEN ABS_LEVEL = 14 THEN ORGEH END) AS LEVEL14_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 14 THEN ORG_NAME END) AS LEVEL14_NAME,

           MAX(CASE WHEN ABS_LEVEL = 15 THEN ORGEH END) AS LEVEL15_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 15 THEN ORG_NAME END) AS LEVEL15_NAME,

           MAX(CASE WHEN ABS_LEVEL = 16 THEN ORGEH END) AS LEVEL16_ORGEH,
           MAX(CASE WHEN ABS_LEVEL = 16 THEN ORG_NAME END) AS LEVEL16_NAME
    FROM chain_abs
    GROUP BY BASE_ORGEH
),

own_org AS (
    SELECT BASE_ORGEH,
           ORGEH,
           ORG_NAME
    FROM chain
    WHERE LB = 0
)

/* 8. FINALNI IZPIS */
SELECT
    LTRIM(per.PERNR, '0') AS PERNR,
    per.FIRST_NAME,
    per.LAST_NAME,

    LTRIM(mgr.MANAGER_PERNR, '0') AS MANAGER_PERNR,
    mgr.MANAGER_FIRST_NAME,
    mgr.MANAGER_LAST_NAME,
    mgr.MANAGER_POSITION_ID,
    mgr.MANAGER_POSITION_NAME,
    mgr.MANAGER_SOURCE,
    mgr.MANAGER_LEVEL,

    per.CONTROLLING_AREA,
    per.COST_CENTER,
    per.COST_CENTER_NAME,
    per.COST_CENTER_LONG_NAME,

    per.EMPLOYEE_GROUP,
    per.EMPLOYEE_SUBGROUP,

    per.POSITION_ID,
    per.POSITION_NAME,

    oo.ORGEH AS ORGEH,
    oo.ORG_NAME AS ORG_UNIT_NAME,

    f.ORG_DEPTH,

    f.LEVEL1_ORGEH,
    f.LEVEL1_NAME,
    f.LEVEL2_ORGEH,
    f.LEVEL2_NAME,
    f.LEVEL3_ORGEH,
    f.LEVEL3_NAME,
    f.LEVEL4_ORGEH,
    f.LEVEL4_NAME,
    f.LEVEL5_ORGEH,
    f.LEVEL5_NAME,
    f.LEVEL6_ORGEH,
    f.LEVEL6_NAME,
    f.LEVEL7_ORGEH,
    f.LEVEL7_NAME,
    f.LEVEL8_ORGEH,
    f.LEVEL8_NAME,
    f.LEVEL9_ORGEH,
    f.LEVEL9_NAME,
    f.LEVEL10_ORGEH,
    f.LEVEL10_NAME,
    f.LEVEL11_ORGEH,
    f.LEVEL11_NAME,
    f.LEVEL12_ORGEH,
    f.LEVEL12_NAME,
    f.LEVEL13_ORGEH,
    f.LEVEL13_NAME,
    f.LEVEL14_ORGEH,
    f.LEVEL14_NAME,
    f.LEVEL15_ORGEH,
    f.LEVEL15_NAME,
    f.LEVEL16_ORGEH,
    f.LEVEL16_NAME

FROM person per
LEFT JOIN flat f
       ON f.BASE_ORGEH = per.ORGEH
LEFT JOIN own_org oo
       ON oo.BASE_ORGEH = per.ORGEH
LEFT JOIN manager mgr
       ON mgr.PERNR = per.PERNR;
	   
PERNR;FIRST_NAME;LAST_NAME;MANAGER_PERNR;MANAGER_FIRST_NAME;MANAGER_LAST_NAME;MANAGER_POSITION_ID;MANAGER_POSITION_NAME;MANAGER_SOURCE;MANAGER_LEVEL;CONTROLLING_AREA;COST_CENTER;COST_CENTER_NAME;COST_CENTER_LONG_NAME;EMPLOYEE_GROUP;EMPLOYEE_SUBGROUP;POSITION_ID;POSITION_NAME;ORGEH;ORG_UNIT_NAME;ORG_DEPTH;LEVEL1_ORGEH;LEVEL1_NAME;LEVEL2_ORGEH;LEVEL2_NAME;LEVEL3_ORGEH;LEVEL3_NAME;LEVEL4_ORGEH;LEVEL4_NAME;LEVEL5_ORGEH;LEVEL5_NAME;LEVEL6_ORGEH;LEVEL6_NAME;LEVEL7_ORGEH;LEVEL7_NAME;LEVEL8_ORGEH;LEVEL8_NAME;LEVEL9_ORGEH;LEVEL9_NAME;LEVEL10_ORGEH;LEVEL10_NAME;LEVEL11_ORGEH;LEVEL11_NAME;LEVEL12_ORGEH;LEVEL12_NAME;LEVEL13_ORGEH;LEVEL13_NAME;LEVEL14_ORGEH;LEVEL14_NAME;LEVEL15_ORGEH;LEVEL15_NAME;LEVEL16_ORGEH;LEVEL16_NAME
469;Gregor;Peršin;2354;Jan;Berčič;50004901;Vodja obrata;ORG_CHIEF;2;1000;0000021403;Elektropoliranje;Elektropoliranje;1;OP;50000473;Obdelovalec površin;50001783;Pralnica;9;50000000;Podjetje;50000001;BRINOX Innovations;50000016;BRINOX - SLO;50001562;Poslovodstvo;50003022;Tehnične operacije;50000011;Proizvodnja;50004748;Obrat Sora;50000058;Proizvodnja procesne opreme - Sora;50001783;Pralnica;?;?;?;?;?;?;?;?;?;?;?;?;?;?	   


