Oracle Fusion · Projects
Projects Global vs Local CER Report
Projects
Capital expenditure requests (CER) compared at global and local levels — approved, committed, and spent against each capital request by classification, so capital governance can see authorization versus actual spend.
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 Global vs Local CER Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
Projects Global vs Local CER Report
Sample build · illustrativeFilters
Project Number
1003
Project Status
Validated
Project Year From
Sample
Project Year To
Sample
96
Capital requests
$84.00M
Approved capital
4
Over authorization
| Project | Cer Classification | Approved | Committed | Spent | Variance |
|---|---|---|---|---|---|
| Sample | Standard | Sample | Sample | Sample | Sample |
| — | Corporate | — | — | — | — |
| Sample | Standard | Sample | Sample | Sample | Sample |
| — | Default | — | — | — | — |
| Sample | Standard | Sample | Sample | Sample | Sample |
| Sample | Standard | Sample | Sample | Sample | Sample |
AI Analyst · active
reading
The report compares each CER's approved amount to committed and spent, by project classification.
flag
Four requests have spend above their approved authorization — capital committed without an approved increase.
root cause & next step
Obtain the authorization increase or halt the spend; capital over authorization is a governance breach auditors test.
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.
Show / hide SQL
--this query is used for ......
WITH GLOBAL_CER
AS (
SELECT DISTINCT
PPA.SEGMENT1, PPA.PROJECT_ID
FROM
PJF_PROJECTS_ALL_VL PPA,
PJF_CLASS_CATEGORIES_VL PCCA,
PJF_CLASS_CODES_VL PCCO,
PJF_PROJECT_CLASSES_V PPC
WHERE
PPA.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 (CASE WHEN PCCA.CLASS_CATEGORY IN ( 'Global CER' ) THEN PCCO.CLASS_CODE END) IN ('Y')
AND (PPA.SEGMENT1 IN (:P_PROJECT_NUMBER) OR 'All' IN ('All'||:P_PROJECT_NUMBER))
AND (PPA.PROJECT_STATUS_CODE IN (:P_PROJECT_STATUS) OR 'All' IN ('All'||:P_PROJECT_STATUS))
AND ((SUBSTR(PPA.SEGMENT1 ,8,2) ) BETWEEN nvl(:P_PROJECT_YEAR_FROM, SUBSTR(PPA.SEGMENT1 ,8,2) )
AND nvl(:P_PROJECT_YEAR_TO, SUBSTR(PPA.SEGMENT1 ,8,2) ))
),
PO_NUMG AS (
SELECT PPA.project_id
, AIL.ATTRIBUTE2
,(CASE WHEN aiA.INVOICE_CURRENCY_CODE='USD' THEN SUM(ail.AMOUNT) ELSE SUM(ail.AMOUNT)*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=aia.INVOICE_CURRENCY_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(sysdate)) END) ACTUALS_IN_USD
FROM ap_invoice_lines_all AIL
, pjf_projects_all_vl PPA
, AP_INVOICES_ALL AIA
, PJF_CLASS_CATEGORIES_VL PCCA,
PJF_CLASS_CODES_VL PCCO,
PJF_PROJECT_CLASSES_V PPC
WHERE PPA.project_id = AIL.pjc_project_id
and AIA.INVOICE_ID=AIL.INVOICE_ID
and PPA.PROJECT_ID= PPC.PROJECT_ID
AND AIL.attribute2 IS NOT NULL
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 (CASE WHEN PCCA.CLASS_CATEGORY IN ( 'Global CER' ) THEN PCCO.CLASS_CODE END) IN ('Y')
GROUP BY PPA.project_id
, AIL.ATTRIBUTE2
, AiA.INVOICE_CURRENCY_CODE
)
,
PO_NUML AS (
SELECT PPA.project_id
, AIL.ATTRIBUTE2
,(CASE WHEN aiA.INVOICE_CURRENCY_CODE='USD' THEN SUM(ail.AMOUNT) ELSE SUM(ail.AMOUNT)*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=aia.INVOICE_CURRENCY_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(sysdate)) END) ACTUALS_IN_USD
FROM ap_invoice_lines_all AIL
, pjf_projects_all_vl PPA
, AP_INVOICES_ALL AIA
, PJF_CLASS_CATEGORIES_VL PCCA,
PJF_CLASS_CODES_VL PCCO,
PJF_PROJECT_CLASSES_V PPC
WHERE PPA.project_id = AIL.pjc_project_id
and AIA.INVOICE_ID=AIL.INVOICE_ID
and PPA.PROJECT_ID= PPC.PROJECT_ID
AND AIL.attribute2 IS NOT NULL
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 NVL(PPA.ATTRIBUTE4, '-1')<>'-1'
AND (CASE WHEN PCCA.CLASS_CATEGORY IN ( 'Project Type Class' ) THEN PCCO.CLASS_CODE END) IN ('CER')
GROUP BY PPA.project_id
, AIL.ATTRIBUTE2
, AiA.INVOICE_CURRENCY_CODE
)
SELECT DISTINCT
GLOBAL_CER_PROJECT_NUMBER
,NVL(LOCAL_CER_PROJECT_NUMBER,'A') LOCAL_CER_PROJECT_NUMBER
, PROJECT_NAME
, PROJECT_MANAGER
,LEGAL_ENTITY
,LOCATION
,Functional_Currency
,PO_NUMBER
,MAX(NVL(ACTUALS_IN_USD,0)) ACTUALS_IN_USD
,MAX(Open_Commitments_in_USD) Open_Commitments_in_USD
,MAX(PO_Left_to_go) PO_Left_to_go
--,Budget_in_USD
FROM
(
SELECT
GLOBAL_CER_PROJECT_NUMBER
,LOCAL_CER_PROJECT_NUMBER
, PROJECT_NAME
, PROJECT_MANAGER
,LEGAL_ENTITY
,LOCATION
,Functional_Currency
,PO_NUMBER
,NVL(ACTUALS_IN_USD,0) ACTUALS_IN_USD
,NVL(Open_Commitments_in_USD,0) Open_Commitments_in_USD
,NVL(PO_Left_to_go,0) PO_Left_to_go
--,Budget_in_USD
FROM
(
SELECT
PPA.PROJECT_ID
,PPA.ORG_ID
--,PPE.NAME TASK_NAME
,HOUF.NAME
, PPA.NAME PROJECT_NAME
, PPA.SEGMENT1 GLOBAL_CER_PROJECT_NUMBER
,NULL LOCAL_CER_PROJECT_NUMBER
, PPNF.LIST_NAME PROJECT_MANAGER
, ( SELECT DISTINCT FLV.FLEX_VALUE
FROM FND_FLEX_VALUES_VL FLV
WHERE XEP.NAME=FLV.DESCRIPTION
AND FLV.VALUE_CATEGORY='ENTITY VALUE SET') LEGAL_ENTITY
,( SELECT DISTINCT FLV.DESCRIPTION
FROM FND_FLEX_VALUES_VL FLV
WHERE PPA.ATTRIBUTE1=FLV.FLEX_VALUE
AND FLV.VALUE_CATEGORY='LOCATION VALUE SET' ) LOCATION
,PPA.PROJECT_CURRENCY_CODE Functional_Currency
, PCT.CMT_NUMBER PO_NUMBER
, 0 ACTUALS_IN_USD
,(CASE WHEN PPA.PROJECT_CURRENCY_CODE='USD' THEN NVL(CAST(pct.ADDITIONAL_INFO1 AS NUMERIC),0) ELSE NVL(CAST(pct.ADDITIONAL_INFO1 AS NUMERIC),0)*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=PPA.PROJECT_CURRENCY_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(SYSDATE )) END) Open_Commitments_in_USD
,(CASE WHEN PPA.PROJECT_CURRENCY_CODE='USD' THEN NVL(CAST(pct.ADDITIONAL_INFO3 AS NUMERIC),0) ELSE NVL(CAST(pct.ADDITIONAL_INFO3 AS NUMERIC),0)*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=PPA.PROJECT_CURRENCY_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(SYSDATE)) END) PO_Left_to_go
/* ,(CASE WHEN PLD.PFC_CODE='USD' THEN PLD.PFC_RAW_COST ELSE PLD.PFC_RAW_COST*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=PLD.PFC_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(pld.PFC_COST_RATE_DATE )) END) Budget_in_USD
*/
FROM
PJF_PROJECTS_ALL_VL PPA,
PJF_PROJECT_TYPES_VL PPT,
PJF_CLASS_CATEGORIES_VL PCCA,
PJF_CLASS_CODES_VL PCCO,
PJF_PROJECT_CLASSES_V PPC,
PJF_LATESTPROJECTMANAGER_V PLM,
PER_PERSON_NAMES_F_V PPNF,
PJF_PROJ_ELEMENTS_VL PPE,
pjc_exp_items_all pei,
pjc_cost_dist_lines_all PCDLA,
HR_ORGANIZATION_UNITS_F_TL HOUF,
XLE_ENTITY_PROFILES XEP,
PJC_COMMITMENT_TXNS PCT
, pjf_txn_sources_vl PTSV
, pjf_txn_document_vl PTDV
/* , PJO_PLAN_VERSIONS_B PVB
, PJO_PLANNING_ELEMENTS PEL
,PJO_PLAN_LINE_DETAILS PLD */
,GLOBAL_CER GC
WHERE
PPA.PROJECT_TYPE_ID = PPT.PROJECT_TYPE_ID
--AND PPA.SEGMENT1 = 'US CUSTOM Project6'
AND PPA.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 (CASE WHEN PCCA.CLASS_CATEGORY IN ( 'Global CER' ) THEN PCCO.CLASS_CODE END) IN ('Y')
AND PPA.PROJECT_ID = PLM.PROJECT_ID(+)
AND PLM.RESOURCE_SOURCE_ID = PPNF.PERSON_ID(+)
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE(+) AND PPNF.EFFECTIVE_END_DATE(+)
AND PPA.project_id = PCDLA.project_id(+)
AND PCDLA.task_id (+) = PPE.proj_element_id
AND PPA.PROJECT_ID = PPE.PROJECT_ID
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 PPA.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
AND PCT.PROJECT_ID(+)=PPA.PROJECT_ID
AND PCT.TASK_ID(+)=PPE.proj_element_id
and pei.EXPENDITURE_ITEM_ID=pcdla.EXPENDITURE_ITEM_ID(+)
and pei.PROJECT_ID(+)=ppa.PROJECT_ID
and pei.TASK_ID(+)=ppe.proj_element_id
AND PCT.transaction_source_id = PTSV.transaction_source_id
AND PCT.document_id = PTDV.document_id
AND PTSV.transaction_source_id = PTDV.transaction_source_id
AND UPPER(PTSV.user_transaction_source) NOT IN ('ORACLE FUSION PAYABLES')
AND UPPER(PTDV.DOCUMENT_NAME) NOT IN ('SUPPLIER INVOICE COMMITMENT')
/* AND PPA.PROJECT_ID=PEL.PROJECT_ID(+)
AND PEL.PLANNING_ELEMENT_ID=PLD.PLANNING_ELEMENT_ID(+)
--AND PPA.NAME='US CUSTOM Project01'
AND PEL.TASK_ID(+)=PPE.proj_element_id
AND PVB.PROJECT_ID(+)=PPA.PROJECT_ID
AND PVB.PLAN_VERSION_ID(+)=PLD.PLAN_VERSION_ID
AND PVB.PLAN_VERSION_ID(+)=PEL.PLAN_VERSION_ID
AND PVB.PLAN_STATUS_CODE(+)='B' */
and gc.project_id=PPA.project_id
)
UNION
SELECT
GLOBAL_CER_PROJECT_NUMBER
,LOCAL_CER_PROJECT_NUMBER
, PROJECT_NAME
, PROJECT_MANAGER
,LEGAL_ENTITY
,LOCATION
,Functional_Currency
,PO_NUMBER
,NVL(ACTUALS_IN_USD,0) ACTUALS_IN_USD
,NVL(Open_Commitments_in_USD,0) Open_Commitments_in_USD
,NVL(PO_Left_to_go,0) PO_Left_to_go
--,Budget_in_USD
FROM
(
SELECT
PPA.PROJECT_ID
,PPA.ORG_ID
,HOUF.NAME
, PPA.NAME PROJECT_NAME
, PPA.SEGMENT1 GLOBAL_CER_PROJECT_NUMBER
,NULL LOCAL_CER_PROJECT_NUMBER
, PPNF.LIST_NAME PROJECT_MANAGER
, ( SELECT DISTINCT FLV.FLEX_VALUE
FROM FND_FLEX_VALUES_VL FLV
WHERE XEP.NAME=FLV.DESCRIPTION
AND FLV.VALUE_CATEGORY='ENTITY VALUE SET') LEGAL_ENTITY
,( SELECT DISTINCT FLV.DESCRIPTION
FROM FND_FLEX_VALUES_VL FLV
WHERE PPA.ATTRIBUTE1=FLV.FLEX_VALUE
AND FLV.VALUE_CATEGORY='LOCATION VALUE SET' ) LOCATION
,PPA.PROJECT_CURRENCY_CODE Functional_Currency
, AILA.ATTRIBUTE2 PO_NUMBER
, NVL(AILA.ACTUALS_IN_USD,0) ACTUALS_IN_USD
,0 Open_Commitments_in_USD
,0 PO_Left_to_go
/* ,(CASE WHEN PLD.PFC_CODE='USD' THEN SUM(PLD.PFC_RAW_COST) ELSE SUM(PLD.PFC_RAW_COST)*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=PLD.PFC_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(pld.PFC_COST_RATE_DATE)) END) Budget_in_USD
*/
FROM
PJF_PROJECTS_ALL_VL PPA,
PJF_PROJECT_TYPES_VL PPT,
PJF_CLASS_CATEGORIES_VL PCCA,
PJF_CLASS_CODES_VL PCCO,
PJF_PROJECT_CLASSES_V PPC,
PJF_LATESTPROJECTMANAGER_V PLM,
PER_PERSON_NAMES_F_V PPNF,
PJF_PROJ_ELEMENTS_VL PPE,
pjc_exp_items_all pei,
pjc_cost_dist_lines_all PCDLA,
HR_ORGANIZATION_UNITS_F_TL HOUF,
XLE_ENTITY_PROFILES XEP
/* , pjf_txn_sources_vl PTSV
, pjf_txn_document_vl PTDV */
/* , PJO_PLAN_VERSIONS_B PVB
, PJO_PLANNING_ELEMENTS PEL
,PJO_PLAN_LINE_DETAILS PLD */
,GLOBAL_CER GC
,PO_NUMG AILA
WHERE
PPA.PROJECT_TYPE_ID = PPT.PROJECT_TYPE_ID
--AND PPA.SEGMENT1 = 'US CUSTOM Project6'
AND PPA.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 (CASE WHEN PCCA.CLASS_CATEGORY IN ( 'Global CER' ) THEN PCCO.CLASS_CODE END) IN ('Y')
AND PPA.PROJECT_ID = PLM.PROJECT_ID(+)
AND PLM.RESOURCE_SOURCE_ID = PPNF.PERSON_ID(+)
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE(+) AND PPNF.EFFECTIVE_END_DATE(+)
AND PPA.project_id = PCDLA.project_id
AND PCDLA.task_id = PPE.proj_element_id
AND PPA.PROJECT_ID = PPE.PROJECT_ID
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 PPA.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
and pei.EXPENDITURE_ITEM_ID=pcdla.EXPENDITURE_ITEM_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 PPA.PROJECT_ID=PVB.PROJECT_ID(+)
AND PEL.PLANNING_ELEMENT_ID=PLD.PLANNING_ELEMENT_ID(+)
--AND PPA.NAME='US CUSTOM Project01'
AND PEL.TASK_ID(+)=PPE.proj_element_id
AND PEL.PROJECT_ID(+)=PPA.PROJECT_ID
AND PVB.PLAN_VERSION_ID(+)=PLD.PLAN_VERSION_ID
AND PVB.PLAN_VERSION_ID(+)=PEL.PLAN_VERSION_ID
AND PVB.PLAN_STATUS_CODE(+)='B' */
and gc.PROJECT_ID=PPA.PROJECT_ID
and aila.project_id(+)=ppa.project_id
/* GROUP BY
PPA.PROJECT_ID
,PPA.ORG_ID
--,PPE.NAME TASK_NAME
,HOUF.NAME
, PPA.NAME
, PPA.SEGMENT1
, PPNF.LIST_NAME
,XEP.NAME
,PPA.ATTRIBUTE1
,PPA.PROJECT_CURRENCY_CODE
,AILA.ATTRIBUTE2
,PPA.ATTRIBUTE4
--, pld.PFC_COST_RATE_DATE
--, PLD.PFC_CODE
, AILA.ACTUALS_IN_USD */
)
UNION
SELECT
GLOBAL_CER_PROJECT_NUMBER
,LOCAL_CER_PROJECT_NUMBER
, PROJECT_NAME
, PROJECT_MANAGER
,LEGAL_ENTITY
,LOCATION
,Functional_Currency
,PO_NUMBER
,NVL(ACTUALS_IN_USD,0) ACTUALS_IN_USD
,NVL(TO_NUMBER(Open_Commitments_in_USD),0) Open_Commitments_in_USD
,NVL(TO_NUMBER(PO_Left_to_go),0) PO_Left_to_go
--,Budget_in_USD
FROM
(
SELECT
PPA.PROJECT_ID
,PPA.ORG_ID
--,PPE.NAME TASK_NAME
,HOUF.NAME
, PPA.NAME PROJECT_NAME
, PPA.ATTRIBUTE4 GLOBAL_CER_PROJECT_NUMBER
,PPA.SEGMENT1 LOCAL_CER_PROJECT_NUMBER
, PPNF.LIST_NAME PROJECT_MANAGER
, ( SELECT DISTINCT FLV.FLEX_VALUE
FROM FND_FLEX_VALUES_VL FLV
WHERE XEP.NAME=FLV.DESCRIPTION
AND FLV.VALUE_CATEGORY='ENTITY VALUE SET') LEGAL_ENTITY
,( SELECT DISTINCT FLV.DESCRIPTION
FROM FND_FLEX_VALUES_VL FLV
WHERE PPA.ATTRIBUTE1=FLV.FLEX_VALUE
AND FLV.VALUE_CATEGORY='LOCATION VALUE SET' ) LOCATION
,PPA.PROJECT_CURRENCY_CODE Functional_Currency
, PCT.CMT_NUMBER PO_NUMBER
, 0 ACTUALS_IN_USD
,(CASE WHEN PPA.PROJECT_CURRENCY_CODE='USD' THEN NVL(CAST(pct.ADDITIONAL_INFO1 AS NUMERIC),0) ELSE NVL(CAST(pct.ADDITIONAL_INFO1 AS NUMERIC),0)*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=PPA.PROJECT_CURRENCY_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(SYSDATE )) END) Open_Commitments_in_USD
,(CASE WHEN PPA.PROJECT_CURRENCY_CODE='USD' THEN NVL(CAST(pct.ADDITIONAL_INFO3 AS NUMERIC),0) ELSE NVL(CAST(pct.ADDITIONAL_INFO3 AS NUMERIC),0)*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=PPA.PROJECT_CURRENCY_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(SYSDATE)) END) PO_Left_to_go
/*,(CASE WHEN PLD.PFC_CODE='USD' THEN PLD.PFC_RAW_COST ELSE PLD.PFC_RAW_COST*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=PLD.PFC_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(pld.PFC_COST_RATE_DATE)) END) Budget_in_USD*/
FROM
PJF_PROJECTS_ALL_VL PPA,
PJF_PROJECT_TYPES_VL PPT,
PJF_CLASS_CATEGORIES_VL PCCA,
PJF_CLASS_CODES_VL PCCO,
PJF_PROJECT_CLASSES_V PPC,
PJF_LATESTPROJECTMANAGER_V PLM,
PER_PERSON_NAMES_F_V PPNF,
PJF_PROJ_ELEMENTS_VL PPE,
pjc_exp_items_all pei,
pjc_cost_dist_lines_all PCDLA,
HR_ORGANIZATION_UNITS_F_TL HOUF,
XLE_ENTITY_PROFILES XEP,
PJC_COMMITMENT_TXNS PCT
, pjf_txn_sources_vl PTSV
, pjf_txn_document_vl PTDV
/* , PJO_PLAN_VERSIONS_B PVB
, PJO_PLANNING_ELEMENTS PEL
,PJO_PLAN_LINE_DETAILS PLD */
,GLOBAL_CER GC
WHERE
PPA.PROJECT_TYPE_ID = PPT.PROJECT_TYPE_ID
--AND PPA.SEGMENT1 = 'US CUSTOM Project6'
AND PPA.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 (CASE WHEN PCCA.CLASS_CATEGORY IN ( 'Global CER' ) THEN PCCO.CLASS_CODE END) IN ('Y')
AND NVL(PPA.ATTRIBUTE4, '-1')<>'-1'
AND (CASE WHEN PCCA.CLASS_CATEGORY IN ( 'Project Type Class' ) THEN PCCO.CLASS_CODE END) IN ('CER')
AND PPA.PROJECT_ID = PLM.PROJECT_ID(+)
AND PLM.RESOURCE_SOURCE_ID = PPNF.PERSON_ID(+)
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE(+) AND PPNF.EFFECTIVE_END_DATE(+)
AND PPA.project_id = PCDLA.project_id(+)
AND PCDLA.task_id(+) = PPE.proj_element_id
AND PPA.PROJECT_ID = PPE.PROJECT_ID
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 PPA.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
AND PCT.PROJECT_ID(+)=PPA.PROJECT_ID
AND PCT.TASK_ID(+)=PPE.proj_element_id
and pei.EXPENDITURE_ITEM_ID=pcdla.EXPENDITURE_ITEM_ID(+)
and pei.PROJECT_ID(+)=ppa.PROJECT_ID
and pei.TASK_ID(+)=ppe.proj_element_id
AND PCT.transaction_source_id = PTSV.transaction_source_id
AND PCT.document_id = PTDV.document_id
AND PTSV.transaction_source_id = PTDV.transaction_source_id
AND UPPER(PTSV.user_transaction_source) NOT IN ('ORACLE FUSION PAYABLES')
AND UPPER(PTDV.DOCUMENT_NAME) NOT IN ('SUPPLIER INVOICE COMMITMENT')
/* AND PPA.PROJECT_ID=PEL.PROJECT_ID(+)
AND PEL.PLANNING_ELEMENT_ID=PLD.PLANNING_ELEMENT_ID(+)
--AND PPA.NAME='US CUSTOM Project01'
AND PEL.TASK_ID(+)=PPE.proj_element_id
AND PVB.PROJECT_ID(+)=PPA.PROJECT_ID
AND PVB.PLAN_VERSION_ID(+)=PLD.PLAN_VERSION_ID
AND PVB.PLAN_VERSION_ID(+)=PEL.PLAN_VERSION_ID
AND PVB.PLAN_STATUS_CODE(+)='B' */
and gc.segment1=PPA.ATTRIBUTE4
)
UNION
SELECT
GLOBAL_CER_PROJECT_NUMBER
,LOCAL_CER_PROJECT_NUMBER
, PROJECT_NAME
, PROJECT_MANAGER
,LEGAL_ENTITY
,LOCATION
,Functional_Currency
,PO_NUMBER
,NVL(ACTUALS_IN_USD,0) ACTUALS_IN_USD
,NVL(Open_Commitments_in_USD,0) Open_Commitments_in_USD
,NVL(PO_Left_to_go,0) PO_Left_to_go
--,Budget_in_USD
FROM
(
SELECT
PPA.PROJECT_ID
,PPA.ORG_ID
,HOUF.NAME
, PPA.NAME PROJECT_NAME
, PPA.ATTRIBUTE4 GLOBAL_CER_PROJECT_NUMBER
,PPA.SEGMENT1 LOCAL_CER_PROJECT_NUMBER
, PPNF.LIST_NAME PROJECT_MANAGER
, ( SELECT DISTINCT FLV.FLEX_VALUE
FROM FND_FLEX_VALUES_VL FLV
WHERE XEP.NAME=FLV.DESCRIPTION
AND FLV.VALUE_CATEGORY='ENTITY VALUE SET') LEGAL_ENTITY
,( SELECT DISTINCT FLV.DESCRIPTION
FROM FND_FLEX_VALUES_VL FLV
WHERE PPA.ATTRIBUTE1=FLV.FLEX_VALUE
AND FLV.VALUE_CATEGORY='LOCATION VALUE SET' ) LOCATION
,PPA.PROJECT_CURRENCY_CODE Functional_Currency
, AILA.ATTRIBUTE2 PO_NUMBER
, NVL(AILA.ACTUALS_IN_USD,0) ACTUALS_IN_USD
,0 Open_Commitments_in_USD
,0 PO_Left_to_go
/* ,(CASE WHEN PLD.PFC_CODE='USD' THEN SUM(PLD.PFC_RAW_COST) ELSE SUM(PLD.PFC_RAW_COST)*(SELECT DISTINCT
gdr.CONVERSION_RATE
FROM
gl_daily_conversion_types gdc
,gl_daily_rates gdr
WHERE
UPPER(GDC.USER_CONVERSION_TYPE)='STANDARD'
AND GDR.FROM_CURRENCY=PLD.PFC_CODE
AND gdc.CONVERSION_TYPE=GDR.CONVERSION_TYPE
AND GDR.FROM_CURRENCY<>GDR.TO_CURRENCY
AND GDR.TO_CURRENCY='USD'
AND GDR.CONVERSION_DATE=TRUNC(pld.PFC_COST_RATE_DATE)) END) Budget_in_USD*/
FROM
PJF_PROJECTS_ALL_VL PPA,
PJF_PROJECT_TYPES_VL PPT,
PJF_CLASS_CATEGORIES_VL PCCA,
PJF_CLASS_CODES_VL PCCO,
PJF_PROJECT_CLASSES_V PPC,
PJF_LATESTPROJECTMANAGER_V PLM,
PER_PERSON_NAMES_F_V PPNF,
PJF_PROJ_ELEMENTS_VL PPE,
pjc_exp_items_all pei,
pjc_cost_dist_lines_all PCDLA,
HR_ORGANIZATION_UNITS_F_TL HOUF,
XLE_ENTITY_PROFILES XEP
/* , pjf_txn_sources_vl PTSV
, pjf_txn_document_vl PTDV */
-- , PJO_PLAN_VERSIONS_B PVB
-- , PJO_PLANNING_ELEMENTS PEL
-- ,PJO_PLAN_LINE_DETAILS PLD
,GLOBAL_CER GC
,PO_NUML AILA
WHERE
PPA.PROJECT_TYPE_ID = PPT.PROJECT_TYPE_ID
--AND PPA.SEGMENT1 = 'US CUSTOM Project6'
AND PPA.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 (CASE WHEN PCCA.CLASS_CATEGORY IN ( 'Global CER' ) THEN PCCO.CLASS_CODE END) IN ('Y')
AND NVL(PPA.ATTRIBUTE4, '-1')<>'-1'
AND (CASE WHEN PCCA.CLASS_CATEGORY IN ( 'Project Type Class' ) THEN PCCO.CLASS_CODE END) IN ('CER')
AND PPA.PROJECT_ID = PLM.PROJECT_ID(+)
AND PLM.RESOURCE_SOURCE_ID = PPNF.PERSON_ID(+)
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE(+) AND PPNF.EFFECTIVE_END_DATE(+)
AND PPA.project_id = PCDLA.project_id
AND PCDLA.task_id = PPE.proj_element_id
AND PPA.PROJECT_ID = PPE.PROJECT_ID
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 PPA.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
and pei.EXPENDITURE_ITEM_ID=pcdla.EXPENDITURE_ITEM_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 PPA.PROJECT_ID=PVB.PROJECT_ID(+)
AND PEL.PLANNING_ELEMENT_ID=PLD.PLANNING_ELEMENT_ID(+)
--AND PPA.NAME='US CUSTOM Project01'
AND PEL.TASK_ID(+)=PPE.proj_element_id
AND PEL.PROJECT_ID(+)=PPA.PROJECT_ID
AND PVB.PLAN_VERSION_ID(+)=PLD.PLAN_VERSION_ID
AND PVB.PLAN_VERSION_ID(+)=PEL.PLAN_VERSION_ID
AND PVB.PLAN_STATUS_CODE(+)='B' */
and gc.segment1=PPA.ATTRIBUTE4
and aila.project_id(+)=ppa.project_id
/* GROUP BY
PPA.PROJECT_ID
,PPA.ORG_ID
--,PPE.NAME TASK_NAME
,HOUF.NAME
, PPA.NAME
, PPA.SEGMENT1
, PPNF.LIST_NAME
,XEP.NAME
,PPA.ATTRIBUTE1
,PPA.PROJECT_CURRENCY_CODE
,AILA.ATTRIBUTE2
,PPA.ATTRIBUTE4
--, pld.PFC_COST_RATE_DATE
--, PLD.PFC_CODE
,AILA.ACTUALS_IN_USD */
)
)
WHERE PO_NUMBER IS NOT NULL
GROUP BY
GLOBAL_CER_PROJECT_NUMBER
,LOCAL_CER_PROJECT_NUMBER
, PROJECT_NAME
, PROJECT_MANAGER
,LEGAL_ENTITY
,LOCATION
,Functional_Currency
,PO_NUMBER
ORDER BY GLOBAL_CER_PROJECT_NUMBER
,LOCAL_CER_PROJECT_NUMBER,PO_NUMBER:P_PROJECT_NUMBER :P_PROJECT_STATUS :P_PROJECT_YEAR_FROM :P_PROJECT_YEAR_TO
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_CLASS_CATEGORIES_VL | dimension | dimension |
| PJF_CLASS_CODES_VL | dimension | dimension |
| PJC_EXP_ITEMS_ALL | dimension | dimension |
| GL_DAILY_RATES | 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.
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_CLASS_CATEGORIES_VL | 2 | 46 |
| PJF_CLASS_CODES_VL | 2 | 46 |
| PJC_EXP_ITEMS_ALL | 98 | 8 |
| GL_DAILY_RATES | 1 | 10 |
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.