In PeopleSoft, a Student's Current Program Is Not a Column

Query your PeopleSoft Campus Solutions data with AI against a replicated database, ask how many students are in each academic program, and the count comes back too high. Every change a student's program ever went through is still a row in PS_ACAD_PROG: admitted, matriculated, plan change, leave of absence, return. Each row carries an EFFDT and an EFFSEQ, and between them those two columns decide which row is true today. Nothing in the replicated table says so. The tool your institution reported from applied that rule by default, and the default belongs to the tool.

Before you start

• A Campus Solutions database already replicated into a warehouse. No application connector exists. Fivetran's own PeopleSoft page says it pulls "directly from the source database" and recommends "our Oracle connector or SQL Server connector", depending on how your institution hosts PeopleSoft. Db2 is the third supported host. Airbyte has no PeopleSoft source at all.
• Read access to the PS_ tables in the application schema. That prefix is PeopleSoft's own, applied at table-create time, so it doesn't vary by extract tool.
• Your own estate, because no sandbox exists. Oracle distributes PeopleSoft images to licensed customers through My Oracle Support. Nobody can follow this post from nothing, so every query below runs against your database.

The question

How many students are in each academic program?

It's the census question, it gets asked every term, and a university answers it to its board, its accreditor and its state. The columns involved look obvious: PS_ACAD_PROG holds academic program rows, EMPLID identifies the person, ACAD_PROG names the program.

What breaks

The obvious query:

SELECT acad_prog, COUNT(DISTINCT emplid) AS students
FROM   ps_acad_prog
GROUP  BY acad_prog
ORDER  BY students DESC;

That counts program actions and labels them students. A student admitted to one program, who then changed plan twice and took a leave of absence, has a row for each of those events. Where those rows name different programs, she's counted inside each of them, at the same time.

The second attempt is the one that matters, because it looks careful and still gets the wrong answer:

SELECT p.acad_prog, COUNT(DISTINCT p.emplid) AS students
FROM   ps_acad_prog p
JOIN  (SELECT emplid, stdnt_car_nbr, MAX(effdt) AS effdt
       FROM   ps_acad_prog
       WHERE  effdt <= CURRENT_DATE
       GROUP  BY emplid, stdnt_car_nbr) cur
  ON   p.emplid = cur.emplid
 AND   p.stdnt_car_nbr = cur.stdnt_car_nbr
 AND   p.effdt = cur.effdt
GROUP  BY p.acad_prog;

Taking the latest effective date per student is the right instinct and it doesn't finish the job. MAX(effdt) can return more than one row, and the schema has a column whose entire purpose is to make that happen.

Why it breaks

A program row is a program action

Oracle names the structure and it isn't a per-student record. From the Student Records 9.0 PeopleBook:

"The system stores the student's academic program and plan data on rows in the academic program table (ACAD_PROG). Collectively, all of a student's rows in the ACAD_PROG table are called the student's program stack."

A stack, and all of it stays. Oracle is more precise still in the Campus Solutions Warehouse 9.1 PeopleBook, describing F_ACAD_PROG_DTL, the fact table whose "PeopleSoft Source" column reads PS_ACAD_PROG:

"Contains information about an individual program action entry for a particular student for a given academic program. This fact table contains all students that enrolled, matriculated, withdrew, or completed an academic career/program at an institution. There is one row of data per student (person) surrogate ID, student career number, program surrogate ID, effective date/sequence and program action surrogate ID. The table is not keyed by term."

One row per program action entry, dated. Enrolled, matriculated, withdrew and completed are all in there together, and Oracle says so in the same breath.

EFFSEQ exists so that one date can hold two rows

This is the part that defeats MAX(effdt), and PeopleTools states it plainly in the Application Designer Developer's Guide:

"If EFFSEQ is paired with EFFDT, it enables you to enter more than one row with the same effective date. You assign a unique sequence number to each row that has the same effective date."

So EFFSEQ is not redundant precision. It's the mechanism for recording two changes on one day, which is ordinary during registration and at term rollover. A query that resolves to the maximum effective date and stops will return both of them, and count that student twice.

Oracle's own batch processing shows the full rule. The Term Activation documentation says:

"When a student has multiple eligible programs, the process selects the primary academic program by taking the program with the lowest STDNT_CAR_NBR and the highest EFFSEQ."

Two columns, in a specific order, and a third one to partition by. The current row is the highest EFFSEQ inside the highest EFFDT that isn't in the future, per EMPLID and STDNT_CAR_NBR.

Nobody inside PeopleSoft ever had to know that

Here's the part that explains why this reaches a warehouse unnoticed. PeopleSoft Query collapses the stack for you, and it does it without being asked. From PeopleTools 8.63, Specifying Effective Date Criteria:

"When you choose the record that has EFFDT as a key field, Query Manager automatically creates default criteria and adds that criteria to the Criteria page. This criteria is used to specify which row of data PeopleSoft Query retrieves for each item in the table. The default is the currently effective row. Defaults are: Expression 1: Record Alias.EFFDT, Condition Type: EffDt <=, Expression 2: Current Date, Effective Sequence: Last."

And then, in a note on the same page:

"Remember that the effective date operators work differently than the standard comparison operators: they always return a single effective-dated row."

Query Manager adds three criteria and an effective-sequence setting the moment you pick an effective-dated record. The replicated table carries the columns and none of the behaviour. Both panels quote the PeopleTools 8.63 page, re-read on 2026-10-13.

Effective Sequence: Last is the whole rule, pre-set, in the delivered reporting tool. For as long as the institution reported out of PeopleSoft Query, the correct answer was the default, and nobody who wrote a report had to learn what EFFSEQ was for. Replication copies PS_ACAD_PROG. It does not copy Query, its effective-date operators, or that default.

This is the same move the Salesforce posts in this series turned on. Salesforce's "Opportunities with Products" report type held the line-item grain, and replication copied the tables while leaving the boundary behind. Here the boundary is a setting on a criteria page.

The behaviour was never in the table to begin with

It's worth being exact about where effective dating lives, because it explains why no introspection tool can find it. PeopleTools builds the physical table from the record definition, and it's explicit that the build uses only part of it: SQL Create works "based on a subset of parameters in your record definition".

The columns are in that subset. The key order, the descending collation on EFFDT and EFFSEQ, and the effective-dating semantics are in the part left behind. So a warehouse holding PS_ACAD_PROG holds two columns called EFFDT and EFFSEQ with nothing attached to them, and an agent pointed at that schema has nothing to read. The relationship between a person and their program rows is one_to_many, and the "many" is history rather than several concurrent programs. Nothing in the landed schema says which.

Oracle shipped the correction once, as a product

F_ACAD_PROG_DTL exists because this problem is real and Oracle solved it in the Campus Solutions Warehouse: a fact table built from PS_ACAD_PROG with its grain written down. An institution building its own warehouse is redoing that work. Informatica ships a documented feature for PeopleSoft sources called "Handling EFFSEQ when Extracting Current Rows", which is a second vendor treating it as a named problem worth a feature. Its page body is JavaScript-rendered and wasn't read for this post, so take it as evidence the feature exists and nothing more.

Two more things the schema won't tell you

Both are real and neither belongs in this argument, so they're named rather than worked:

• PROG_ACTION and PROG_STATUS are two columns. One records what happened, the other records what the student now is. "Withdrawn" is an action and "active" is a status, and a filter on the wrong one answers a different question.
• STDNT_CAR_NBR is a career number, not a sequence. One person pursuing two degrees is one human in two stacks. Deduplicating on EMPLID alone silently drops the second.

A third is worth knowing before you point anything at this schema: PS_ prefixes views as well as tables, so a list of relations doesn't tell you which is which.

The fix

Declare the grain and the current-row rule once, where every question has to go through it:

# semantic_model/campus_solutions/relationships.yaml
# Oracle, Student Records 9.0: "all of a student's rows in the ACAD_PROG table
# are called the student's program stack". The many side is HISTORY.
relationships:
  - name: person_to_program_stack
    from_table: ps_personal_data
    from_column: emplid
    to_table: ps_acad_prog
    to_column: emplid
    cardinality: one_to_many
    notes: >
      History rather than concurrency. Never aggregate across this join without
      reducing ps_acad_prog to its current row first.

  - name: person_to_class_enrollment
    from_table: ps_personal_data
    from_column: emplid
    to_table: ps_stdnt_enrl
    to_column: emplid
    cardinality: one_to_many
    # Oracle, Student Records 9.0: five components and one self-service
    # application "all of which post enrollment records to the same table
    # (STDNT_ENRL)".

# semantic_model/campus_solutions/metrics.yaml
metrics:
  - name: students_by_academic_program
    description: >
      Headcount by academic program, counting each student's current program
      row once. The current row is the highest effseq within the highest
      effdt not in the future, per emplid and stdnt_car_nbr.
    primary_table: ps_acad_prog
    source_tables: [ps_acad_prog]
    calculation: COUNT(*)
    bindings:
      grain: [emplid, stdnt_car_nbr]
      current_row_order: [effdt DESC, effseq DESC]
      filter: effdt <= CURRENT_DATE
    # Oracle, PeopleTools: EFFSEQ "enables you to enter more than one row with
    # the same effective date", so effdt alone does not resolve to one row.
    # Oracle, Term Activation: the primary program is "the program with the
    # lowest STDNT_CAR_NBR and the highest EFFSEQ".
    reviewed: false

reviewed: false is the honest state for a declaration derived from vendor documentation rather than run against your replica. A query using an unsigned metric still answers and carries a warning until somebody approves it, which is the right behaviour here, because three of the decisions in it are yours:

• Whether future-dated rows count. effdt <= CURRENT_DATE is Query's default. An institution reporting on a term that hasn't started wants a different as-of date.
• Which program actions mean "enrolled". PROG_ACTION is a configured code list, and whether a leave of absence is still a student in the program is institutional policy.
• What a second STDNT_CAR_NBR means in your census. One person, two careers, one headcount or two. Your census definition decides, and the schema has no opinion.

Reproduce it yourself

One. Find the stack. Pick any student with more than one program row and read the rows in order:

SELECT emplid, stdnt_car_nbr, effdt, effseq,
       acad_career, acad_prog, prog_action, prog_status
FROM   ps_acad_prog
WHERE  emplid = :emplid
ORDER  BY stdnt_car_nbr, effdt, effseq;

Two. Count the right way. One current row per student per career number:

WITH current_row AS (
  SELECT emplid,
         stdnt_car_nbr,
         acad_career,
         acad_prog,
         prog_status,
         prog_action,
         ROW_NUMBER() OVER (PARTITION BY emplid, stdnt_car_nbr
                            ORDER BY effdt DESC, effseq DESC) AS rn
  FROM   ps_acad_prog
  WHERE  effdt <= CURRENT_DATE
)
SELECT acad_prog, COUNT(*) AS students
FROM   current_row
WHERE  rn = 1
GROUP  BY acad_prog
ORDER  BY students DESC;

Three. Measure your own gap. Run the naive count beside the correct one and subtract. The difference is the number of history rows your reporting has been treating as people:

SELECT COUNT(*)                                        AS all_program_rows,
       COUNT(DISTINCT emplid || '-' || stdnt_car_nbr)  AS program_stacks
FROM   ps_acad_prog
WHERE  effdt <= CURRENT_DATE;

Four. Find the rows MAX(EFFDT) would double count. This is the population that makes the careful-looking query wrong, and its size is a property of your institution's registration patterns:

SELECT emplid, stdnt_car_nbr, effdt, COUNT(*) AS rows_on_this_date
FROM   ps_acad_prog
WHERE  effdt <= CURRENT_DATE
GROUP  BY emplid, stdnt_car_nbr, effdt
HAVING COUNT(*) > 1
ORDER  BY rows_on_this_date DESC;

If that returns nothing today, it will return something after the next registration cycle. EFFSEQ is in the schema because Oracle expects it.

What this looks like in Agami

• The current-row rule is declared where every question goes through it. effdt DESC, effseq DESC partitioned by emplid and stdnt_car_nbr lives in the metric definition. Governance does not live in the prompt, so the rule can't be argued out of by a cleverly worded question.
• one_to_many carries a reason. The join from a person to their program stack is declared as history rather than concurrency, and the fan-trap and chasm-trap pre-flight reads declared join cardinality to detect when an aggregate crosses a join that multiplies rows. It names which join inflated which number and then lets you decide, because whether that's a bug depends on the question.
• An unsigned metric still warns you. A metric nobody has approved answers the question and carries a warning until a person signs it off. For a current-row rule derived from PeopleBooks and never yet run against your replica, that's the correct state to be in.
• The three judgment calls stay yours. The as-of date, which PROG_ACTION codes count as enrolled, and whether a second career number is one headcount or two are recorded as decisions in the model, with the person who made them attached.
• It answers in the assistant. The question gets asked where the work already happens, and the declaration is what the answer has to pass through.

What this post can't tell you. How wrong your number currently is. We have no Campus Solutions warehouse, no headcount, and no measurement of how many students carry two rows on one effective date. Queries three and four above are the honest substitute, and they run on your data rather than ours. Nor does this post claim any institution has made this error; it claims the replicated schema doesn't prevent it.

Frequently asked questions

Does EFF_STATUS solve this? No. EFF_STATUS marks a row active or inactive, which is a different question from which row is current. A stack can hold several active rows across several effective dates, and the most recent one is still found by ordering.

Can't I just filter PROG_STATUS = 'AC'? That narrows the stack and doesn't reduce it to one row. A student can have several active-status rows at different effective dates, and the plan changes between them are exactly the history you're trying to collapse.

Our warehouse was built from the Campus Solutions Warehouse rather than from PS_ACAD_PROG. Does this apply? Less so, and check the grain anyway. F_ACAD_PROG_DTL is one row per program action entry by Oracle's own description, so it's the stack in fact-table form rather than a current-state table. The reduction still has to happen somewhere.

Why not materialise a current-program view and point everything at that? That works and it's a good idea. It also moves the definition into a view nobody reviews, so record the as-of date and the PROG_ACTION list that went into it. The three decisions above don't disappear when the SQL moves.

Does the PS_ prefix vary by extract tool? No, and that's unusual for this series. PeopleTools gives the table "the same name as your record definition, prefaced with PS_" at create time, so the prefix is in the source database before any connector sees it.

References

• Oracle, PeopleTools 8.52 Application Designer Developer's Guide. The PS_ prefix rule, and the EFFDT / EFFSEQ / EFF_STATUS definitions.
• Oracle, PeopleTools 8.63, Specifying Effective Date Criteria. Query Manager's default criteria, including Effective Sequence: Last.
• Oracle, Student Records 9.0, Managing Student Programs, Plans, and Subplans. The program stack.
• Oracle, Student Records 9.0, Term Activation. Lowest STDNT_CAR_NBR, highest EFFSEQ.
• Oracle, Student Records 9.0, Enrollment. STDNT_ENRL as the single posting table.
• Oracle, Campus Solutions Warehouse 9.1 PeopleBook. The F_ACAD_PROG_DTL grain statement.
• Fivetran, Oracle PeopleSoft connector. Replicate the underlying database.
• Informatica, Handling EFFSEQ when Extracting Current Rows. Cited as evidence the feature exists; the page body is JavaScript-rendered and was not read.
• Ellucian Banner records when a major started and nothing records when it stopped, which is the same question failing from the opposite direction: Ellucian Banner Records When a Major Started. No Column Records When It Stopped.
• agami-core on GitHub

Make the current row a rule your headcount has to pass through

Agami is the trust layer between your AI assistant and your warehouse. It records that the current program row is the highest effseq within the highest effdt, carries that into every question about headcount, and keeps the as-of date and the PROG_ACTION list as decisions with a name attached.

Start a free trial or talk to us →