Projects Exceptions Report

Projects

Projects whose setup or plan has a problem — missing budget, no plan version, expired tasks with open cost, or no assigned manager — so the PMO can fix the projects that would otherwise fail costing, billing, or reporting.

Run note · Performance The subledger (XLA) join — especially Projects-to-XLA — is a known BI Publisher performance trap on large ledgers. Pre-join it in the warehouse, or apply tight ledger, period, and source filters before running online.

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

Sample build · illustrative

Filters

Gl Loc From

Sample

Gl Loc To

Sample

Gl Period

FEB-26

Gl Period From

FEB-26

Gl Period To

FEB-26

Ledger

US Primary

148

Projects checked

Exceptions

No active budget

Project	Exception	Detail	Organization	Manager	Status
Sample	Sample	Sample	US Operations	Sample	Open
—	—	—	EMEA	—	Posted
Sample	Sample	Sample	APAC	Sample	Validated
—	—	—	LATAM	—	Open
Sample	Sample	Sample	US Operations	Sample	Paid
Sample	Sample	Sample	US Operations	Sample	Open

AI Analyst · active

reading

The report tests each project in PJF_PROJECTS_ALL_VL against the required plan and budget setup in the PJO planning tables, listing what's missing.

flag

Six projects are taking cost with no active budget version — they can't report budget-vs-actual and will pass through unchecked spend.

root cause & next step

Baseline a budget on the six projects; cost flowing to a project with no budget is the gap that lets overruns go unseen until they are large.

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

PJF_PROJECTS_ALL_VL
PJO_PLAN_VERSIONS_VL
PJO_PLAN_LINE_DETAILS
PJO_PLAN_TYPES_B
GL_LEDGERS

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

Show / hide SQL

--Policy Violation
WITH LEDGER_TBL AS
(
	SELECT DISTINCT
	GL.NAME,
	PPAV.ORG_ID
	FROM
	PJF_PROJECTS_ALL_VL PPAV,
	HR_ORGANIZATION_INFORMATION_F HO,
	GL_LEDGERS GL
	WHERE
	PPAV.ORG_ID =HO.ORGANIZATION_ID
	AND HO.ORG_INFORMATION3=TO_CHAR(GL.LEDGER_ID)

)
, CURRENT_BUDGET AS
(
SELECT PPAV.project_id
  --, SUM(NVL(PPLD.pc_raw_cost,0)) current_approved  -- project currency raw cost (pc_code)
  , SUM(NVL(PPLD.PFC_RAW_COST,0)) current_approved  -- project Ledger currency raw cost (pfc_code)
  , PPAV.segment1
  , PPLD.planning_element_id
  , PPE.task_id 
/*   , PTV.task_number
, PTV.task_name
, PTV.task_id */
FROM pjf_projects_all_vl PPAV
  , pjo_plan_versions_vl PPVB
  , pjo_plan_line_details PPLD
  , fnd_lookup_values_vl FLVL
  , pjo_plan_types_b PPTB
  , pjo_planning_elements PPE
  --, pjf_tasks_v PTV
WHERE PPAV.project_id = PPVB.project_id
  AND PPVB.plan_version_id = PPLD.plan_version_id
  AND (DECODE(UPPER(PPVB.plan_status_code),
       'W',
	       DECODE(UPPER(PPVB.current_plan_status_flag),
		   'Y','CW',
		   PPVB.plan_status_code),
		   'B',
		      DECODE(UPPER(CONCAT(PPVB.current_plan_status_flag,PPVB.original_flag)),
			  'YY','COB',
			  'YN','CB',
			  'NY','OB',
			  PPVB.plan_status_code),
      PPVB.plan_status_code))   = FLVL.lookup_code
  AND UPPER(FLVL.lookup_type)     = 'PJO_MNG_BUD_PLAN_STATUS'
  AND UPPER(FLVL.meaning)         IN ('CURRENT AND ORIGINAL BASELINE','CURRENT BASELINE')
  AND PPVB.plan_type_id           = PPTB.plan_type_id
  AND UPPER(PPTB.plan_class_code) IN ('BUDGET')
  AND PPVB.PLAN_VERSION_ID = PPE.PLAN_VERSION_ID
  AND PPLD.PLANNING_ELEMENT_ID = PPE.PLANNING_ELEMENT_ID
 
GROUP BY PPAV.project_id
, PPAV.segment1
/* , PTV.task_number
, PTV.task_name
, PTV.task_id */
, PPLD.planning_element_id
, PPE.task_id
)
, SEC_TBL AS
(
SELECT 
	DISTINCT 
	HOU.ORGANIZATION_ID
	FROM 
	HR_OPERATING_UNITS HOU,
	FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
	PER_ROLES_DN PRD,
	PER_USER_ROLES PUR,
	PER_USERS PU
WHERE 
	1=1
	AND FURDA.ORG_ID = HOU.ORGANIZATION_ID
	AND UPPER(FURDA.ROLE_NAME) IN (:P_ROLE_CODES)
	AND UPPER(FURDA.ACTIVE_FLAG) = 'Y'
	AND UPPER(FURDA.ROLE_NAME) = UPPER(PRD.ROLE_COMMON_NAME)
	AND PRD.ROLE_ID = PUR.ROLE_ID
	AND PUR.USER_ID = PU.USER_ID
	AND UPPER(PU.USERNAME) = UPPER(:XDO_USER_NAME)
	AND PU.USER_GUID = FURDA.USER_GUID
)
, LTG_COMMITMENT AS
(
SELECT PPAV.segment1
, PPAV.project_id
, PTV.task_id
, PTV.task_number
, SUM(PCT.additional_info3) UD3
FROM pjc_commitment_txns PCT
, pjf_projects_all_vl PPAV
, pjf_tasks_v PTV
, pjf_p_periods_all_v PPPAV

WHERE 1=1 
AND PPAV.project_id = PTV.project_id
AND PPAV.project_id = pct.project_id
AND PTV.task_id = pct.task_id
AND UPPER(PCT.commitment_type) = 'EXT'
AND PCT.DENOM_CURRENCY_CODE = PCT.ACCT_CURRENCY_CODE  -- return when trx and ledger curr are same
AND PPAV.ORG_ID							  = PPPAV.ORG_ID
AND PCT.RECVR_PA_DATE					  BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
AND PPPAV.END_DATE   <= 		(
		SELECT DISTINCT
		GP.END_DATE
		FROM
		gl_periods GP,
	    gl_period_types GPT
        , gl_period_statuses GPS
		WHERE
		1=1
		AND UPPER(GP.period_set_name) = UPPER(:P_CALENDAR)
		AND GP.adjustment_period_flag ='N'	
		AND GP.period_type = GPT.period_type
        AND GPT.period_type = GPS.period_type
        AND GPS.closing_status = 'O'
		AND TRUNC(SYSDATE) BETWEEN GP.START_DATE AND GP.END_DATE
		)
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD) OR 'All' IN (:P_GL_PERIOD||'All'))  -- ending gl period inception

/* AND (PPPAV.START_DATE >= (select distinct start_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD_FROM) OR 'All' IN (:P_GL_PERIOD_FROM||'All'))
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD_TO) OR 'All' IN (:P_GL_PERIOD_TO||'All')) */

GROUP BY PPAV.segment1
, PPAV.project_id
, PTV.task_id
, PTV.task_number

UNION ALL

SELECT PPAV.segment1
 , PPAV.project_id
, PTV.task_id
, PTV.task_number
, (SUM(PCT.additional_info3)*CONV_TBL.CONVERSION_RATE) UD3

FROM pjc_commitment_txns PCT
, pjf_projects_all_vl PPAV
, pjf_tasks_v PTV
,(SELECT
DISTINCT
GDR.FROM_CURRENCY,
GDR.TO_CURRENCY,
GDR.CONVERSION_RATE,
GDR.CONVERSION_DATE
FROM
GL_DAILY_RATES GDR
,GL_DAILY_CONVERSION_TYPES GDCT
,GL_LEDGERS GL
WHERE 1=1
AND GDCT.CONVERSION_TYPE				= GDR.CONVERSION_TYPE
AND GL.CURRENCY_CODE					= GDR.TO_CURRENCY
AND UPPER(GDCT.USER_CONVERSION_TYPE)    ='CORPORATE'
) CONV_TBL
,pjf_p_periods_all_v PPPAV
 

WHERE 1=1 
AND PPAV.project_id = PTV.project_id
AND PPAV.project_id = pct.project_id
AND PTV.task_id = pct.task_id
AND UPPER(PCT.commitment_type) = 'EXT'
AND PCT.DENOM_CURRENCY_CODE = CONV_TBL.FROM_CURRENCY
AND PCT.ACCT_CURRENCY_CODE = CONV_TBL.TO_CURRENCY	
AND PCT.EXPENDITURE_ITEM_DATE=CONV_TBL.CONVERSION_DATE
AND PPAV.ORG_ID							  = PPPAV.ORG_ID
AND PCT.RECVR_PA_DATE					  BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
AND PPPAV.END_DATE   <= 		(
		SELECT DISTINCT
		GP.END_DATE
		FROM
		gl_periods GP,
	    gl_period_types GPT
        , gl_period_statuses GPS
		WHERE
		1=1
		AND UPPER(GP.period_set_name) = UPPER(:P_CALENDAR)
		AND GP.adjustment_period_flag ='N'	
		AND GP.period_type = GPT.period_type
        AND GPT.period_type = GPS.period_type
        AND GPS.closing_status = 'O'
		AND TRUNC(SYSDATE) BETWEEN GP.START_DATE AND GP.END_DATE
		)
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD) OR 'All' IN (:P_GL_PERIOD||'All'))  -- ending gl period inception

GROUP BY 
  PPAV.project_id
, PTV.task_id
, PTV.task_number
, CONV_TBL.CONVERSION_RATE
, PPAV.segment1

)

, ASSET_TBL AS
(
SELECT
project_id,
task_id,
proj_num,
task_number,
SUM(cost_interfaced_to_fa) cost_interfaced_to_fa
FROM
(
SELECT DISTINCT PPAV.project_id
, PTV.task_id
, PPAV.segment1 proj_num
, PTV.task_number
,PPALA.PROJECT_ASSET_LINE_ID
,PPALA.CURRENT_ASSET_COST
/* ,NVL((
		CASE 
		  WHEN PPALA.TRANSFER_STATUS_CODE = 'T' and TO_NUMBER(SUBSTR(PPALA.FA_PERIOD_NAME, 1, 2)) <=  TO_NUMBER(SUBSTR(PPPAV.PERIOD_NAME, 1, 2))THEN
			PPALA.CURRENT_ASSET_COST
			END
	),0) COST_INTERFACED_TO_FA */
	
, NVL((CASE WHEN PPALA.TRANSFER_STATUS_CODE = 'T' THEN PPALA.CURRENT_ASSET_COST END),0) COST_INTERFACED_TO_FA

FROM pjf_projects_all_vl PPAV
, pjf_tasks_v PTV
, PJC_PRJ_ASSET_LNS_ALL PPALA
, pjc_prj_asset_ln_dets PPAID
,PJF_P_PERIODS_ALL_V PPPAV
WHERE 1=1
AND PPAV.project_id                       = PTV.project_id
AND PPAV.PROJECT_ID 					  = PPALA.PROJECT_ID
AND PTV.task_id                           = PPALA.task_id 
and PPALA.PROJECT_ASSET_LINE_DETAIL_ID    = PPAID.PROJECT_ASSET_LINE_DETAIL_ID
AND PPAV.ORG_ID							  = PPPAV.ORG_ID
AND PPPAV.PERIOD_NAME                     = PPALA.FA_PERIOD_NAME
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD) OR 'All' IN (:P_GL_PERIOD||'All')) 
--*** END
)
WHERE 1=1
GROUP BY
project_id,
task_id,
proj_num,
task_number
)

SELECT /*+ PARALLEL(e,12) */
proj_num
, proj_name
, proj_manager
, proj_year
, task_number
, task_name
, legal_entity
, channel
, gl_location
, proj_cur
, Expense_amt
, cip_cost_amount
, cost_FA
, GL_CIP_bal
, cap_amt
, total_spent
, PA_accrual_amt
, LTG_PO_Committed
, total_spent_Commit
, current_approved_budget
, over_spent
, over_per
, over_per_test
, budget_status

, subtotal_total_spend
, subtotal_budget

, subtotal_over_spent
, subtotal_over_per

, CASE WHEN subtotal_over_per < 100 then 'Under Budget' 
       WHEN subtotal_over_per  = 100 then 'At Budget'
	   WHEN subtotal_over_per  > 100 AND subtotal_over_per < 110 AND subtotal_over_spent < 2500000  then 'Over Budget'  
	   WHEN subtotal_over_per  > 110 OR subtotal_over_spent >= 2500000 then 'Policy Violation' END subtotal_budget_status 
	   
, current_over_spent	   
, current_over_per	 
, current_total_spent_Commit
, current_subtotal_over_spent
, current_subtotal_over_per
 
, CASE WHEN current_subtotal_over_per < 100 then 'Under Budget' 
       WHEN current_subtotal_over_per  = 100 then 'At Budget'
	   WHEN current_subtotal_over_per  > 100 AND current_subtotal_over_per < 110 AND current_subtotal_over_spent < 2500000  then 'Over Budget'  
	   WHEN current_subtotal_over_per  > 110 OR current_subtotal_over_spent >= 2500000 then 'Policy Violation' END current_subtotal_budget_status	   
, proj_status

, class_code
, email_address
, person_id

FROM (

SELECT /*+ PARALLEL(e,12) */
proj_num
, proj_name
, proj_manager
, proj_year
, task_number
, task_name
, legal_entity
, channel
, gl_location
, proj_cur
, Expense_amt
, cip_cost_amount
, cost_FA
, GL_CIP_bal
, cap_amt
, total_spent
, PA_accrual_amt
, LTG_PO_Committed
, total_spent_Commit
, current_approved_budget
, over_spent
, over_per

, over_per*100 over_per_test

, CASE WHEN over_per*100 < 100 then 'Under Budget' 
       WHEN over_per*100  = 100 then 'At Budget'
	   WHEN over_per*100  > 100 AND over_per*100 < 110 AND over_spent < 2500000 then 'Over Budget'
	   WHEN over_per*100  > 110 OR over_spent >= 2500000 then 'Policy Violation' END budget_status 
	   
,current_over_spent	   
,current_over_per	 
,current_total_spent_Commit  
, proj_status

, SUM(total_spent_Commit) OVER (PARTITION BY proj_num) subtotal_total_spend
, SUM(current_approved_budget) OVER (PARTITION BY proj_num) subtotal_budget

, SUM(total_spent_Commit) OVER (PARTITION BY proj_num)- SUM(current_approved_budget) OVER (PARTITION BY proj_num) subtotal_over_spent

, CASE WHEN SUM(current_approved_budget) OVER (PARTITION BY proj_num) = 0 THEN 0 ELSE ROUND((SUM(total_spent_Commit) OVER (PARTITION BY proj_num)/SUM(current_approved_budget) OVER (PARTITION BY proj_num))*100) END  subtotal_over_per

/* , CASE WHEN SUM(current_approved_budget) OVER (PARTITION BY proj_num) = 0 THEN 0 ELSE ROUND((SUM(current_total_spent_Commit) OVER (PARTITION BY proj_num)/SUM(current_approved_budget) OVER (PARTITION BY proj_num))*100) END  current_subtotal_over_per
, SUM(current_total_spent_Commit) OVER (PARTITION BY proj_num)- SUM(current_approved_budget) OVER (PARTITION BY proj_num) current_subtotal_over_spent */

, SUM(total_spent) OVER (PARTITION BY proj_num)- SUM(current_approved_budget) OVER (PARTITION BY proj_num) current_subtotal_over_spent

, CASE WHEN SUM(current_approved_budget) OVER (PARTITION BY proj_num) = 0 THEN 0 ELSE ROUND((SUM(total_spent) OVER (PARTITION BY proj_num)/SUM(current_approved_budget) OVER (PARTITION BY proj_num))*100) END  current_subtotal_over_per

, class_code
, email_address
, person_id

FROM(
SELECT /*+ PARALLEL(e,12) */
proj_num
, proj_name
, proj_manager
, proj_year
, task_number
, task_name
, legal_entity
, channel
, gl_location
, proj_cur
, SUM(NVL(Expense_amt,0)) Expense_amt

, SUM(NVL(cip_cost_amount,0)) cip_cost_amount
, NVL(cost_FA,0) cost_FA
, SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0) GL_CIP_bal
, NVL(cost_FA,0) cap_amt
, SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0) total_spent

, SUM(NVL(PA_accrual_amt,0)) PA_accrual_amt
, NVL(commitments,0) LTG_PO_Committed
, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0))+NVL(commitments,0) total_spent_Commit

, NVL(current_approved_budget,0) current_approved_budget

, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0))+NVL(commitments,0) - NVL(current_approved_budget,0) over_spent

, CASE WHEN NVL(current_approved_budget,0) = 0 THEN 0 ELSE ROUND( ( ((SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0))+NVL(commitments,0) )/ NVL(current_approved_budget,0) ) ,10) END over_per
--, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0))- NVL(current_approved_budget,0) current_over_spent
, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0)) - NVL(current_approved_budget,0) current_over_spent
--, CASE WHEN NVL(current_approved_budget,0) = 0 THEN 0 ELSE ROUND( ( ((SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0)))/ NVL(current_approved_budget,0) ) ,10) END current_over_per
, CASE WHEN NVL(current_approved_budget,0) = 0 THEN 0 ELSE ROUND( ( (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))/ NVL(current_approved_budget,0) ) ,10) END current_over_per
, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0)) current_total_spent_Commit
, proj_status

, class_code
, email_address
, person_id

FROM(
SELECT /*+ PARALLEL(e,12) */
PPAV.segment1 proj_num
, PPAV.name proj_name
, PPNFV.list_name proj_manager
, SUBSTR(PPAV.SEGMENT1 ,8,2) proj_year
, PTV.task_number
, PTV.task_name

, XEP.name legal_entity
, PPAV.attribute2 channel
, PPAV.attribute1 gl_location
, PPAV.project_currency_code proj_cur
, (CASE WHEN PEIA.capitalizable_flag = 'N' THEN PCDLA.acct_raw_cost END) Expense_amt

, (CASE WHEN UPPER(PTSV.user_transaction_source) IN ('MISCELLANEOUS COST') AND UPPER(PTDV.document_name) IN ('MISCELLANEOUS COST') AND PEIA.net_zero_adjustment_flag = 'Y' THEN PCDLA.acct_raw_cost END) PA_accrual_amt

, (select UD3
FROM LTG_COMMITMENT LTG
WHERE LTG.project_id(+) = PPAV.project_id
AND LTG.task_id(+) = PTV.task_id
--AND PPPAV.PERIOD_NAME = LTG.GL_PERIOD
GROUP BY PPAV.segment1
, UD3
) commitments

, (SELECT current_approved
     FROM current_budget CB
	 WHERE CB.project_id = PPAV.project_id
	 AND CB.task_id = PTV.task_id ) current_approved_budget
	 
, PPSV.project_status_name proj_status

, PCCV.class_code

--, PPAID.cip_cost cip_cost_amount
, (CASE WHEN PEIA.capitalizable_flag = 'Y' THEN PCDLA.acct_raw_cost END) cip_cost_amount

, ASTB.cost_interfaced_to_fa cost_FA

, PEA.email_address
, PPNFV.person_id

, LT.name  ledger
, XEP.LEGAL_ENTITY_ID 

, PEIA.expenditure_item_id trx
, PCDLA.line_num

FROM pjf_projects_all_vl PPAV
, pjf_project_statuses_vl PPSV
, pjf_tasks_v PTV
, pjc_exp_items_all PEIA
, pjc_cost_dist_lines_all PCDLA
, pjf_txn_sources_vl PTSV
, pjf_txn_document_vl PTDV
, pjf_p_periods_all_v PPPAV

, pjf_project_classes PPC
, pjf_class_codes_vl PCCV
, pjf_class_categories_vl PCCB

, pjf_latestprojectmanager_v PLV
, per_person_names_f_v PPNFV

, per_all_people_f_v PAPFV
, PER_EMAIL_ADDRESSES PEA

, ASSET_TBL ASTB

, ledger_tbl LT
, xle_entity_profiles XEP
, SEC_TBL ST

WHERE 1 = 1
AND PPAV.template_flag                  = 'N'
AND PPAV.project_status_code            = PPSV.project_status_code
AND PPAV.project_id                     = PTV.project_id
AND PPAV.project_id                     = PEIA.project_id
AND PTV.task_id                         = PEIA.task_id
AND PPAV.project_id                     = PCDLA.project_id 
AND PEIA.task_id                        = PCDLA.task_id
AND PEIA.expenditure_item_id            = PCDLA.expenditure_item_id
AND PTV.task_id                         = PCDLA.task_id
AND PEIA.transaction_source_id          = PTSV.transaction_source_id	   
AND PEIA.document_id                    = PTDV.document_id
AND PTSV.transaction_source_id          = PTDV.transaction_source_id

AND PPAV.project_id   = PPC.project_id
AND PPC.class_code_id = PCCV.class_code_id
AND PPC.CLASS_CATEGORY_ID  = PCCV.CLASS_CATEGORY_ID
AND PCCB.class_category_id = PCCV.class_category_id
AND UPPER(PCCB.class_category) IN ('PROJECT TYPE CLASS')
AND UPPER(PCCV.class_code) IN ('CER')

AND PPAV.project_id 					= PLV.project_id(+)
AND PLV.resource_source_id 				= PPNFV.person_id(+)
AND SYSDATE 							BETWEEN PPNFV.effective_start_date(+) AND PPNFV.effective_end_date(+)

AND PAPFV.person_id(+)  = PPNFV.person_id
AND PEA.EMAIL_ADDRESS_ID(+) = PAPFV.PRIMARY_EMAIL_ID

AND PPAV.project_id = ASTB.project_id(+)
AND PTV.task_id = ASTB.task_id(+)

AND PPAV.org_id								= LT.org_id
AND PPAV.legal_entity_id 					= XEP.legal_entity_id
AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
AND PPAV.ORG_ID							  = PPPAV.ORG_ID
AND PCDLA.RECVR_PA_DATE					  BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
AND PPPAV.END_DATE   <= 		(
		SELECT DISTINCT
		GP.END_DATE
		FROM
		gl_periods GP,
	    gl_period_types GPT
        , gl_period_statuses GPS
		WHERE
		1=1
		AND UPPER(GP.period_set_name) = UPPER(:P_CALENDAR)
		AND GP.adjustment_period_flag ='N'	
		AND GP.period_type = GPT.period_type
        AND GPT.period_type = GPS.period_type
        AND GPS.closing_status = 'O'
		AND TRUNC(SYSDATE) BETWEEN GP.START_DATE AND GP.END_DATE
		)
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD) OR 'All' IN (:P_GL_PERIOD||'All'))  -- ending gl period inception

/* AND (PPPAV.START_DATE >= (select distinct start_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD_FROM) OR 'All' IN (:P_GL_PERIOD_FROM||'All'))
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD_TO) OR 'All' IN (:P_GL_PERIOD_TO||'All')) */
--*** END

UNION

SELECT /*+ PARALLEL(e,12) */
PPAV.segment1 proj_num
, PPAV.name proj_name
, PPNFV.list_name proj_manager
, SUBSTR(PPAV.SEGMENT1 ,8,2) proj_year
, PTV.task_number
, PTV.task_name

, XEP.name legal_entity
, PPAV.attribute2 channel
, PPAV.attribute1 gl_location
, PPAV.project_currency_code proj_cur
, NULL Expense_amt

, NULL PA_accrual_amt

/* , (select UD3
FROM LTG_COMMITMENT LTG
WHERE LTG.project_id(+) = PPAV.project_id
AND LTG.task_id(+) = PTV.task_id
--AND PPPAV.PERIOD_NAME = LTG.GL_PERIOD
GROUP BY PPAV.segment1
, UD3
) commitments */

, LTG.UD3 commitments
	 
, NVL(CB.current_approved,0) current_approved_budget
	 
, PPSV.project_status_name proj_status

, PCCV.class_code

--, PPAID.cip_cost cip_cost_amount
, NULL cip_cost_amount

, ASTB.cost_interfaced_to_fa cost_FA

, PEA.email_address
, PPNFV.person_id

, LT.name  ledger
, XEP.LEGAL_ENTITY_ID 

, null trx 
, null line_num 

FROM pjf_projects_all_vl PPAV
, pjf_project_statuses_vl PPSV
, pjf_tasks_v PTV

, pjf_project_classes PPC
, pjf_class_codes_vl PCCV
, pjf_class_categories_vl PCCB

, pjf_latestprojectmanager_v PLV
, per_person_names_f_v PPNFV

, per_all_people_f_v PAPFV
, PER_EMAIL_ADDRESSES PEA

, ASSET_TBL ASTB
, LTG_COMMITMENT LTG
, current_budget CB 

, ledger_tbl LT
, xle_entity_profiles XEP
, SEC_TBL ST

WHERE 1 = 1
AND PPAV.template_flag                  = 'N'
AND PPAV.project_status_code            = PPSV.project_status_code
AND PPAV.project_id                     = PTV.project_id

AND LTG.project_id(+) = PPAV.project_id
AND LTG.task_id(+) = PTV.task_id

AND CB.project_id = PPAV.project_id
AND CB.task_id = PTV.task_id

AND PPAV.project_id   = PPC.project_id
AND PPC.class_code_id = PCCV.class_code_id
AND PPC.CLASS_CATEGORY_ID  = PCCV.CLASS_CATEGORY_ID
AND PCCB.class_category_id = PCCV.class_category_id
AND UPPER(PCCB.class_category) IN ('PROJECT TYPE CLASS')
AND UPPER(PCCV.class_code) IN ('CER')

AND PPAV.project_id 					= PLV.project_id(+)
AND PLV.resource_source_id 				= PPNFV.person_id(+)
AND SYSDATE 							BETWEEN PPNFV.effective_start_date(+) AND PPNFV.effective_end_date(+)

AND PAPFV.person_id(+)  = PPNFV.person_id
AND PEA.EMAIL_ADDRESS_ID(+) = PAPFV.PRIMARY_EMAIL_ID

AND PPAV.project_id = ASTB.project_id(+)
AND PTV.task_id = ASTB.task_id(+)

AND PPAV.org_id								= LT.org_id
AND PPAV.legal_entity_id 					= XEP.legal_entity_id
AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
--end 
)
WHERE 1 = 1

AND (ledger            					IN (:P_LEDGER)
OR  'ALL'                				IN (:P_LEDGER||'ALL'))

AND (LEGAL_ENTITY_ID            		IN (:P_LEGAL_ENTITY)
OR  'All'                				IN (:P_LEGAL_ENTITY||'All'))

AND (CLASS_CODE                 		IN (:P_PROJ_TYPE_CLASS)
OR  'All'                				IN (:P_PROJ_TYPE_CLASS||'All'))

AND (proj_num		            		IN (:P_PROJ_NUM)
OR  'All'                				IN (:P_PROJ_NUM||'All'))

AND (proj_status		                IN (:P_PROJ_STATUS)
OR  'All'                				IN (:P_PROJ_STATUS||'All'))

AND (proj_year                          >= (:P_YEAR_FROM)
OR  'All'                				IN (:P_YEAR_FROM||'All'))
AND (proj_year                          <= (:P_YEAR_TO)
OR  'All'                				IN (:P_YEAR_TO||'All'))

AND (channel		                >= (:P_CHANNEL_FROM)
OR  'All'                			    IN (:P_CHANNEL_FROM||'All'))
AND (channel            		    <= (:P_CHANNEL_TO)
OR  'All'                			    IN (:P_CHANNEL_TO||'All'))

AND (gl_location		                >= (:P_GL_LOC_FROM)
OR  'All'                			    IN (:P_GL_LOC_FROM||'All'))
AND (gl_location           		        <= (:P_GL_LOC_TO)
OR  'All'                			    IN (:P_GL_LOC_TO||'All'))

GROUP BY proj_num
, proj_name
, proj_manager
, proj_year
, task_number
, task_name
, legal_entity
, channel
, gl_location
, proj_cur
, commitments
, current_approved_budget
, proj_status
, class_code
, email_address
, person_id
, cost_FA
)
WHERE 1 = 1
)
WHERE 1=1
ORDER BY proj_num

:P_GL_LOC_FROM :P_GL_LOC_TO :P_GL_PERIOD :P_GL_PERIOD_FROM :P_GL_PERIOD_TO :P_LEDGER :P_LEGAL_ENTITY :P_PROJ_NUM :P_PROJ_STATUS :P_PROJ_TYPE_CLASS :P_CHANNEL_FROM :P_CHANNEL_TO :P_YEAR_FROM :P_YEAR_TO :XDO_USER_NAME :P_CALENDAR :P_ROLE_CODES

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
PJO_PLAN_VERSIONS_VL	dimension	dimension
PJO_PLAN_LINE_DETAILS	dimension	dimension
PJO_PLAN_TYPES_B	dimension	dimension
GL_LEDGERS	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.

Projects 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
PJF_PROJECTS_ALL_VL	31	67
PJO_PLAN_VERSIONS_VL	No page in this catalog’s data dictionary yet
PJO_PLAN_LINE_DETAILS	12	2
PJO_PLAN_TYPES_B	No page in this catalog’s data dictionary yet
GL_LEDGERS	10	104

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.