Projects Budgets vs Actuals Report

Projects

Project budget against actual cost and commitments by task, with variance and percent spent, so project managers and finance can see which projects are over or under budget and where the spend is going.

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 Budgets vs Actuals Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Date

2026-02-28

Gl Brand From

Sample

Gl Brand To

Sample

Gl Loc From

Sample

Gl Loc To

Sample

Gl Period From

FEB-26

148

Projects

$62.0M

Budget

Over budget

Project	Task	Budget	Actual	Commitment	Variance	% Spent
Sample	Sample	Sample	Sample	Sample	Sample	Sample
—	—	—	—	—	—	—
Sample	Sample	Sample	Sample	Sample	Sample	Sample
—	—	—	—	—	—	—
Sample	Sample	Sample	Sample	Sample	Sample	Sample
Sample	Sample	Sample	Sample	Sample	Sample	Sample

AI Analyst · active

reading

The report compares planned amounts in the project budget to actuals in PJC_EXP_ITEMS_ALL and open commitments, by project and task.

flag

11 projects are over budget, and three are over before commitments are even spent — their open purchase orders will push them further over once delivered.

root cause & next step

Re-baseline or fund the three commitment-driven overruns now; budget-vs-actual that ignores open commitments hides overruns until the invoices land.

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

<![CDATA[-- Budgets to actuals Tooling

WITH ASSET_TBL AS
(
SELECT
project_id
, task_id
, manufacturer_name
, tonnage
, proj_num
, task_number
, SUM(cost_interfaced_to_fa) cost_interfaced_to_fa

FROM (
SELECT PPAV.project_id
, PTV.task_id
, PPAA.manufacturer_name
, FAK.SEGMENT7 tonnage
, PPAV.segment1 proj_num
, PTV.task_number
, (CASE WHEN PPALA.transfer_status_code = 'T' then PCDLA.project_raw_cost  end ) cost_interfaced_to_fa

FROM pjf_projects_all_vl PPAV
, pjf_tasks_v PTV
, pjc_prj_assets_all PPAA
, PJC_PRJ_ASSET_LNS_ALL PPALA
, fa_asset_keywords FAK
, gl_code_combinations GCC

--added
, pjc_exp_items_all PEIA
, pjc_cost_dist_lines_all PCDLA
, pjc_prj_asset_ln_dets PPAID
--end

WHERE 1=1
AND PPAV.project_id                       = PTV.project_id
AND PPAV.project_id                       = PPAA.project_id
AND PPALA.PROJECT_ID                      = PPAA.PROJECT_ID
and PPALA.PROJECT_ASSET_ID                = PPAA.PROJECT_ASSET_ID
AND PPAA.ASSET_KEY_CCID 				  = FAK.CODE_COMBINATION_ID
AND PPAV.PROJECT_ID 					  = PPALA.PROJECT_ID
AND PTV.task_id                           = PPALA.task_id 
AND PPALA.CIP_CCID 						  = GCC.CODE_COMBINATION_ID

--added
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 PPAID.expenditure_item_id    = pcdla.expenditure_item_id
and PPAID.LINE_NUM               = pcdla.LINE_NUM
and PPALA.PROJECT_ASSET_LINE_DETAIL_ID  =    PPAID.PROJECT_ASSET_LINE_DETAIL_ID
--end

)
WHERE 1=1
GROUP BY project_id
, task_id
, manufacturer_name
, tonnage
, proj_num
, task_number

)

, CURRENT_BUDGET AS
(
SELECT 
PPAV.project_id
  , SUM(NVL(PPLD.pc_raw_cost,0)) current_approved
  , 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
  --AND PPAV.project_id                       = PTV.project_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 ('XXC_PROJECT_MANAGER_JOB','XXC_PROJECT_INQUIRY_JOB','XXC_PROJECT_ACCOUNTANT_JOB','XXC_PROJECT_ACCOUNTING_MANAGER_JOB')
	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.project_id
, PTV.task_id
, 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.PRJ_CURRENCY_CODE
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) = 'COMMON 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.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.project_id
, PTV.task_id

UNION ALL

SELECT 
  PPAV.project_id
, PTV.task_id
, (SUM(PCT.additional_info3)*CONV_TBL.CONVERSION_RATE) UD3
--,CONV_TBL.CONVERSION_RATE
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.FROM_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.PRJ_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) = 'COMMON 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.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'))
--AND PCT.DENOM_CURRENCY_CODE!=PCT.PRJ_CURRENCY_CODE
--AND PCT.EXPENDITURE_ITEM_DATE= :P_DATE

GROUP BY 
  PPAV.project_id
, PTV.task_id
,CONV_TBL.CONVERSION_RATE
)
,PROJ_PARAM AS 
(
select
SUBSTR(PPAV.SEGMENT1 ,8,2) proj_year
, PPAV.project_id
, PPAV.attribute2 selling
, PPAV.attribute1 location
--, SUBSTR(PTV.service_type_code,1,4) brand
FROM pjf_projects_all_vl PPAV
WHERE 1=1
AND (SUBSTR(PPAV.SEGMENT1 ,8,2)                              >= (:P_YEAR_FROM)
OR  'All'                					IN (:P_YEAR_FROM||'All'))
AND (SUBSTR(PPAV.SEGMENT1 ,8,2)                            <= (:P_YEAR_TO)
OR  'All'                					IN (:P_YEAR_TO||'All'))
AND (PPAV.attribute2		                    >= (:P_SELLING_FROM)
OR  'All'                			    IN (:P_SELLING_FROM||'All'))
AND (PPAV.attribute2             		        <= (:P_SELLING_TO)
OR  'All'                			    IN (:P_SELLING_TO||'All'))
AND (PPAV.attribute1		                    >= (:P_GL_LOC_FROM)
OR  'All'                			    IN (:P_GL_LOC_FROM||'All'))
AND (PPAV.attribute1            		        <= (:P_GL_LOC_TO)
OR  'All'                			    IN (:P_GL_LOC_TO||'All'))

)

SELECT service_type_code
, proj_num
, proj_name
, task_number
, task_name
, manufacturer_name
, tonnage
, item_complex
, proj_cur
, expense_amt
, proj_status
, cip
, cost_interfaced_to_fa
, GL_CIP
, capitalized
, PA_actual
, accrual_amt
, commitments
, total_spent_Commit
, current_approved_budget
, var
, var_per
, var_per_test
, budget_status
, subtotal_total_spend
, subtotal_budget
, subtotal_var_per

, (CASE WHEN subtotal_var_per < 100 then 'Under Budget' 
       when subtotal_var_per  = 100 then 'At Budget'
       WHEN item_complex IN ('1','2','3') AND (subtotal_var_per > 100 AND subtotal_var_per < 105) THEN 'Over Budget'
       WHEN item_complex IN ('4','5') AND (subtotal_var_per > 100 AND subtotal_var_per < 110) THEN 'Over Budget'	
       WHEN item_complex IN ('1','2','3') AND subtotal_var_per > 105  THEN 'Policy Violation'	
       WHEN item_complex IN ('4','5') AND subtotal_var_per > 110 THEN 'Policy Violation'	
   ELSE NULL END) subtotal_budget_status
   
, CLASS_CODE
, selling
, location
, brand
, TASK_ID
, R

FROM (

SELECT service_type_code
, proj_num
, proj_name
, task_number
, task_name
, manufacturer_name
, tonnage
, item_complex
, proj_cur
, expense_amt
, proj_status
, cip
, cost_interfaced_to_fa
, GL_CIP
, capitalized
, PA_actual
, accrual_amt
, commitments
, total_spent_Commit
, current_approved_budget
, var
, var_per
, var_per*100 var_per_test

, (CASE WHEN var_per*100 < 100 then 'Under Budget' 
       when var_per*100  = 100 then 'At Budget'
       WHEN item_complex IN ('1','2','3') AND (var_per*100 > 100 AND var_per*100 < 105) THEN 'Over Budget'
       WHEN item_complex IN ('4','5') AND (var_per*100 > 100 AND var_per*100 < 110) THEN 'Over Budget'	
       WHEN item_complex IN ('1','2','3') AND var_per*100 > 105  THEN 'Policy Violation'	
       WHEN item_complex IN ('4','5') AND var_per*100 > 110 THEN 'Policy Violation'	
   ELSE NULL END)	   budget_status
   
, SUM(total_spent_Commit) OVER (PARTITION BY proj_num, service_type_code) subtotal_total_spend
, SUM(current_approved_budget) OVER (PARTITION BY proj_num, service_type_code) subtotal_budget

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

, selling
, location
, brand
, TASK_ID
, R

FROM (
SELECT service_type_code
, proj_num
, proj_id
, proj_name
, task_number
, task_name
, task_id
, SUM(NVL(Expense_amt,0)) Expense_amt
, SUM(NVL(PA_accrual_amt,0)) accrual_amt
, item_complex
, proj_cur
, proj_status
, NVL(commitments,0) commitments
, NVL(current_approved_budget,0) current_approved_budget
, SUM(NVL(cip_cost_amount,0)) cip
, NVL(cost_interfaced_to_fa,0) cost_interfaced_to_fa
, SUM(NVL(cip_cost_amount,0))-NVL(cost_interfaced_to_fa,0) GL_CIP
, NVL(cost_interfaced_to_fa,0) capitalized
, manufacturer_name
, tonnage

, SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_interfaced_to_fa,0)) + NVL(cost_interfaced_to_fa,0) PA_actual

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

, ((SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_interfaced_to_fa,0)) + NVL(cost_interfaced_to_fa,0))-SUM(NVL(PA_accrual_amt,0))+NVL(commitments,0)) -NVL(current_approved_budget,0) var

, 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_interfaced_to_fa,0)) + NVL(cost_interfaced_to_fa,0))-SUM(NVL(PA_accrual_amt,0))+NVL(commitments,0))/NVL(current_approved_budget,0)),10) END  var_per

, LEGAL_ENTITY_IDENTIFIER
, LEGAL_ENTITY_ID
, CLASS_CODE
, project_status_code
, proj_year
, selling
, location
, brand
, R

FROM (
/*PROJECT LEVEL*/
SELECT /*+ PARALLEL(48) */
PTVV.service_type_code
, PPAV.segment1 proj_num
, PPAV.project_id proj_id
, PPAV.name proj_name
, TO_NUMBER('') task_number
, '' task_name
, PTV.PROJ_ELEMENT_ID task_id
, NULL Expense_amt
, NULL PA_accrual_amt

, ( SELECT DISTINCT  PCCV.CLASS_CODE 
   FROM PJF_CLASS_CATEGORIES_VL PCCB,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES_V PPC,
	PJF_PROJECTS_ALL_VL PPAV1
	WHERE PPAV.PROJECT_ID = PPAV1.PROJECT_ID
	 AND PPAV1.PROJECT_ID = PPC.PROJECT_ID
	AND PPC.CLASS_CATEGORY_ID=PCCB.CLASS_CATEGORY_ID
	AND PCCB.CLASS_CATEGORY_ID=PCCV.CLASS_CATEGORY_ID
	AND PPC.CLASS_CODE_ID= PCCV.CLASS_CODE_ID
	AND UPPER(PCCB.CLASS_CATEGORY) IN ( 'ITEM COMPLEXITY' )
   ) item_complex
   
, PPAV.project_currency_code proj_cur
, PPSV.project_status_name proj_status
, LTG.UD3 commitments
, NVL(CB.current_approved,0) current_approved_budget
, null cip_cost_amount
, ASTB.cost_interfaced_to_fa
, ASTB.manufacturer_name
, ASTB.tonnage
, XEP.LEGAL_ENTITY_IDENTIFIER
, XEP.LEGAL_ENTITY_ID
, PCCV.CLASS_CODE
, PPAV.project_status_code 
, SUBSTR(PPAV.SEGMENT1 ,8,2) proj_year
, null trx 
, null line_num 
, PPAV.attribute2 selling
, PPAV.attribute1 location
, SUBSTR(PTVV.service_type_code,1,4) brand
, '1' R

FROM pjf_projects_all_vl PPAV
, pjf_project_statuses_vl PPSV
, PJF_PROJ_ELEMENTS_B PTV
, LTG_COMMITMENT LTG
, XLE_ENTITY_PROFILES XEP 
, pjf_project_classes PPC
, pjf_class_codes_vl PCCV
, pjf_class_categories_vl PCCB
, SEC_TBL ST
, current_budget CB 
, ASSET_TBL ASTB
, PROJ_PARAM PP
, PJF_PROJ_ELEMENTS_TL PPET
, pjf_tasks_v PTVV
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.PROJ_ELEMENT_ID
AND PPAV.LEGAL_ENTITY_ID 				  = XEP.LEGAL_ENTITY_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 ('TER','MER')
AND ST.ORGANIZATION_ID						= PPAV.ORG_ID  -- FUT Test
AND CB.project_id = PPAV.project_id
AND CB.task_id = PTV.PROJ_ELEMENT_ID
AND CB.task_id= PPET.PROJ_ELEMENT_ID
AND PPET.LANGUAGE='US'
AND PPAV.project_id = ASTB.project_id(+)
AND PTV.PROJ_ELEMENT_ID = ASTB.task_id(+)
AND PPAV.project_id                       = PP.project_id
AND PTV.PROJ_ELEMENT_ID =PTVV.parent_task_id
AND UPPER(PTV.OBJECT_TYPE)='PJF_STRUCTURES'

UNION

/*PARENT TASK LEVEL*/
SELECT /*+ PARALLEL(48) */
PTV.service_type_code
, PPAV.segment1 proj_num
, PPAV.project_id proj_id
, PPAV.name proj_name
, PTV.task_number
, PTV.task_name
, PTV.task_id
, NULL Expense_amt
, NULL PA_accrual_amt
, ( SELECT DISTINCT  PCCV.CLASS_CODE 
   FROM PJF_CLASS_CATEGORIES_VL PCCB,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES_V PPC,
	PJF_PROJECTS_ALL_VL PPAV1
	WHERE PPAV.PROJECT_ID = PPAV1.PROJECT_ID
	 AND PPAV1.PROJECT_ID = PPC.PROJECT_ID
	AND PPC.CLASS_CATEGORY_ID=PCCB.CLASS_CATEGORY_ID
	AND PCCB.CLASS_CATEGORY_ID=PCCV.CLASS_CATEGORY_ID
	AND PPC.CLASS_CODE_ID= PCCV.CLASS_CODE_ID
	AND UPPER(PCCB.CLASS_CATEGORY) IN ( 'ITEM COMPLEXITY' )
   ) item_complex
   
, PPAV.project_currency_code proj_cur
, PPSV.project_status_name proj_status
, LTG.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
, null cip_cost_amount
, ASTB.cost_interfaced_to_fa
, ASTB.manufacturer_name
, ASTB.tonnage
, XEP.LEGAL_ENTITY_IDENTIFIER
, XEP.LEGAL_ENTITY_ID
, PCCV.CLASS_CODE
, PPAV.project_status_code 
, SUBSTR(PPAV.SEGMENT1 ,8,2) proj_year
, null trx 
, null line_num 
, PPAV.attribute2 selling
, PPAV.attribute1 location
, SUBSTR(PTV.service_type_code,1,4) brand
, '2' R

FROM pjf_projects_all_vl PPAV
, pjf_project_statuses_vl PPSV
, pjf_tasks_v PTV
, LTG_COMMITMENT LTG
, XLE_ENTITY_PROFILES XEP 
, pjf_project_classes PPC
, pjf_class_codes_vl PCCV
, pjf_class_categories_vl PCCB
, SEC_TBL ST
, current_budget CB 
, ASSET_TBL ASTB
, PROJ_PARAM PP
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 PPAV.LEGAL_ENTITY_ID 				  = XEP.LEGAL_ENTITY_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 ('TER','MER')
AND ST.ORGANIZATION_ID						= PPAV.ORG_ID  -- FUT Test
AND CB.project_id = PPAV.project_id
AND CB.task_id = PTV.parent_task_id
AND PTV.WBS_LEVEL='1'
AND PPAV.project_id = ASTB.project_id(+)
AND PTV.task_id = ASTB.task_id(+)
AND PPAV.project_id                       = PP.project_id

UNION 

/*ACTUALS*/
SELECT /*+ PARALLEL(48) */
PTV.service_type_code
, PPAV.segment1 proj_num
, PPAV.project_id proj_id
, PPAV.name proj_name
, PTV.task_number
, PTV.task_name
, PTV.task_id
, (CASE WHEN PEIA.capitalizable_flag = 'N' THEN PCDLA.project_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.period_accrual_flag = 'Y' THEN PCDLA.project_raw_cost END) PA_accrual_amt

, ( SELECT DISTINCT  PCCV.CLASS_CODE 
   FROM PJF_CLASS_CATEGORIES_VL PCCB,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES_V PPC,
	PJF_PROJECTS_ALL_VL PPAV1
	WHERE PPAV.PROJECT_ID = PPAV1.PROJECT_ID
	 AND PPAV1.PROJECT_ID = PPC.PROJECT_ID
	AND PPC.CLASS_CATEGORY_ID=PCCB.CLASS_CATEGORY_ID
	AND PCCB.CLASS_CATEGORY_ID=PCCV.CLASS_CATEGORY_ID
	AND PPC.CLASS_CODE_ID= PCCV.CLASS_CODE_ID
	AND UPPER(PCCB.CLASS_CATEGORY) IN ( 'ITEM COMPLEXITY' )
   ) item_complex
   
, PPAV.project_currency_code proj_cur
, PPSV.project_status_name proj_status

,LTG.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
	 
, (CASE WHEN PEIA.capitalizable_flag = 'Y' THEN PCDLA.project_raw_cost END) cip_cost_amount
, ASTB.cost_interfaced_to_fa
, ASTB.manufacturer_name
, ASTB.tonnage
, XEP.LEGAL_ENTITY_IDENTIFIER
, XEP.LEGAL_ENTITY_ID
, PCCV.CLASS_CODE
, PPAV.project_status_code 
, SUBSTR(PPAV.SEGMENT1 ,8,2) proj_year
, PEIA.expenditure_item_id trx
, PCDLA.line_num
, PPAV.attribute2 selling
, PPAV.attribute1 location
, SUBSTR(PTV.service_type_code,1,4) brand
, '2' R

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
, XLE_ENTITY_PROFILES XEP 
, pjf_project_classes PPC
, pjf_class_codes_vl PCCV
, pjf_class_categories_vl PCCB 
, SEC_TBL ST
, ASSET_TBL ASTB
, PROJ_PARAM PP
, LTG_COMMITMENT LTG

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 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                       = 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 PPAV.ORG_ID							  = PPPAV.ORG_ID
AND PCDLA.RECVR_PA_DATE					  BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
AND PPAV.LEGAL_ENTITY_ID 				  = XEP.LEGAL_ENTITY_ID
AND LTG.project_id(+) = PPAV.project_id
AND LTG.task_id(+) = PTV.task_id

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'))

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 ('TER','MER')
AND PPAV.project_id = ASTB.project_id(+)
AND PTV.task_id = ASTB.task_id(+)
AND PPAV.project_id                       = PP.project_id

UNION 
/*BUDGET*/
SELECT /*+ PARALLEL(48) */
PTV.service_type_code
, PPAV.segment1 proj_num
, PPAV.project_id proj_id
, PPAV.name proj_name
, PTV.task_number
, PTV.task_name
, PTV.task_id
, NULL Expense_amt
, NULL PA_accrual_amt
, ( SELECT DISTINCT  PCCV.CLASS_CODE 
   FROM PJF_CLASS_CATEGORIES_VL PCCB,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES_V PPC,
	PJF_PROJECTS_ALL_VL PPAV1
	WHERE PPAV.PROJECT_ID = PPAV1.PROJECT_ID
	 AND PPAV1.PROJECT_ID = PPC.PROJECT_ID
	AND PPC.CLASS_CATEGORY_ID=PCCB.CLASS_CATEGORY_ID
	AND PCCB.CLASS_CATEGORY_ID=PCCV.CLASS_CATEGORY_ID
	AND PPC.CLASS_CODE_ID= PCCV.CLASS_CODE_ID
	AND UPPER(PCCB.CLASS_CATEGORY) IN ( 'ITEM COMPLEXITY' )
   ) item_complex
   
, PPAV.project_currency_code proj_cur
, PPSV.project_status_name proj_status
, LTG.UD3 commitments
, NVL(CB.current_approved,0) current_approved_budget
, null cip_cost_amount
, ASTB.cost_interfaced_to_fa
, ASTB.manufacturer_name
, ASTB.tonnage
, XEP.LEGAL_ENTITY_IDENTIFIER
, XEP.LEGAL_ENTITY_ID
, PCCV.CLASS_CODE
, PPAV.project_status_code 
, SUBSTR(PPAV.SEGMENT1 ,8,2) proj_year
, null trx 
, null line_num 
, PPAV.attribute2 selling
, PPAV.attribute1 location
, SUBSTR(PTV.service_type_code,1,4) brand
, '2' R

FROM pjf_projects_all_vl PPAV
, pjf_project_statuses_vl PPSV
, pjf_tasks_v PTV
, LTG_COMMITMENT LTG
, XLE_ENTITY_PROFILES XEP 
, pjf_project_classes PPC
, pjf_class_codes_vl PCCV
, pjf_class_categories_vl PCCB
, SEC_TBL ST
, current_budget CB 
, ASSET_TBL ASTB
, PROJ_PARAM PP 

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 PPAV.LEGAL_ENTITY_ID 				  = XEP.LEGAL_ENTITY_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 ('TER','MER')
AND ST.ORGANIZATION_ID						= PPAV.ORG_ID  -- FUT Test
AND CB.project_id = PPAV.project_id
AND CB.task_id = PTV.task_id
AND PPAV.project_id = ASTB.project_id(+)
AND PTV.task_id = ASTB.task_id(+)
AND PPAV.project_id                       = PP.project_id

) 

WHERE 1=1

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 (project_status_code		        IN (:P_PROJ_STATUS)
OR  'All'                					IN (:P_PROJ_STATUS||'All'))

AND (brand		                        >= (:P_GL_BRAND_FROM)
OR  'All'                		        IN (:P_GL_BRAND_FROM||'All'))
AND (brand            		            <= (:P_GL_BRAND_TO)
OR  'All'                				IN (:P_GL_BRAND_TO||'All'))

GROUP BY service_type_code
, proj_num
, proj_id
, proj_name
, task_number
, task_name
, task_id
, item_complex
, proj_cur
, proj_status
, commitments
, current_approved_budget
, cost_interfaced_to_fa
, manufacturer_name
, tonnage
, LEGAL_ENTITY_IDENTIFIER
, LEGAL_ENTITY_ID
, CLASS_CODE
, project_status_code
, proj_year
, selling
, location
, brand
, R
)
WHERE 1=1

)
WHERE 1=1
ORDER BY
---service_type_code
 proj_num
, R
, task_ID]]>

:P_DATE :P_GL_BRAND_FROM :P_GL_BRAND_TO :P_GL_LOC_FROM :P_GL_LOC_TO :P_GL_PERIOD_FROM :P_GL_PERIOD_TO :P_LEGAL_ENTITY :P_PROJ_NUM :P_PROJ_STATUS :P_PROJ_TYPE_CLASS :P_SELLING_FROM :P_SELLING_TO :P_YEAR_FROM :P_YEAR_TO :XDO_USER_NAME

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
PJF_TASKS_V	dimension	dimension
PJC_EXP_ITEMS_ALL	dimension	dimension
PJC_COST_DIST_LINES_ALL	dimension	dimension
PJO_PLAN_LINE_DETAILS	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
PJF_TASKS_V	Setup / configuration table — joined for reference, not exposed for analytics
PJC_EXP_ITEMS_ALL	98	8
PJC_COST_DIST_LINES_ALL	38	8
PJO_PLAN_LINE_DETAILS	12	2
GL_CODE_COMBINATIONS	7	61

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.