Active Employee Details Report

Payables

Active employee detail — each employee's organization, supervisor, work email, and default expense account — the roster Payables and Expenses use to validate expense reporters and route approvals.

Sample build of the Active Employee Details Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Business Unit

APAC

Person Id

1003

P Brand Segment1

Sample

P Brand Segment2

Sample

P Cost Center Segment1

$96,400.00

P Cost Center Segment2

$96,400.00

2,140

Active employees

No supervisor

No default account

Employee	Organization	Supervisor	Email	Default Account	Status
Sample	US Operations	Sample	Sample	1000-2100-000	Open
—	EMEA	—	—	1000-5400-000	Posted
Sample	APAC	Sample	Sample	1000-1410-000	Validated
—	LATAM	—	—	2000-2100-000	Open
Sample	US Operations	Sample	Sample	1000-6300-000	Paid
Sample	US Operations	Sample	Sample	1000-2100-000	Open

AI Analyst · active

reading

The report reads PER_ALL_PEOPLE_F with assignments and supervisor links, plus the default expense account.

flag

12 active employees have no supervisor assignment, so their expense reports can't route for approval and will stall on submission.

root cause & next step

Assign supervisors to the 12; a missing supervisor is the most common reason an expense report sits unrouted.

Illustrative data. The live interactive version — drill-through, filters, export, and the AI Analyst — runs on your warehouse. See it live →

This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.

Data sources

How it interconnects: this data set reads the physical tables above. Those same tables surface in OTBI as subject areas and in BICC as PVOs — three lenses on one source. Open any table to trace its subject areas and View Objects.

SQL data set · genericized · parameterized · no hardcoded segments

Show / hide SQL

select * from (
SELECT 
papf.person_id,
ppnf.LAST_NAME || DECODE(NVL(LENGTH(ppnf.FIRST_NAME), 0), 0, '', ', ') || ppnf.FIRST_NAME || 
DECODE(NVL(LENGTH(ppnf.MIDDLE_NAMES), 0), 0, '', ', ') || ppnf.MIDDLE_NAMES || 
DECODE(NVL(LENGTH(ppnf.TITLE), 0), 0, '', ', ') || ppnf.TITLE employee_name, 
papf.person_number, 
TO_CHAR(papf.START_DATE, 'DD/MM/YYYY') Start_Date, 
papf.PRIMARY_EMAIL_ID,
pea.EMAIL_ADDRESS emp_email_address, 
CASE when glcc.segment1 is not null then glcc.segment1||'.'||glcc.segment2||'.'||glcc.segment3||'.'||glcc.segment4||'.'||glcc.segment5||'.'||glcc.segment6||'.'||glcc.segment7||'.'||glcc.segment8||'.'||glcc.segment9 end Default_Expense_Account   -- -- fusion5
,hauft.NAME Legal_entity  
,hou.NAME  bu_name      
,pjft.name    jobname       
,pjf.job_code  JOB_CODE  
,ppnf1.display_name SUPERVISORNAME 
,papf2.person_number  supervisor_number
,pea2.EMAIL_ADDRESS supervisor_mail 
FROM    per_all_people_f papf
       ,per_all_assignments_m paam
       ,per_assignment_supervisors_f pasf
	   ,per_all_people_f papf2 
       ,per_person_names_f ppnf1
	   ,per_person_names_f ppnf
	   ,gl_code_combinations glcc
	   ,per_email_addresses pea
	   ,per_email_addresses pea2 
	   ,HR_ORG_UNIT_CLASSIFICATIONS_F houcf
	   ,HR_ALL_ORGANIZATION_UNITS_F haouf
	   ,HR_ORGANIZATION_UNITS_F_TL hauft
	   ,hr_operating_units hou
	   ,per_jobs_f_tl pjft
	   ,per_jobs_f pjf
	   --,hr_all_positions_f_tl hapft
WHERE  1=1 
AND TRUNC(SYSDATE) BETWEEN TRUNC(papf.EFFECTIVE_START_DATE) AND TRUNC(papf.EFFECTIVE_END_DATE)
AND papf.person_id = paam.person_id
AND    paam.primary_assignment_flag = 'Y'
----AND    paam.assignment_type = 'E' 
AND    paam.effective_latest_change = 'Y'
AND ASSIGNMENT_STATUS_TYPE ='ACTIVE'
AND    TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
--AND    TRUNC(SYSDATE) BETWEEN pasf.effective_start_date AND pasf.effective_end_date
AND     papf.person_id = pasf.person_id (+)
AND    pasf.manager_type (+) = 'LINE_MANAGER'
AND    pasf.effective_start_date (+)<= sysdate
AND    pasf.effective_end_date  (+)  > sysdate
AND    ppnf1.person_id(+) = pasf.manager_id
AND    ppnf1.name_type(+) = 'GLOBAL'
----AND    TRUNC(SYSDATE) BETWEEN ppnf1.effective_start_date AND ppnf1.effective_end_date
AND    ppnf1.effective_start_date (+)<= sysdate
AND    ppnf1.effective_end_date  (+)  > sysdate
AND    ppnf.person_id = papf.person_id
AND    ppnf.name_type = 'GLOBAL'
AND    TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND    paam.DEFAULT_CODE_COMB_ID = glcc.CODE_COMBINATION_ID(+)

AND papf.PERSON_ID= pea.PERSON_ID(+)
AND papf.PRIMARY_EMAIL_ID =pea.EMAIL_ADDRESS_ID(+)

AND papf2.PERSON_ID(+) = pasf.manager_id
AND papf2.PRIMARY_EMAIL_ID =pea2.EMAIL_ADDRESS_ID(+)
AND papf2.effective_start_date (+)<= sysdate
AND papf2.effective_end_date  (+)  > sysdate
--AND TRUNC(SYSDATE) BETWEEN papf2.effective_start_date AND papf2.effective_end_date
AND haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID 
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID 
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE 
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE 
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE 
AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP' 
AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date
AND hauft.organization_id = paam.legal_entity_id
AND hou.ORGANIZATION_ID = paam.BUSINESS_UNIT_ID 
AND paam.job_id = pjft.job_id(+)
AND pjft.language(+) = 'US'
-- --AND paam.position_id = hapft.position_id(+)
-- --AND    hapft.language (+) = 'US'
AND paam.job_id= pjf.job_id(+)
--and papf.person_number = '105'
AND ppnf.PERSON_ID = nvl(:P_PERSON_ID, ppnf.PERSON_ID)  
AND (paam.legal_entity_id IN (:p_Legal_Entity)OR  'All' IN (:p_Legal_Entity||'All'))
AND hou.ORGANIZATION_ID = nvl(:P_Business_unit ,hou.ORGANIZATION_ID)
AND (nvl(pjf.job_id,'1') IN (:p_job_id) OR  'All' IN (:p_job_id||'All'))
AND nvl(papf2.person_id,1) = nvl (:p_manager_pid , nvl(papf2.person_id,1))

AND  ( nvl(glcc.segment1,0)  between nvl(:p_company_segment1,nvl(glcc.segment1,0)) AND nvl(:p_company_segment2,nvl(glcc.segment1,0)) )
AND  ( nvl(glcc.segment2,0)  between nvl(:p_Selling_Method_segment1,nvl(glcc.segment2,0)) AND nvl(:p_Selling_Method_segment2,nvl(glcc.segment2,0)) )
AND  ( nvl(glcc.segment3,0)  between nvl(:p_Cost_Center_segment1,nvl(glcc.segment3,0)) AND nvl(:p_Cost_Center_segment2,nvl(glcc.segment3,0)) )
AND  ( nvl(glcc.segment5,0)  between nvl(:p_location_segment1,nvl(glcc.segment5,0)) AND nvl(:p_location_segment2,nvl(glcc.segment5,0)) )
AND  ( nvl(glcc.segment6,0)  between nvl(:p_Brand_segment1,nvl(glcc.segment6,0)) AND nvl(:p_Brand_segment2,nvl(glcc.segment6,0)) )
AND hou.ORGANIZATION_ID IN (
							SELECT distinct bu.bu_id
							FROM fusion.fun_all_business_units_v bu
							,fusion.fun_user_role_data_asgnmnts role
							,fusion.per_users pu
							,fusion.per_roles_dn_vl pr
							WHERE role.org_id = bu.bu_id
							AND pu.user_guid = role.user_guid
							AND pr.role_common_name = role.role_name
							AND role.active_flag ='Y' 
							AND pu.username = fnd_global.user_name
                          )
)
Order by employee_name

:P_Business_unit :P_PERSON_ID :p_Brand_segment1 :p_Brand_segment2 :p_Cost_Center_segment1 :p_Cost_Center_segment2 :p_Legal_Entity :p_Selling_Method_segment1 :p_Selling_Method_segment2 :p_company_segment1 :p_company_segment2 :p_job_id :p_location_segment1 :p_location_segment2 :p_manager_pid

The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.

●— fact → dimension join

Element	Type	Definition
PER_ALL_ASSIGNMENTS_M	dimension	dimension
PER_ASSIGNMENT_SUPERVISORS_F	dimension	dimension
PER_PERSON_NAMES_F	dimension	dimension
GL_CODE_COMBINATIONS	dimension	dimension
Amount	measure	measure

Runs on your cloud warehouse — Snowflake, BigQuery, Redshift, or Synapse on AWS, Google Cloud, Azure, or any provider. Reconciled to the source control total — 0% variance by design. You own the code, the model, and the data.

How the data gets here: a BICC bulk extract of the source tables above, on the same pattern for every report. See the extraction pattern & data flow →

See the complete model

How this report's fact and dimensions fit the full picture, via conformed keys.

Payables data model →Enterprise model →

Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.

Table	Reporting columns	Subject areas
PER_ALL_PEOPLE_F	105	26
PER_ALL_ASSIGNMENTS_M	129	21
PER_ASSIGNMENT_SUPERVISORS_F	51	0
PER_PERSON_NAMES_F	160	0
GL_CODE_COMBINATIONS	7	61
PER_EMAIL_ADDRESSES	80	82
HR_ORG_UNIT_CLASSIFICATIONS_F	16	95
HR_ALL_ORGANIZATION_UNITS_F	18	156
HR_ORGANIZATION_UNITS_F_TL	13	172
HR_OPERATING_UNITS	Setup / configuration table — joined for reference, not exposed for analytics
PER_JOBS_F_TL	12	8
PER_JOBS_F	98	4

Reporting columns = fields the report selects that are exposed as analytics attributes; subject areas = the OTBI subject areas the table appears in. Setup and configuration tables (master data, ledger and book setup, lookups) are referenced by the report's joins but aren't exposed as analytics columns or subject areas — that's expected, not a gap.