Projects Transactions Dashboard Report

Projects

Project cost transactions from across the subledgers — supplier invoices, expenses, and labor — tied to project and task, so finance can see what's hitting each project and catch misclassified or uncosted transactions.

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

Sample build · illustrative

Filters

Gl Cc From

Sample

Gl Cc To

Sample

Gl Loc From

Sample

Gl Loc To

Sample

Gl Period From

FEB-26

Gl Period To

FEB-26

9,400

Cost transactions

$41.0M

Project cost

218

Uncosted

Project	Task	Source	Transaction	Supplier	Amount	Cost Status
Sample	Sample	Sample	Sample	Acme Industrial	$1,240,500.00	$1,240,500.00
—	—	—	—	Northwind Trading	$842,150.75	$842,150.75
Sample	Sample	Sample	Sample	Globex Holdings	$96,400.00	$96,400.00
—	—	—	—	Initech LLC	$1,005,233.10	$1,005,233.10
Sample	Sample	Sample	Sample	Umbrella Corp	$58,720.40	$58,720.40
Sample	Sample	Sample	Sample	Acme Industrial	$1,240,500.00	$1,240,500.00

AI Analyst · active

reading

The report pulls project costs from AP invoices and expense items via PJC_EXP_ITEMS_ALL, tied to each project and task.

flag

218 transactions are uncosted — imported to the project but not yet run through cost distribution, so they aren't in project actuals or capitalizable cost yet.

root cause & next step

Run cost distribution for the 218; an uncosted backlog usually means a costing rule or rate schedule is missing for one expenditure type.

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[/*----this query is used for Project Transactions ...... Transaction by Project and Transaction by Vendor tabs*/

WITH INV_DET AS 
(
SELECT 
PPA.project_id
, ppa.segment1
, ppe.ELEMENT_NUMBER
, ppe.proj_element_id
, AIL.ATTRIBUTE2
, AIA.INVOICE_NUM
, PS.VENDOR_NAME
, PS.SEGMENT1 VENDOR_NUMBER
, AID.DISTRIBUTION_LINE_NUMBER 
, AID.DESCRIPTION,PEI.EXPENDITURE_ITEM_ID
, AIL.LINE_NUMBER,AID.INVOICE_DISTRIBUTION_ID

FROM ap_invoice_lines_all AIL
, pjf_projects_all_vl PPA
, PJF_PROJ_ELEMENTS_VL PPE
, AP_INVOICES_ALL AIA
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, POZ_SUPPLIERS_V PS
, pjc_exp_items_all pei
, pjf_txn_sources_vl PTSV
, pjf_txn_document_vl PTDV

WHERE 1 = 1
--AND PPA.project_id = AID.pjc_project_id removeing Join as per Defect REF-28414
-- Changing Join as per Defect REF-28414
and pei.original_header_id=AIA.invoice_id
AND PEI.original_dist_id= AID.invoice_distribution_id 
and ppa.project_id=ppe.project_id
and AIA.INVOICE_ID=AIL.INVOICE_ID
and AIA.INVOICE_ID=AID.INVOICE_ID
and AIL.LINE_NUMBER=AID.INVOICE_LINE_NUMBER
--and ppe.proj_element_id=aiD.pjc_task_id
and ps.VENDOR_ID=pei.VENDOR_ID
and pei.PROJECT_ID=ppa.PROJECT_ID
and pei.TASK_ID=ppe.proj_element_id 

AND PEI.transaction_source_id = PTSV.transaction_source_id	   
AND PEI.document_id              = PTDV.document_id
AND PTSV.transaction_source_id    = PTDV.transaction_source_id
AND UPPER(PTSV.user_transaction_source) IN ('ORACLE FUSION PAYABLES') 
AND UPPER(PTDV.DOCUMENT_NAME) IN ('SUPPLIER INVOICE')

--AND PPA.SEGMENT1 IN ('ADV342124E20241')
)

, XLA_TBL AS
(
SELECT /*+ PARALLEL(48) */ DISTINCT
XDL.source_distribution_id_num_1
  , XDL.source_distribution_id_num_2
  , XDL.source_distribution_type
  , GCC.segment1
  , GCC.segment2
  , GCC.segment3
  , GCC.segment4
  , GCC.segment5
  , GCC.segment6
  , GCC.segment7
  , GCC.segment8
  , GCC.segment9
  ,GJB.NAME BATCH_NAME
FROM xla_distribution_links XDL
  , xla_ae_lines XAL
  , gl_code_combinations GCC
  , gl_ledgers GL
  
  ,GL_IMPORT_REFERENCES GIR
  ,GL_JE_BATCHES GJB
  
WHERE XDL.ae_header_id        = XAL.ae_header_id
  AND XDL.ae_line_num         = XAL.ae_line_num
  AND XDL.application_id      IN (10036,200)
  AND XDL.application_id      = XAL.application_id
  AND XAL.ledger_id           = GL.Ledger_id
  AND XAL.code_combination_id = GCC.code_combination_id
  AND GL.chart_of_accounts_id = GCC.chart_of_accounts_id
  AND XAL.code_combination_id <> -1
  AND UPPER(XAL.accounting_class_code) IN ('RAW_COST','SELF_ASSESSED_TAX','ITEM EXPENSE','NRTAX','FREIGHT','MISCELLANEOUS EXPENSE','RTAX') 
  AND UPPER(GL.ledger_category_code) = 'PRIMARY'  
  AND GIR.GL_SL_LINK_ID(+)      = XAL.GL_SL_LINK_ID	
  AND GIR.GL_SL_LINK_TABLE(+)	= XAL.GL_SL_LINK_TABLE
  AND GIR.JE_BATCH_ID           = GJB.JE_BATCH_ID(+)
 
GROUP BY XDL.source_distribution_id_num_1
  , XDL.source_distribution_id_num_2
  , XDL.source_distribution_type
  , GCC.segment1
  , GCC.segment2
  , GCC.segment3
  , GCC.segment4
  , GCC.segment5
  , GCC.segment6
  , GCC.segment7
  , GCC.segment8
  , GCC.segment9
  ,GJB.NAME
)

, 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
) 
, ASSET_TBL AS
(
SELECT 
PPAV.project_id
, PTV.task_id
, PPAA.ASSET_NUMBER
, PPAA.ASSET_Name
, fcv.segment1 Asset_Major_Category
, fcv.segment2 Asset_Minor_Category

FROM pjf_projects_all_vl PPAV
, pjf_tasks_v PTV
, pjc_prj_assets_all PPAA
, PJC_PRJ_ASSET_LNS_ALL PPALA
, FA_CATEGORIES_VL FCV

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 PPAV.PROJECT_ID 					  = PPALA.PROJECT_ID
AND PPALA.task_id                         = PTV.task_id

and PPAA.ASSET_CATEGORY_ID = fcv.CATEGORY_ID

--AND PPAV.SEGMENT1 IN ('CER Project 02','ADV342124E20241')

GROUP BY PPAV.project_id
, PTV.task_id
, PPAA.ASSET_NUMBER
, PPAA.ASSET_Name
, fcv.segment1 
, fcv.segment2 
)

SELECT 
PROJECT_ID
, ORG_NAME
, PROJECT_NAME
, PROJECT_NUMBER
, TASK_NUMBER_TOP
, TASK_NAME_TOP
, TASK_NUMBER
, TASK_NAME
, GL_PERIOD
, GL_DATE
, EXPENDITURE_ITEM_DATE
, PROJECT_YEAR
, VENDOR_NAME
, VENDOR_NUMBER
, PO_NUMBER
, INVOICE_NUMBER
, Cost_Dist_Line_Number
, Cost_Dist_Line_Currency_Code
, sum(Cost_Dist_Line_Raw_Cost) Cost_Dist_Line_Raw_Cost
, Cost_Dist_Line_Project_Exchange_Rate
, sum(NVL(Cost_Dist_Line_Amount,0)) Cost_Dist_Line_Amount
, Project_Functional_Currency_Code
, EXPENDITURE_TYPE
, GL_STRING
, GL_BATCH_NAME
, EXPENDITURE_COMMENT
, ASSORTMENT
, transaction_source
, EXPENDITURE_ID
, EXPENDITURE_BATCH
, ITEM_COMPLEXITY
, ASSET_NUMBER
, ASSET_Name
, Asset_Major_Category
, Asset_Minor_Category
, NET_ZERO_ADJUSTMENT_FLAG 

FROM 
(
SELECT 
PPA.PROJECT_ID
, PPA.ORG_ID
, HOUF.NAME ORG_NAME
, PPA.NAME PROJECT_NAME
, PPA.SEGMENT1 PROJECT_NUMBER
, PPE1.ELEMENT_NUMBER TASK_NUMBER_TOP
, PPE1.NAME TASK_NAME_TOP
, PPE.ELEMENT_NUMBER TASK_NUMBER
, PPE.NAME TASK_NAME
--, NVL(PCDLA.PRVDR_PA_PERIOD_NAME,PCDLA.RECVR_PA_PERIOD_NAME) GL_PERIOD
, PPPAV.PERIOD_NAME GL_PERIOD
, NVL(PCDLA.PRVDR_PA_DATE, PCDLA.RECVR_PA_DATE) GL_DATE
, PEI.EXPENDITURE_ITEM_DATE	
, SUBSTR(PPA.SEGMENT1 ,8,2)  PROJECT_YEAR
, ID.VENDOR_NAME
, ID.VENDOR_NUMBER
, ID.ATTRIBUTE2 PO_NUMBER
, ID.INVOICE_NUM INVOICE_NUMBER
, ID.DISTRIBUTION_LINE_NUMBER Cost_Dist_Line_Number
, PCDLA.DENOM_CURRENCY_CODE  Cost_Dist_Line_Currency_Code
, nvl(PCDLA.DENOM_RAW_COST,0) Cost_Dist_Line_Raw_Cost
/*---As per the CR-REF-294544----*/
--, ROUND(PCDLA.PROJECT_EXCHANGE_RATE,2) Cost_Dist_Line_Project_Exchange_Rate -- Project exchange rate
--, ROUND(PCDLA.ACCT_EXCHANGE_RATE,2) Cost_Dist_Line_Project_Exchange_Rate -- Provider Ledger Currency Conversion Rate
, ROUND(PCDLA.PROJFUNC_COST_EXCHANGE_RATE,2) Cost_Dist_Line_Project_Exchange_Rate
--, nvl(PCDLA.project_raw_cost,0) Cost_Dist_Line_Amount
, nvl(PCDLA.ACCT_RAW_COST,0) Cost_Dist_Line_Amount  -- Raw Cost in Provider Ledger Currency
--, PCDLA.PROJECT_CURRENCY_CODE Project_Functional_Currency_Code
, PCDLA.ACCT_CURRENCY_CODE Project_Functional_Currency_Code  -- Provider Ledger Currency
/*---CR-REF-294544 END ----*/
, PET.EXPENDITURE_TYPE_NAME EXPENDITURE_TYPE
/*, nvl(nvl(CASE WHEN NVL(GCC.SEGMENT4,'-1')<>'-1' THEN GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8 || '.' || GCC.SEGMENT9 ELSE NULL END 
,CASE WHEN NVL(AP_XT.SEGMENT4,'-1')<>'-1' THEN AP_XT.SEGMENT1 || '.' || AP_XT.SEGMENT2 || '.' || AP_XT.SEGMENT3 || '.' || AP_XT.SEGMENT4 || '.' || AP_XT.SEGMENT5 || '.' || AP_XT.SEGMENT6 || '.' || AP_XT.SEGMENT7 || '.' || AP_XT.SEGMENT8 || '.' || AP_XT.SEGMENT9 ELSE NULL END ) 
, CASE WHEN NVL(PRJ_XT.SEGMENT4,'-1')<>'-1' THEN PRJ_XT.SEGMENT1 || '.' || PRJ_XT.SEGMENT2 || '.' || PRJ_XT.SEGMENT3 || '.' || PRJ_XT.SEGMENT4 || '.' || PRJ_XT.SEGMENT5 || '.' || PRJ_XT.SEGMENT6 || '.' || PRJ_XT.SEGMENT7 || '.' || PRJ_XT.SEGMENT8 || '.' || PRJ_XT.SEGMENT9 ELSE NULL END )  GL_String*/
, nvl(nvl(CASE WHEN NVL(PRJ_XT.SEGMENT4,'-1')<>'-1' THEN PRJ_XT.SEGMENT1 || '.' || PRJ_XT.SEGMENT2 || '.' || PRJ_XT.SEGMENT3 || '.' || PRJ_XT.SEGMENT4 || '.' || PRJ_XT.SEGMENT5 || '.' || PRJ_XT.SEGMENT6 || '.' || PRJ_XT.SEGMENT7 || '.' || PRJ_XT.SEGMENT8 || '.' || PRJ_XT.SEGMENT9 ELSE NULL END
,CASE WHEN NVL(AP_XT.SEGMENT4,'-1')<>'-1' THEN AP_XT.SEGMENT1 || '.' || AP_XT.SEGMENT2 || '.' || AP_XT.SEGMENT3 || '.' || AP_XT.SEGMENT4 || '.' || AP_XT.SEGMENT5 || '.' || AP_XT.SEGMENT6 || '.' || AP_XT.SEGMENT7 || '.' || AP_XT.SEGMENT8 || '.' || AP_XT.SEGMENT9 ELSE NULL END ) 
, CASE WHEN NVL(GCC.SEGMENT4,'-1')<>'-1' THEN GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8 || '.' || GCC.SEGMENT9 ELSE NULL END 
)  GL_String
, nvl(AP_XT.BATCH_NAME ,PRJ_XT.BATCH_NAME) GL_BATCH_NAME
 --,PCDLA.ACCT_EVENT_ID
, NVL(ID.DESCRIPTION, PEI.EXPENDITURE_COMMENT)  EXPENDITURE_COMMENT
, PPA.ATTRIBUTE3 ASSORTMENT
, PTSV.user_transaction_source transaction_source
, PEI.EXPENDITURE_ITEM_ID EXPENDITURE_ID
, PEI.USER_BATCH_NAME EXPENDITURE_BATCH
--, CASE WHEN UPPER(PCCB.class_category) IN ('ITEM COMPLEXITY') THEN PCCV.CLASS_CODE END ITEM_COMPLEXITY

, ( SELECT DISTINCT  PCCO.CLASS_CODE 
   FROM PJF_CLASS_CATEGORIES_VL PCCA,
	PJF_CLASS_CODES_VL PCCO,
	PJF_PROJECT_CLASSES_V PPC,
	PJF_PROJECTS_ALL_VL PPA1
	WHERE
	PPA.PROJECT_ID=PPA1.PROJECT_ID
	 AND PPA1.PROJECT_ID= PPC.PROJECT_ID
	AND PPC.CLASS_CATEGORY_ID=PCCA.CLASS_CATEGORY_ID
	AND PCCA.CLASS_CATEGORY_ID=PCCO.CLASS_CATEGORY_ID
	AND PPC.CLASS_CODE_ID= PCCO.CLASS_CODE_ID
	AND UPPER(PCCA.CLASS_CATEGORY) IN ( 'ITEM COMPLEXITY' )
   ) ITEM_COMPLEXITY

, ASTB.asset_number
, ASTB.asset_name
, ASTB.Asset_Major_Category
, ASTB.Asset_Minor_Category

, PEI.NET_ZERO_ADJUSTMENT_FLAG NET_ZERO_ADJUSTMENT_FLAG

--, NVL(NVL(PRJ_XT.SEGMENT1,AP_XT.SEGMENT1),GCC.SEGMENT1) gl_entity
, NVL(NVL(PRJ_XT.SEGMENT2,AP_XT.SEGMENT2),GCC.SEGMENT2) gl_selling
, NVL(NVL(PRJ_XT.SEGMENT3,AP_XT.SEGMENT3),GCC.SEGMENT3) gl_cc
, NVL(NVL(PRJ_XT.SEGMENT5,AP_XT.SEGMENT5),GCC.SEGMENT5) gl_loc
--, NVL(NVL(PRJ_XT.SEGMENT6,AP_XT.SEGMENT6),GCC.SEGMENT6) gl_brand

FROM PJF_PROJECTS_ALL_VL PPA
, pjf_project_statuses_vl PPSV
, PJF_PROJECT_TYPES_VL PPT
, PJF_PROJ_ELEMENTS_VL PPE
, pjc_exp_items_all pei
, PJF_EXP_TYPES_VL PET
, pjc_cost_dist_lines_all PCDLA
, HR_ORGANIZATION_UNITS_F_TL HOUF
, XLE_ENTITY_PROFILES XEP
, pjf_p_periods_all_v PPPAV
, INV_DET ID
	
, xla_tbl PRJ_XT
, xla_tbl AP_XT
, GL_CODE_COMBINATIONS GCC

, PJF_PROJ_ELEMENTS_VL PPE1

, pjf_txn_sources_vl PTSV
, pjf_txn_document_vl PTDV

, pjf_project_classes PPC
, pjf_class_codes_vl PCCV
, pjf_class_categories_vl PCCB

, SEC_TBL ST

, ASSET_TBL ASTB
	
WHERE 1 = 1
AND PPA.PROJECT_TYPE_ID 					= PPT.PROJECT_TYPE_ID
AND PPA.project_status_code                 = PPSV.project_status_code
AND PPA.PROJECT_ID 						    = PPE.PROJECT_ID

and ppa.PROJECT_ID                          = pei.PROJECT_ID
and ppe.proj_element_id                     = pei.TASK_ID 

AND PPA.project_id                  		= PCDLA.project_id
AND PPE.proj_element_id           		    = PCDLA.task_id
and pei.EXPENDITURE_ITEM_ID                 = PCDLA.EXPENDITURE_ITEM_ID
AND PEI.task_id                             = PCDLA.task_id     -- added

and pet.EXPENDITURE_TYPE_ID                 = pei.EXPENDITURE_TYPE_ID
AND PPA.ORG_ID							    = PPPAV.ORG_ID
AND PCDLA.RECVR_PA_DATE					    BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
AND PPA.ORG_ID 		                        = HOUF.ORGANIZATION_ID
AND SYSDATE 								BETWEEN HOUF.EFFECTIVE_START_DATE AND HOUF.EFFECTIVE_END_DATE
AND HOUF.LANGUAGE							= USERENV('LANG')
--AND PPT.ENABLE_CAPITALIZATION_FLAG 		= 'Y'
AND PPA.TEMPLATE_FLAG 						= 'N'

AND PPE1.PROJECT_ID                         = PPA.PROJECT_ID
AND PPE1.PROJ_ELEMENT_ID                    = PPE.DENORM_TOP_ELEMENT_ID
	
AND PPA.LEGAL_ENTITY_ID 					= XEP.LEGAL_ENTITY_ID

AND PEI.transaction_source_id            = PTSV.transaction_source_id	   
AND PEI.document_id                      = PTDV.document_id
AND PTSV.transaction_source_id           = PTDV.transaction_source_id

AND PPA.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 PPA.project_id = ASTB.project_id (+)
AND PPE.proj_element_id  = ASTB.task_id (+)
	
and ID.PROJECT_ID(+) = PPA.PROJECT_ID
AND ID.PROJ_ELEMENT_ID(+) = PPE.PROJ_ELEMENT_ID
and ID.EXPENDITURE_ITEM_ID(+) = PEI.EXPENDITURE_ITEM_ID 
	
	
AND PCDLA.raw_cost_dr_ccid                = GCC.code_combination_id(+)

AND PCDLA.expenditure_item_id             = PRJ_XT.source_distribution_id_num_1(+)
AND PCDLA.line_num                        = PRJ_XT.source_distribution_id_num_2(+)
AND PEI.original_dist_id                  = AP_XT.source_distribution_id_num_1(+)
AND AP_XT.source_distribution_type(+)     = 'AP_INV_DIST'
AND PRJ_XT.source_distribution_type(+)    = 'R'

AND AP_XT.source_distribution_id_num_1   = ID.INVOICE_DISTRIBUTION_ID(+)	

AND ST.ORGANIZATION_ID						= PPA.ORG_ID

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

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

AND (PPA.SEGMENT1		            		IN (:P_PROJ_NUM)
OR  'All'                					IN (:P_PROJ_NUM||'All'))

AND (PPSV.project_status_name		        IN (:P_PROJ_STATUS)
OR  'All'                					IN (:P_PROJ_STATUS||'All'))

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

 )
WHERE 1=1

/* AND (GL_PERIOD             >= (:P_GL_PERIOD_FROM)
OR 'All'                  IN (:P_GL_PERIOD_FROM||'All'))
AND (GL_PERIOD            <= (:P_GL_PERIOD_TO)
OR 'All'                  IN (:P_GL_PERIOD_TO||'All')) */

AND (gl_selling		                    >= (:P_SELLING_FROM)
OR  'All'                			    IN (:P_SELLING_FROM||'All'))
AND (gl_selling            		        <= (:P_SELLING_TO)
OR  'All'                			    IN (:P_SELLING_TO||'All'))

AND (gl_cc		                    >= (:P_GL_CC_FROM)
OR  'All'                			    IN (:P_GL_CC_FROM||'All'))
AND (gl_cc            		        <= (:P_GL_CC_TO)
OR  'All'                			    IN (:P_GL_CC_TO||'All'))

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

GROUP BY PROJECT_ID
,ORG_NAME
,PROJECT_NAME
,PROJECT_NUMBER
,TASK_NUMBER_TOP
,TASK_NAME_TOP
,TASK_NUMBER
,TASK_NAME
,GL_PERIOD
,GL_DATE
,EXPENDITURE_ITEM_DATE
,PROJECT_YEAR
,VENDOR_NAME
,VENDOR_NUMBER
,PO_NUMBER
,INVOICE_NUMBER
,Cost_Dist_Line_Number
,Cost_Dist_Line_Currency_Code

,Cost_Dist_Line_Project_Exchange_Rate

,Project_Functional_Currency_Code
,EXPENDITURE_TYPE
, GL_STRING
, GL_BATCH_NAME
,EXPENDITURE_COMMENT
,ASSORTMENT
,transaction_source
,EXPENDITURE_ID
,EXPENDITURE_BATCH
,ITEM_COMPLEXITY
,ASSET_NUMBER
,ASSET_Name
,Asset_Major_Category
,Asset_Minor_Category
,NET_ZERO_ADJUSTMENT_FLAG

/* ORDER BY PROJECT_NUMBER
,TASK_NUMBER
,EXPENDITURE_ID */]]>

:P_GL_CC_FROM :P_GL_CC_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 :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_PROJECTS_ALL_VL	dimension	dimension
PJF_PROJ_ELEMENTS_VL	dimension	dimension
AP_INVOICES_ALL	dimension	dimension
AP_INVOICE_DISTRIBUTIONS_ALL	dimension	dimension
Amount	measure	measure
Cost Status	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
PJC_EXP_ITEMS_ALL	98	8
PJF_PROJECTS_ALL_VL	31	67
PJF_PROJ_ELEMENTS_VL	106	65
AP_INVOICES_ALL	63	15
AP_INVOICE_DISTRIBUTIONS_ALL	59	11
POZ_SUPPLIERS_V	2	24

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.