Quarterly CIP FA by Project Report

Projects

The quarterly bridge from project CIP to Fixed Assets — what each project capitalized into Assets in the quarter, by project and asset, so finance can confirm CIP cleared to FA and depreciation began.

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 Quarterly CIP FA by Project Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

End Gl Period

FEB-26

Gl Location From

Sample

Gl Location To

Sample

Legal Entity

Globex Holdings

Project Class Code

Standard

Project Number

1003

Projects capitalized

$14.20M

Capitalized to FA

$1.30M

CIP not cleared

Project	Asset	Quarter	Cip Capitalized	Fa Cost	Status
Sample	Sample	Sample	Sample	$1,240,500.00	Open
—	—	—	—	$842,150.75	Posted
Sample	Sample	Sample	Sample	$96,400.00	Validated
—	—	—	—	$1,005,233.10	Open
Sample	Sample	Sample	Sample	$58,720.40	Paid
Sample	Sample	Sample	Sample	$1,240,500.00	Open

AI Analyst · active

reading

The report bridges project capitalization to Fixed Assets additions by quarter.

flag

$1.3M was capitalized in the project but not yet added in FA — a gap between project close and asset creation.

root cause & next step

Run the asset interface for the held amounts; CIP that doesn't reach FA never starts depreciating.

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

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

/* ===============================================================
   ONE FA CTE: Carry-over FA + Q1_FA + Q2_FA + Q3_FA,Q4_FA per Project
   =============================================================== */
FA_SUM AS (
       SELECT
        ppa1.PROJECT_ID,

        /* Carry-over FA: FA lines BEFORE the anchor year start of :P_END_GL_PERIOD */
        SUM(
            CASE
                WHEN gp.YEAR_START_DATE <
                     (SELECT MAX(YEAR_START_DATE)
                        FROM GL_PERIODS
                       WHERE PERIOD_NAME = :P_END_GL_PERIOD
                         AND PERIOD_SET_NAME = (
                               SELECT PERIOD_SET_NAME
                                 FROM GL_PERIODS
                                WHERE PERIOD_NAME = :P_END_GL_PERIOD
                                  AND ROWNUM = 1
                           ))
                THEN NVL(ppa1.CURRENT_ASSET_COST, 0)
                ELSE 0
            END
        ) AS CARRY_OVER_FA,

        /* Quarterly FA in the selected fiscal year up to :P_QUARTER */
        SUM(
            CASE
                WHEN gp.PERIOD_SET_NAME = (
                           SELECT PERIOD_SET_NAME
                             FROM GL_PERIODS
                            WHERE PERIOD_NAME = :P_END_GL_PERIOD
                              AND ROWNUM = 1
                     )
                 AND gp.FISCAL_YEAR = :P_YEAR
                 AND gp.QUARTER_NUM = 1
                 AND 1 <= :P_QUARTER
                THEN NVL(ppa1.CURRENT_ASSET_COST, 0)
                ELSE 0
            END
        ) AS Q1_FA,

        SUM(
            CASE
                WHEN gp.PERIOD_SET_NAME = (
                           SELECT PERIOD_SET_NAME
                             FROM GL_PERIODS
                            WHERE PERIOD_NAME = :P_END_GL_PERIOD
                              AND ROWNUM = 1
                     )
                 AND gp.FISCAL_YEAR = :P_YEAR
                 AND gp.QUARTER_NUM = 2
                 AND 2 <= :P_QUARTER
                THEN NVL(ppa1.CURRENT_ASSET_COST, 0)
                ELSE 0
            END
        ) AS Q2_FA,

        SUM(
            CASE
                WHEN gp.PERIOD_SET_NAME = (
                           SELECT PERIOD_SET_NAME
                             FROM GL_PERIODS
                            WHERE PERIOD_NAME = :P_END_GL_PERIOD
                              AND ROWNUM = 1
                     )
                 AND gp.FISCAL_YEAR = :P_YEAR
                 AND gp.QUARTER_NUM = 3
                 AND 3 <= :P_QUARTER
                THEN NVL(ppa1.CURRENT_ASSET_COST, 0)
                ELSE 0
            END
        ) AS Q3_FA,
		SUM(
            CASE
                WHEN gp.PERIOD_SET_NAME = (
                           SELECT PERIOD_SET_NAME
                             FROM GL_PERIODS
                            WHERE PERIOD_NAME = :P_END_GL_PERIOD
                              AND ROWNUM = 1
                     )
                 AND gp.FISCAL_YEAR = :P_YEAR
                 AND gp.QUARTER_NUM = 4
                 AND 4 <= :P_QUARTER
                THEN NVL(ppa1.CURRENT_ASSET_COST, 0)
                ELSE 0
            END
        ) AS Q4_FA

        /* Add Q4_FA similarly if needed */
    FROM PJC_PRJ_ASSET_LNS_ALL ppa1
    JOIN GL_PERIODS gp
      ON gp.PERIOD_NAME = ppa1.FA_PERIOD_NAME
    WHERE ppa1.TRANSFER_STATUS_CODE = 'T'
      AND ppa1.FA_PERIOD_NAME IS NOT NULL
	
    GROUP BY ppa1.PROJECT_ID
)

/* ===============================================================
   MAIN QUERY: Your existing CIP logic + LEFT JOIN FA_SUM
   =============================================================== */
SELECT
    src.PROJECT_NUMBER,
    src.PROJECT_NAME,
    src.PROJECT_STATUS,
    src.GL_LOCATION,
    src.GL_SELLING_METHOD,
    src.LEGAL_ENTITY_NAME,

    /* CIP-only aggregates */
    NVL(SUM(src.CIP_COST_AMOUNT), 0) AS CIP_COST_AMOUNT,
    NVL(SUM(src.CARRY_OVER_CIP) - MAX(NVL(fa.CARRY_OVER_FA, 0)), 0)  AS CARRY_OVER_CIP,
    NVL(SUM(src.Q1_CIP) - MAX(NVL(fa.Q1_FA, 0)) , 0)          AS Q1_CIP,
    NVL(SUM(src.Q2_CIP) -  MAX(NVL(fa.Q2_FA, 0))  , 0)          AS Q2_CIP,
    NVL(SUM(src.Q3_CIP) - MAX(NVL(fa.Q3_FA, 0)) , 0)          AS Q3_CIP,
    NVL(SUM(src.Q4_CIP) - MAX(NVL(fa.Q4_FA, 0)) , 0)          AS Q4_CIP,

   
/* FA columns MUST be aggregated in a GROUP BY query */
    MAX(NVL(fa.Q1_FA, 0))            AS Q1_FA,
    MAX(NVL(fa.Q2_FA, 0))            AS Q2_FA,
    MAX(NVL(fa.Q3_FA, 0))            AS Q3_FA,
	MAX(NVL(fa.Q4_FA, 0))            AS Q4_FA,
    /* If you add Q4_FA in the FA_SUM CTE, also expose it aggregated: */
    -- MAX(NVL(fa.Q4_FA, 0))         AS Q4_FA,

    /* YTD summaries: aggregate FA parts first, then combine */
    NVL(  NVL(SUM(src.Q1_CIP) - MAX(NVL(fa.Q1_FA, 0)) , 0)    +  NVL(SUM(src.Q2_CIP) -  MAX(NVL(fa.Q2_FA, 0))  , 0) + NVL(SUM(src.Q3_CIP) - MAX(NVL(fa.Q3_FA, 0)) , 0) +  NVL(SUM(src.Q4_CIP) - MAX(NVL(fa.Q4_FA, 0)) , 0), 0) AS YTD_CIP,
    NVL( MAX(NVL(fa.Q1_FA, 0)) + MAX(NVL(fa.Q2_FA, 0)) + MAX(NVL(fa.Q3_FA, 0))  + MAX(NVL(fa.Q4_FA,0)) , 0) AS YTD_FA,

    /* CIP Balance (CIP-only) */
    NVL(SUM(src.CARRY_OVER_CIP) - MAX(NVL(fa.CARRY_OVER_FA, 0)), 0)  +  
       +  NVL(SUM(src.Q1_CIP) - MAX(NVL(fa.Q1_FA, 0)) , 0)     +  NVL(SUM(src.Q2_CIP) -  MAX(NVL(fa.Q2_FA, 0))  , 0) +  NVL(SUM(src.Q3_CIP) - MAX(NVL(fa.Q3_FA, 0)) , 0)  +  NVL(SUM(src.Q4_CIP) - MAX(NVL(fa.Q4_FA, 0)) , 0)  AS CIP_BAL

FROM (
    /* -------------------------
       Your inner CIP source (unchanged except FA bits removed)
       ------------------------- */
    SELECT
        PPAV.PROJECT_ID,
        PPAV.SEGMENT1               AS PROJECT_NUMBER,
        PPAV.NAME                   AS PROJECT_NAME,
        PPAV.PROJECT_STATUS_CODE    AS PROJECT_STATUS,
        XEP.NAME                    AS LEGAL_ENTITY_NAME,
        PPAV.ATTRIBUTE1             AS GL_LOCATION,
        PPAV.ATTRIBUTE2             AS GL_SELLING_METHOD,

        /* CIP base amount (per cost line) */
        PCDLA.ACCT_RAW_COST         AS CIP_COST_AMOUNT,

        /* Carry-over CIP up to :P_END_GL_PERIOD (your existing logic) */
        (
            SELECT NVL(SUM(PCDLAA.ACCT_RAW_COST), 0)
            FROM PJC_COST_DIST_LINES_ALL PCDLAA
               , PJF_PROJ_ELEMENTS_VL   PPEV
            WHERE PCDLAA.PROJECT_ID          = PCDLA.PROJECT_ID
              AND PCDLAA.EXPENDITURE_ITEM_ID = PCDLA.EXPENDITURE_ITEM_ID
              AND PCDLAA.LINE_NUM            = PCDLA.LINE_NUM
              AND PPEV.PROJECT_ID            = PPAV.PROJECT_ID
              AND PPEV.PROJ_ELEMENT_ID       = PCDLAA.TASK_ID
              AND PCDLAA.PRVDR_GL_DATE <
                  (SELECT MAX(YEAR_START_DATE)
                     FROM GL_PERIODS
                    WHERE PERIOD_NAME = :P_END_GL_PERIOD
                      AND PERIOD_SET_NAME = PPPAV.PERIOD_SET_NAME)
        ) AS CARRY_OVER_CIP,

        /* Q1..Q4 CIP (same as your file; FA subtractions removed) */
        NVL((
            SELECT NVL(PCDLAA.ACCT_RAW_COST, 0)
            FROM PJC_COST_DIST_LINES_ALL PCDLAA
               , PJF_PROJ_ELEMENTS_VL   PPEV
            WHERE PCDLAA.PROJECT_ID          = PCDLA.PROJECT_ID
              AND PCDLAA.EXPENDITURE_ITEM_ID = PCDLA.EXPENDITURE_ITEM_ID
              AND PCDLAA.LINE_NUM            = PCDLA.LINE_NUM
              AND PPEV.PROJECT_ID            = PPAV.PROJECT_ID
              AND PPEV.PROJ_ELEMENT_ID       = PCDLAA.TASK_ID
              AND PCDLAA.PRVDR_GL_DATE BETWEEN
                  (SELECT MIN(START_DATE) FROM GL_PERIODS
                   WHERE FISCAL_YEAR = :P_YEAR
                     AND QUARTER_NUM <= :P_QUARTER AND QUARTER_NUM = 1
                     AND PERIOD_SET_NAME = PPPAV.PERIOD_SET_NAME)
              AND (SELECT MAX(END_DATE) FROM GL_PERIODS
                   WHERE FISCAL_YEAR = :P_YEAR
                     AND QUARTER_NUM <= :P_QUARTER AND QUARTER_NUM = 1
                     AND PERIOD_SET_NAME = PPPAV.PERIOD_SET_NAME)
              AND ROWNUM = 1
        ), 0) AS Q1_CIP,

        NVL((
            SELECT NVL(PCDLAA.ACCT_RAW_COST, 0)
            FROM PJC_COST_DIST_LINES_ALL PCDLAA
               , PJF_PROJ_ELEMENTS_VL   PPEV
            WHERE PCDLAA.PROJECT_ID          = PCDLA.PROJECT_ID
              AND PCDLAA.EXPENDITURE_ITEM_ID = PCDLA.EXPENDITURE_ITEM_ID
              AND PCDLAA.LINE_NUM            = PCDLA.LINE_NUM
              AND PPEV.PROJECT_ID            = PPAV.PROJECT_ID
              AND PPEV.PROJ_ELEMENT_ID       = PCDLAA.TASK_ID
              AND PCDLAA.PRVDR_GL_DATE BETWEEN
                  (SELECT MIN(START_DATE) FROM GL_PERIODS
                   WHERE FISCAL_YEAR = :P_YEAR
                     AND QUARTER_NUM <= :P_QUARTER AND QUARTER_NUM = 2
                     AND PERIOD_SET_NAME = PPPAV.PERIOD_SET_NAME)
              AND (SELECT MAX(END_DATE) FROM GL_PERIODS
                   WHERE FISCAL_YEAR = :P_YEAR
                     AND QUARTER_NUM <= :P_QUARTER AND QUARTER_NUM = 2
                     AND PERIOD_SET_NAME = PPPAV.PERIOD_SET_NAME)
              AND ROWNUM = 1
        ), 0) AS Q2_CIP,

        NVL((
            SELECT NVL(PCDLAA.ACCT_RAW_COST, 0)
            FROM PJC_COST_DIST_LINES_ALL PCDLAA
               , PJF_PROJ_ELEMENTS_VL   PPEV
            WHERE PCDLAA.PROJECT_ID          = PCDLA.PROJECT_ID
              AND PCDLAA.EXPENDITURE_ITEM_ID = PCDLA.EXPENDITURE_ITEM_ID
              AND PCDLAA.LINE_NUM            = PCDLA.LINE_NUM
              AND PPEV.PROJECT_ID            = PPAV.PROJECT_ID
              AND PPEV.PROJ_ELEMENT_ID       = PCDLAA.TASK_ID
              AND PCDLAA.PRVDR_GL_DATE BETWEEN
                  (SELECT MIN(START_DATE) FROM GL_PERIODS
                   WHERE FISCAL_YEAR = :P_YEAR
                     AND QUARTER_NUM <= :P_QUARTER AND QUARTER_NUM = 3
                     AND PERIOD_SET_NAME = PPPAV.PERIOD_SET_NAME)
              AND (SELECT MAX(END_DATE) FROM GL_PERIODS
                   WHERE FISCAL_YEAR = :P_YEAR
                     AND QUARTER_NUM <= :P_QUARTER AND QUARTER_NUM = 3
                     AND PERIOD_SET_NAME = PPPAV.PERIOD_SET_NAME)
              AND ROWNUM = 1
        ), 0) AS Q3_CIP,

        NVL((
            SELECT SUM(NVL(PCDLAA.ACCT_RAW_COST, 0))
            FROM PJC_COST_DIST_LINES_ALL PCDLAA
               , PJF_PROJ_ELEMENTS_VL   PPEV
            WHERE PCDLAA.PROJECT_ID          = PCDLA.PROJECT_ID
              AND PCDLAA.EXPENDITURE_ITEM_ID = PCDLA.EXPENDITURE_ITEM_ID
              AND PCDLAA.LINE_NUM            = PCDLA.LINE_NUM
              AND PPEV.PROJECT_ID            = PPAV.PROJECT_ID
              AND PPEV.PROJ_ELEMENT_ID       = PCDLAA.TASK_ID
              AND PCDLAA.PRVDR_GL_DATE BETWEEN
                  (SELECT MIN(START_DATE) FROM GL_PERIODS
                   WHERE FISCAL_YEAR = :P_YEAR
                     AND QUARTER_NUM <= :P_QUARTER AND QUARTER_NUM = 4
                     AND PERIOD_SET_NAME = PPPAV.PERIOD_SET_NAME)
              AND (SELECT MAX(END_DATE) FROM GL_PERIODS
                   WHERE FISCAL_YEAR = :P_YEAR
                     AND QUARTER_NUM <= :P_QUARTER AND QUARTER_NUM = 4
                     AND PERIOD_SET_NAME = PPPAV.PERIOD_SET_NAME)
        ), 0) AS Q4_CIP

    FROM PJF_PROJECTS_ALL_VL       PPAV
       , PJF_PROJECT_TYPES_VL      PPTV
       , PJF_CLASS_CODES_VL        PCCV
       , PJF_PROJECT_CLASSES       PPC
       , PJC_COST_DIST_LINES_ALL   PCDLA
       , XLE_ENTITY_PROFILES       XEP
       , PJF_P_PERIODS_ALL_V       PPPAV
       , SEC_TBL                   ST
    WHERE ST.ORGANIZATION_ID    = PPAV.ORG_ID
      AND PPAV.PROJECT_TYPE_ID  = PPTV.PROJECT_TYPE_ID
      AND PPAV.PROJECT_ID       = PPC.PROJECT_ID
      AND PPC.CLASS_CODE_ID     = PCCV.CLASS_CODE_ID
      AND PPAV.PROJECT_ID       = PCDLA.PROJECT_ID
      AND PPTV.ENABLE_CAPITALIZATION_FLAG = 'Y'
      AND PPAV.TEMPLATE_FLAG    = 'N'
      AND PCCV.CLASS_CODE       IN ('CER','MER','TER')
      AND PPAV.LEGAL_ENTITY_ID  = XEP.LEGAL_ENTITY_ID
      AND PCDLA.PRVDR_GL_DATE BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
      AND PPAV.ORG_ID           = PPPAV.ORG_ID
      AND PCDLA.CAPITALIZABLE_FLAG = 'Y'
     AND PPPAV.START_DATE						<= (SELECT DISTINCT START_DATE FROM PJF_P_PERIODS_ALL_V WHERE PERIOD_NAME = (:P_END_GL_PERIOD) AND ORG_ID =PPPAV.ORG_ID)
	AND (XEP.LEGAL_ENTITY_ID            		IN (:P_LEGAL_ENTITY)
    OR  'ALL'                					IN (:P_LEGAL_ENTITY||'ALL'))
	AND PPAV.ATTRIBUTE2 						BETWEEN :P_SELL_METHOD_FROM AND :P_SELL_METHOD_TO
	AND PPAV.ATTRIBUTE1 						BETWEEN :P_GL_LOCATION_FROM AND :P_GL_LOCATION_TO
	AND (PCCV.CLASS_CODE            			IN (:P_PROJECT_CLASS_CODE)
    OR  'ALL'                					IN (:P_PROJECT_CLASS_CODE||'ALL'))
	AND (PPAV.SEGMENT1		            		IN (:P_PROJECT_NUMBER)
    OR  'ALL'                					IN (:P_PROJECT_NUMBER||'ALL'))
) src

LEFT JOIN FA_SUM fa
  ON fa.PROJECT_ID = src.PROJECT_ID

GROUP BY
    src.PROJECT_NUMBER,
    src.PROJECT_NAME,
    src.PROJECT_STATUS,
    src.GL_LOCATION,
    src.GL_SELLING_METHOD,
    src.LEGAL_ENTITY_NAME
ORDER BY src.PROJECT_NUMBER

:P_END_GL_PERIOD :P_GL_LOCATION_FROM :P_GL_LOCATION_TO :P_LEGAL_ENTITY :P_PROJECT_CLASS_CODE :P_PROJECT_NUMBER :P_QUARTER :P_SELL_METHOD_FROM :P_SELL_METHOD_TO :P_YEAR :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
PJC_PRJ_ASSET_LNS_ALL	dimension	dimension
PJF_PROJECTS_ALL_VL	dimension	dimension
FA_ADDITIONS_B	dimension	dimension
GL_PERIODS	dimension	dimension
Fa Cost	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_PRJ_ASSETS_ALL	25	1
PJC_PRJ_ASSET_LNS_ALL	15	2
PJF_PROJECTS_ALL_VL	31	67
FA_ADDITIONS_B	15	9
GL_PERIODS	Setup / configuration table — joined for reference, not exposed for analytics

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.