Projects CIP TIP Asset Detail Report

Projects
Construction-in-progress detail at the accounting level — every cost line sitting in CIP by project and asset, tied to its subledger entry, so finance can reconcile the CIP balance and see what's eligible to capitalize.
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 CIP TIP Asset Detail Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
Sample build · illustrative
Filters
Brand From
Sample
Brand To
Sample
Cost Center From
$96,400.00
Cost Center To
$96,400.00
End Gl Period
FEB-26
Gl Location From
Sample
3,200
CIP lines
$22.40M
CIP balance
$0 diff
Ties to GL
Project	Asset	Cost Line	Account	Amount	Accounting Date
Sample	Sample	$1,240,500.00	1000-2100-000	$1,240,500.00	2026-04-30
—	—	$842,150.75	1000-5400-000	$842,150.75	2026-03-31
Sample	Sample	$96,400.00	1000-1410-000	$96,400.00	2026-02-28
—	—	$1,005,233.10	2000-2100-000	$1,005,233.10	2026-01-31
Sample	Sample	$58,720.40	1000-6300-000	$58,720.40	2025-12-31
Sample	Sample	$1,240,500.00	1000-2100-000	$1,240,500.00	2026-04-30
AI Analyst · active
reading
The report reads CIP cost lines through XLA, tied to their project and planned asset.
flag
CIP ties to GL except for a few lines with no project reference — they sit in the CIP account but can't be capitalized to an asset.
root cause & next step
Tag the orphan lines to a project and asset; untagged CIP is cost that never converts to a depreciating asset.
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 XLA_TBL AS
(
SELECT /*+ PARALLEL(48) */ DISTINCT 
GCC.CODE_COMBINATION_ID,
XAH.EVENT_ID,
XDL.source_distribution_id_num_1,
XDL.source_distribution_id_num_2,
XDL.source_distribution_type,
GJB.NAME BATCH_NAME,
 GCC.segment1,
GCC.segment2,
GCC.segment3,
GCC.segment4,
GCC.segment5,
GCC.segment6,
GCC.segment7,
GCC.segment8,
GCC.segment9
FROM
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
GL_LEDGERS GL,
GL_CODE_COMBINATIONS GCC,
xla_distribution_links XDL,
GL_IMPORT_REFERENCES GIR,
GL_JE_BATCHES GJB
WHERE
1=1
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.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 XAH.APPLICATION_ID IN (10036,200)
AND XAL.CODE_COMBINATION_ID <> -1
AND UPPER(XAL.ACCOUNTING_CLASS_CODE) NOT LIKE '%CLEARING%'
--AND upper(gcc.FINANCIAL_CATEGORY) = upper('OTHER ASSET')
/*ADDED*/
AND 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 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.CODE_COMBINATION_ID,
XAH.EVENT_ID
, GCC.segment1
  , GCC.segment2
  , GCC.segment3
  , GCC.segment4
  , GCC.segment5
  , GCC.segment6
  , GCC.segment7
  , GCC.segment8
  , GCC.segment9
  ,GJB.NAME
),
-- Collect project IDs from first and second blocks
 EXCLUDED_PROJECTS AS (
    SELECT DISTINCT PROJECT_ID
    FROM (
        SELECT PROJECT_ID
        FROM PJC_COST_DIST_LINES_ALL
        WHERE CAPITALIZABLE_FLAG = 'Y'
         -- AND PROJECT_ID = '300000017358197'

        UNION

        SELECT PCDLA.PROJECT_ID
        FROM PJC_COST_DIST_LINES_ALL PCDLA
        JOIN XLA_TBL XT ON PCDLA.ACCT_EVENT_ID = XT.EVENT_ID
        JOIN GL_CODE_COMBINATIONS GCC ON XT.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
        WHERE PCDLA.CAPITALIZABLE_FLAG = 'Y'
         -- AND PCDLA.PROJECT_ID = '300000017358197'
    )
),

-- Collect task IDs from first block
EXCLUDED_TASKS AS (
    SELECT DISTINCT TASK_ID
    FROM PJC_COST_DIST_LINES_ALL
    WHERE CAPITALIZABLE_FLAG = 'Y'
      --AND PROJECT_ID = '300000017358197'
), 
RAW_COST AS
(
SELECT
    PROJECT_ID,
    TASK_ID,
    COST_CCID,
    SUM(ACCT_RAW_COST) AS ACCT_RAW_COST
FROM (
    -- First block
    SELECT
        PCDLA.PROJECT_ID,
        PCDLA.TASK_ID,
        3 AS COST_CCID,
        SUM(PCDLA.ACCT_RAW_COST) AS ACCT_RAW_COST,
		null PROJECT_ASSET_ID
    FROM
        PJC_COST_DIST_LINES_ALL PCDLA,
		PJF_P_PERIODS_ALL_V PPPAV
    WHERE
        PCDLA.CAPITALIZABLE_FLAG = 'Y'
	AND PCDLA.RECVR_PA_DATE						BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
	AND PCDLA.ORG_ID								= PPPAV.ORG_ID
	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 PROJECT_ID IN ('300000017855497', '300000017358214', '300000017358197')
    GROUP BY
        PCDLA.PROJECT_ID,
        PCDLA.TASK_ID
    UNION ALL

    -- Second block
    SELECT
        PCDLA.PROJECT_ID,
        PCDLA.TASK_ID,
        4 AS COST_CCID,
        SUM(PCDLA.ACCT_RAW_COST) AS ACCT_RAW_COST,
		null PROJECT_ASSET_ID
    FROM
		PJF_P_PERIODS_ALL_V PPPAV,
        PJC_COST_DIST_LINES_ALL PCDLA
        JOIN XLA_TBL XT ON PCDLA.ACCT_EVENT_ID = XT.EVENT_ID
        JOIN GL_CODE_COMBINATIONS GCC ON XT.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
	
		 
    WHERE
        PCDLA.CAPITALIZABLE_FLAG = 'Y'
      --  AND PROJECT_ID IN ('300000017855497', '300000017358214', '300000017358197')
        AND PCDLA.TASK_ID NOT IN (SELECT TASK_ID FROM EXCLUDED_TASKS)
			 AND PCDLA.RECVR_PA_DATE						BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
	AND PCDLA.ORG_ID								= PPPAV.ORG_ID
	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)
    GROUP BY
        PCDLA.PROJECT_ID,
        PCDLA.TASK_ID

    UNION ALL

    -- Third block
    SELECT
        PCDLA.PROJECT_ID,
        1 AS TASK_ID,
        2 AS COST_CCID,
        SUM(PCDLA.ACCT_RAW_COST) AS ACCT_RAW_COST,
		null PROJECT_ASSET_ID
    FROM
        PJC_COST_DIST_LINES_ALL PCDLA,
		PJF_P_PERIODS_ALL_V PPPAV
    WHERE
        /* PROJECT_ID IN ('300000017855497', '300000017358214', '300000017358197')
        AND */ PROJECT_ID NOT IN (SELECT PROJECT_ID FROM EXCLUDED_PROJECTS)
			 AND PCDLA.RECVR_PA_DATE						BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
	AND PCDLA.ORG_ID								= PPPAV.ORG_ID
	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)
    GROUP BY
        PCDLA.PROJECT_ID
) combined_cost
GROUP BY
    PROJECT_ID,
    TASK_ID,
    COST_CCID

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

select  distinct
	MAIN_TBL.PROJECT_ID,
	MAIN_TBL.PROJ_ELEMENT_ID,
	MAIN_TBL.PROJECT_NUMBER,
	MAIN_TBL.PROJECT_NUMBER PPP,
	MAIN_TBL.GL_CODE_COMBINATION GGG,
	MAIN_TBL.PROJECT_NAME,
	MAIN_TBL.PROJECT_STATUS,
	MAIN_TBL.PROJECT_TYPE,
	MAIN_TBL.PROJECT_CLASS_CODE,
	MAIN_TBL.PROJECT_MANAGER,
	MAIN_TBL.TASK_NAME,
	MAIN_TBL.TASK_NUMBER,
	MAIN_TBL.TASK_SERVICE_TYPE,
	MAIN_TBL.MANUFACTURER_NAME,
	MAIN_TBL.CAPITAL_HOLD,
	MAIN_TBL.ASSET_CATEGORY,
	MAIN_TBL.ASSET_NUMBER,
	MAIN_TBL.ASSET_NAME,
	MAIN_TBL.ESTIMATED_IN_SERVICE_DATE,
	MAIN_TBL.ACTUAL_IN_SERVICE_DATE,
	--MAIN_TBL.CIP_COST_AMOUNT,
	NVL(RC.ACCT_RAW_COST, 0) AS CIP_COST_AMOUNT,
    NVL(MAIN_TBL.COST_INTERFACED_TO_FA, 0) AS COST_INTERFACED_TO_FA,
    null AS CIP_BALANCE_AMOUNT,
	MAIN_TBL.TOOL_PROCESS_CODE,
	MAIN_TBL.BRAND,
	MAIN_TBL.TOOL_CLASS,
	MAIN_TBL.SKU,
	MAIN_TBL.CUSTOM_INTERCO_ASSET_TRANSFER,
	MAIN_TBL.LEGACY_ASSET_NUMBER,
	MAIN_TBL.TOOL_TONNAGE,
	MAIN_TBL.TOOL_MAKER,
	MAIN_TBL.FA_ADDITIONAL1,
	MAIN_TBL.FA_ADDITIONAL2,
	MAIN_TBL.ASSET_KEY,
	MAIN_TBL.SERIAL_NUMBER,
	MAIN_TBL.TAG_NUMBER,
	MAIN_TBL.COUNTRY,
	MAIN_TBL.STATE,
	MAIN_TBL.CITY,
	MAIN_TBL.BUILDING,
	MAIN_TBL.SITE,
	MAIN_TBL.PROJECT_ASSET_TYPE,
	MAIN_TBL.ASSET_BOOK,
	MAIN_TBL.ESTIMATED_ASSET_UNITS,
	MAIN_TBL.ACTUAL_ASSET_UNITS,
	MAIN_TBL.GL_CODE_COMBINATION,
	MAIN_TBL.TASK_ORG_NAME,
	MAIN_TBL.GL_LOCATION,
	MAIN_TBL.GL_SELLING_METHOD,
	MAIN_TBL.LEGAL_ENTITY_NAME
	--RC.ACCT_RAW_COST
from
(
SELECT 
	PROJECT_ID,
	PROJ_ELEMENT_ID,
	PROJECT_NUMBER,
	PROJECT_NUMBER PPP,
	GL_CODE_COMBINATION GGG,
	PROJECT_NAME,
	PROJECT_STATUS,
	PROJECT_TYPE,
	PROJECT_CLASS_CODE,
	PROJECT_MANAGER,
	TASK_NAME,
	TASK_NUMBER,
	TASK_SERVICE_TYPE,
	MANUFACTURER_NAME,
	CAPITAL_HOLD,
	ASSET_CATEGORY,
	ASSET_NUMBER,
	ASSET_NAME,
	ESTIMATED_IN_SERVICE_DATE,
	ACTUAL_IN_SERVICE_DATE,
	SUM(NVL(CIP_COST_AMOUNT,0)) CIP_COST_AMOUNT,
	SUM(NVL(COST_INTERFACED_TO_FA,0)) COST_INTERFACED_TO_FA,
	SUM((NVL(CIP_COST_AMOUNT,0) - NVL(COST_INTERFACED_TO_FA,0))) CIP_BALANCE_AMOUNT,
	TOOL_PROCESS_CODE,
	BRAND,
	TOOL_CLASS,
	SKU,
	CUSTOM_INTERCO_ASSET_TRANSFER,
	LEGACY_ASSET_NUMBER,
	TOOL_TONNAGE,
	TOOL_MAKER,
	FA_ADDITIONAL1,
	FA_ADDITIONAL2,
	ASSET_KEY,
	SERIAL_NUMBER,
	TAG_NUMBER,
	COUNTRY,
	STATE,
	CITY,
	BUILDING,
	SITE,
	PROJECT_ASSET_TYPE,
	ASSET_BOOK,
	ESTIMATED_ASSET_UNITS,
	ACTUAL_ASSET_UNITS,
	GL_CODE_COMBINATION,
	TASK_ORG_NAME,
	GL_LOCATION,
	GL_SELLING_METHOD,
	LEGAL_ENTITY_NAME,
	CODE_COMBINATION_ID
FROM
(
 SELECT /*+ PARALLEL(48) */ distinct
	PPAV.PROJECT_ID,
	PPEV.PROJ_ELEMENT_ID,
	PPAV.SEGMENT1 PROJECT_NUMBER,
	PPAV.NAME PROJECT_NAME,
	PPAV.PROJECT_STATUS_CODE PROJECT_STATUS,
	PPTV.PROJECT_TYPE PROJECT_TYPE,
	PCCV.CLASS_CODE PROJECT_CLASS_CODE,
	PPNFV.LIST_NAME PROJECT_MANAGER,
	PPEV.NAME TASK_NAME,
	PPEV.ELEMENT_NUMBER TASK_NUMBER,
	PPEV.SERVICE_TYPE_CODE TASK_SERVICE_TYPE,
	null CIP_COST_AMOUNT,
	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) */ 
	PPAA.MANUFACTURER_NAME MANUFACTURER_NAME,
	PPAA.CAPITAL_HOLD_FLAG CAPITAL_HOLD,
	PPAA.ASSET_NUMBER ASSET_NUMBER,
	PPAA.ASSET_NAME ASSET_NAME,
	PPAA.ESTIMATED_IN_SERVICE_DATE ESTIMATED_IN_SERVICE_DATE,
	PPAA.DATE_PLACED_IN_SERVICE ACTUAL_IN_SERVICE_DATE,
	PPAA.SERIAL_NUMBER SERIAL_NUMBER,
	PPAA.TAG_NUMBER TAG_NUMBER,
	PPAA.PROJECT_ASSET_TYPE,
	PPAA.BOOK_TYPE_CODE ASSET_BOOK,
	PPAA.ESTIMATED_ASSET_UNITS,
	PPAA.ASSET_UNITS ACTUAL_ASSET_UNITS,
	FCV.SEGMENT1||'-'||FCV.SEGMENT2 ASSET_CATEGORY,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT1 else null end TOOL_PROCESS_CODE,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT2 else null end BRAND,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT3 else null end TOOL_CLASS,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT4 else null end SKU,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT5 else null end CUSTOM_INTERCO_ASSET_TRANSFER,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT6 else null end LEGACY_ASSET_NUMBER,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT7 else null end TOOL_TONNAGE,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT8 else null end TOOL_MAKER,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT9 else null end FA_ADDITIONAL1,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT10 else null  end FA_ADDITIONAL2,
	case when PPAA.ASSET_KEY_CCID is not null then FAK.SEGMENT1||'.'||FAK.SEGMENT2||'.'||FAK.SEGMENT3||'.'||FAK.SEGMENT4||'.'||FAK.SEGMENT5||'.'||FAK.SEGMENT6||'.'||FAK.SEGMENT7||'.'||FAK.SEGMENT8||'.'||FAK.SEGMENT9||'.'||FAK.SEGMENT10  else null end ASSET_KEY,
	FL.SEGMENT1 COUNTRY,
	FL.SEGMENT2 STATE,
	FL.SEGMENT3 CITY,
	FL.SEGMENT4 BUILDING,
	FL.SEGMENT5 SITE,
	/*GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7||'.'||GCC.SEGMENT8||'.'||GCC.SEGMENT9 GL_CODE_COMBINATION,*/
	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_CODE_COMBINATION,
	HOUFT.NAME TASK_ORG_NAME,
	XEP.NAME LEGAL_ENTITY_NAME,
	PPAV.ATTRIBUTE1 GL_LOCATION,
	PPAV.ATTRIBUTE2 GL_SELLING_METHOD,
	3 CODE_COMBINATION_ID 
FROM 
	PJF_PROJECTS_ALL_VL PPAV,
	PJF_PROJECT_TYPES_VL PPTV,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES PPC,
	PJF_LATESTPROJECTMANAGER_V PLV,
	PER_PERSON_NAMES_F_V PPNFV,
	PJF_PROJ_ELEMENTS_VL PPEV,
	pjc_cost_dist_lines_all PCDLA,
	PJC_PRJ_ASSETS_ALL PPAA,
	PJC_PRJ_ASSET_ASGS PPAAT, /*added to get correct task number*/
	PJC_PRJ_ASSET_LN_DETS PPALD,
	PJC_PRJ_ASSET_LNS_ALL PPALA,
	FA_CATEGORIES_VL FCV,
	FA_ASSET_KEYWORDS FAK,
	FA_LOCATIONS FL,
	GL_CODE_COMBINATIONS GCC,
	HR_ORGANIZATION_UNITS_F_TL HOUFT,
	XLE_ENTITY_PROFILES XEP,
	PJF_P_PERIODS_ALL_V PPPAV,
	pjc_exp_items_all pei,
	SEC_TBL ST,
	xla_tbl PRJ_XT,
	xla_tbl AP_XT
WHERE
	1=1
	AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
	AND PPAV.PROJECT_TYPE_ID 					= PPTV.PROJECT_TYPE_ID
	--AND PPAV.SEGMENT1							= 'US CUSTOM Project6'
	AND PPAV.PROJECT_ID 						= PPC.PROJECT_ID
	AND PPC.CLASS_CODE_ID 						= PCCV.CLASS_CODE_ID
	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 PPAV.project_id                  		= PCDLA.project_id
	AND PCDLA.task_id             				= PPEV.proj_element_id
	AND PPAV.PROJECT_ID 						= PPEV.PROJECT_ID
	AND PPAV.PROJECT_ID 						= PPAA.PROJECT_ID
	AND PCDLA.EXPENDITURE_ITEM_ID				= PPALD.EXPENDITURE_ITEM_ID(+)
	AND PCDLA.LINE_NUM							= PPALD.LINE_NUM(+)
	AND PPAA.PROJECT_ASSET_ID 					= PPALA.PROJECT_ASSET_ID(+)
	AND PPAA.PROJECT_ID							= PPALA.PROJECT_ID(+)
	AND PPEV.proj_element_id					= PPALA.TASK_ID(+)
	AND PPALA.PROJECT_ASSET_LINE_DETAIL_ID		= PPALD.PROJECT_ASSET_LINE_DETAIL_ID(+)
	AND PPAA.ASSET_CATEGORY_ID 					= FCV.CATEGORY_ID
	AND PPAA.ASSET_KEY_CCID = FAK.CODE_COMBINATION_ID(+)
	--AND NVL(PPAA.ASSET_KEY_CCID,FAK.CODE_COMBINATION_ID) = FAK.CODE_COMBINATION_ID
	AND PPAA.LOCATION_ID 						= FL.LOCATION_ID
	--AND PPALA.CIP_CCID 							= GCC.CODE_COMBINATION_ID(+)
	AND PPEV.CARRYING_OUT_ORGANIZATION_ID 		= HOUFT.ORGANIZATION_ID
	AND SYSDATE 								BETWEEN HOUFT.EFFECTIVE_START_DATE AND HOUFT.EFFECTIVE_END_DATE
	AND HOUFT.LANGUAGE							= USERENV('LANG')
	AND PPTV.ENABLE_CAPITALIZATION_FLAG 		= 'Y'
	AND PPAV.TEMPLATE_FLAG 						= 'N'
	AND PCDLA.CAPITALIZABLE_FLAG				= 'Y'
	AND PCCV.CLASS_CODE 						IN ('CER','MER','TER')
	AND PPAV.LEGAL_ENTITY_ID 					= XEP.LEGAL_ENTITY_ID
	AND PCDLA.RECVR_PA_DATE						BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
	AND PPAV.ORG_ID								= PPPAV.ORG_ID 
	AND nvl(TO_NUMBER(SUBSTR(PPALA.FA_PERIOD_NAME, 1, 2)),TO_NUMBER(SUBSTR(PPPAV.PERIOD_NAME, 1, 2))) <=  TO_NUMBER(SUBSTR(PPPAV.PERIOD_NAME, 1, 2))
	AND PPAA.PROJECT_ASSET_ID 					= PPAAT.PROJECT_ASSET_ID
	AND PPEV.proj_element_id					= PPAAT.TASK_ID(+)
	/*ADDED*/
	and ppav.PROJECT_ID                          = pei.PROJECT_ID
    and ppev.proj_element_id                     = pei.TASK_ID
	and pei.EXPENDITURE_ITEM_ID                 = PCDLA.EXPENDITURE_ITEM_ID
    AND PEI.task_id                             = PCDLA.task_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 PRJ_XT.source_distribution_type(+)    = 'R'
AND PEI.original_dist_id                  = AP_XT.source_distribution_id_num_1(+)
AND AP_XT.source_distribution_type(+)     = 'AP_INV_DIST'
	--AND PPAV.PROJECT_ID							= '300000004288440'
	--AND PPAV.segment1 = 'TER524524HYC17CRP3TEST'
	AND (XEP.LEGAL_ENTITY_ID            		IN (:P_LEGAL_ENTITY)
    OR  'All'                					IN (:P_LEGAL_ENTITY||'All'))
	AND (GCC.SEGMENT2            >= (:P_SELL_METHOD_FROM )
    OR  'All'                					IN ((:P_SELL_METHOD_FROM)||'All'))
	AND (GCC.SEGMENT2             <= (:P_SELL_METHOD_TO )
    OR  'All'                					IN ((:P_SELL_METHOD_TO )||'All')) 
	
	AND (GCC.SEGMENT3            >= (:P_COST_CENTER_FROM )
    OR  'All'                					IN ((:P_COST_CENTER_FROM )||'All'))
	AND (GCC.SEGMENT3            <= (:P_COST_CENTER_TO)
    OR  'All'                					IN ((:P_COST_CENTER_TO)||'All'))
	
	AND (GCC.SEGMENT4             >= (:P_NATURAL_ACCOUNT_FROM )
    OR  'All'                					IN ((:P_NATURAL_ACCOUNT_FROM )||'All'))
	AND (GCC.SEGMENT4            <= (:P_NATURAL_ACCOUNT_TO)
    OR  'All'                					IN ((:P_NATURAL_ACCOUNT_TO)||'All')) 
	
	AND (GCC.SEGMENT5            >= (:P_GL_LOCATION_FROM)
    OR  'All'                					IN ((:P_GL_LOCATION_FROM)||'All'))
	AND (GCC.SEGMENT5            <= (:P_GL_LOCATION_TO)
    OR  'All'                					IN ((:P_GL_LOCATION_TO)||'All')) 
	
	AND (GCC.SEGMENT6            >= (:P_BRAND_FROM)
    OR  'All'                					IN ((:P_BRAND_FROM)||'All'))
	AND (GCC.SEGMENT6           <= (:P_BRAND_TO)
    OR  'All'                					IN ((:P_BRAND_TO)||'All'))  
	
	
	/*AND CAST(GCC.SEGMENT2 AS NUMERIC)		BETWEEN CAST(:P_SELL_METHOD_FROM AS NUMERIC) AND CAST(:P_SELL_METHOD_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT3 AS NUMERIC)		BETWEEN CAST(:P_COST_CENTER_FROM AS NUMERIC) AND CAST(:P_COST_CENTER_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT4 AS NUMERIC)		BETWEEN CAST(:P_NATURAL_ACCOUNT_FROM AS NUMERIC) AND CAST(:P_NATURAL_ACCOUNT_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT5 AS NUMERIC)		BETWEEN CAST(:P_GL_LOCATION_FROM AS NUMERIC) AND CAST(:P_GL_LOCATION_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT6 AS NUMERIC)		BETWEEN CAST(:P_BRAND_FROM AS NUMERIC) AND CAST(:P_BRAND_TO AS NUMERIC)*/
	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'))
	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 (FCV.SEGMENT1            				IN (:P_MAJOR_CATEGORY)
    OR  'All'                					IN (:P_MAJOR_CATEGORY||'All'))
	AND (FCV.SEGMENT2            				IN (:P_MINOR_CATEGORY)
    OR  'All'                					IN (:P_MINOR_CATEGORY||'All')) 
union all

SELECT /*+ PARALLEL(48) */
	PPAV.PROJECT_ID,
	PPEV.PROJ_ELEMENT_ID,
	PPAV.SEGMENT1 PROJECT_NUMBER,
	PPAV.NAME PROJECT_NAME,
	PPAV.PROJECT_STATUS_CODE PROJECT_STATUS,
	PPTV.PROJECT_TYPE PROJECT_TYPE,
	PCCV.CLASS_CODE PROJECT_CLASS_CODE,
	PPNFV.LIST_NAME PROJECT_MANAGER,
	PPEV.NAME TASK_NAME,
	PPEV.ELEMENT_NUMBER TASK_NUMBER,
	PPEV.SERVICE_TYPE_CODE TASK_SERVICE_TYPE,
	null CIP_COST_AMOUNT,
	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,
	NULL MANUFACTURER_NAME,
	NULL CAPITAL_HOLD,
	NULL ASSET_NUMBER,
	NULL ASSET_NAME,
	NULL ESTIMATED_IN_SERVICE_DATE,
	NULL ACTUAL_IN_SERVICE_DATE,
	NULL SERIAL_NUMBER,
	NULL TAG_NUMBER,
	NULL PROJECT_ASSET_TYPE,
	NULL ASSET_BOOK,
	NULL ESTIMATED_ASSET_UNITS,
	NULL ACTUAL_ASSET_UNITS,
	NULL ASSET_CATEGORY,
	NULL TOOL_PROCESS_CODE,
	NULL BRAND,
	NULL TOOL_CLASS,
	NULL SKU,
	NULL CUSTOM_INTERCO_ASSET_TRANSFER,
	NULL LEGACY_ASSET_NUMBER,
	NULL TOOL_TONNAGE,
	NULL TOOL_MAKER,
	NULL FA_ADDITIONAL1,
	NULL FA_ADDITIONAL2,
	NULL ASSET_KEY,
	NULL COUNTRY,
	NULL STATE,
	NULL CITY,
	NULL BUILDING,
	NULL SITE,
	/*GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7||'.'||GCC.SEGMENT8||'.'||GCC.SEGMENT9 GL_CODE_COMBINATION,*/
	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_CODE_COMBINATION,
	HOUFT.NAME TASK_ORG_NAME,
	XEP.NAME LEGAL_ENTITY_NAME,
	PPAV.ATTRIBUTE1 GL_LOCATION,
	PPAV.ATTRIBUTE2 GL_SELLING_METHOD,
	 3 CODE_COMBINATION_ID 
FROM 
	PJF_PROJECTS_ALL_VL PPAV,
	PJF_PROJECT_TYPES_VL PPTV,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES PPC,
	PJF_LATESTPROJECTMANAGER_V PLV,
	PER_PERSON_NAMES_F_V PPNFV,
	PJF_PROJ_ELEMENTS_VL PPEV,
	pjc_cost_dist_lines_all PCDLA,
	PJC_PRJ_ASSETS_ALL PPAA,
	PJC_PRJ_ASSET_LN_DETS PPALD,
	PJC_PRJ_ASSET_LNS_ALL PPALA,
	GL_CODE_COMBINATIONS GCC,
	HR_ORGANIZATION_UNITS_F_TL HOUFT,
	XLE_ENTITY_PROFILES XEP,
	PJF_P_PERIODS_ALL_V PPPAV,
	pjc_exp_items_all pei,
	SEC_TBL ST,
	xla_tbl PRJ_XT,
	xla_tbl AP_XT
WHERE
	1=1
	AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
	AND PPAV.PROJECT_TYPE_ID 					= PPTV.PROJECT_TYPE_ID
	--AND PPAV.SEGMENT1							= 'US CUSTOM Project6'
	AND PPAV.PROJECT_ID 						= PPC.PROJECT_ID
	AND PPC.CLASS_CODE_ID 						= PCCV.CLASS_CODE_ID
	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 PPAV.project_id                  		= PCDLA.project_id
	AND PCDLA.task_id             				= PPEV.proj_element_id
	AND PPAV.PROJECT_ID 						= PPEV.PROJECT_ID
	AND PCDLA.EXPENDITURE_ITEM_ID				= PPALD.EXPENDITURE_ITEM_ID(+)
	AND PCDLA.LINE_NUM							= PPALD.LINE_NUM(+)
	AND PPAV.PROJECT_ID							= PPALA.PROJECT_ID(+)
	AND PPEV.proj_element_id					= PPALA.TASK_ID(+)
	AND PPAA.PROJECT_ASSET_ID  = 0
	AND PPALA.PROJECT_ASSET_ID					= 0
	AND PPALA.PROJECT_ASSET_LINE_DETAIL_ID		= PPALD.PROJECT_ASSET_LINE_DETAIL_ID(+)
	--AND PPALA.CIP_CCID 							= GCC.CODE_COMBINATION_ID(+)
	AND PPEV.CARRYING_OUT_ORGANIZATION_ID 		= HOUFT.ORGANIZATION_ID
	AND SYSDATE 								BETWEEN HOUFT.EFFECTIVE_START_DATE AND HOUFT.EFFECTIVE_END_DATE
	AND nvl(TO_NUMBER(SUBSTR(PPALA.FA_PERIOD_NAME, 1, 2)),TO_NUMBER(SUBSTR(PPPAV.PERIOD_NAME, 1, 2))) <=  TO_NUMBER(SUBSTR(PPPAV.PERIOD_NAME, 1, 2))
	AND HOUFT.LANGUAGE							= USERENV('LANG')
	AND PPTV.ENABLE_CAPITALIZATION_FLAG 		= 'Y'
	AND PPAV.TEMPLATE_FLAG 						= 'N'
	AND PCDLA.CAPITALIZABLE_FLAG				= 'Y'
	AND PCCV.CLASS_CODE 						IN ('CER','MER','TER')
	AND PPAV.LEGAL_ENTITY_ID 					= XEP.LEGAL_ENTITY_ID
	AND PCDLA.RECVR_PA_DATE						BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
	AND PPAV.ORG_ID								= PPPAV.ORG_ID
	/*ADDED*/
	and ppav.PROJECT_ID                          = pei.PROJECT_ID
    and ppev.proj_element_id                     = pei.TASK_ID
	and pei.EXPENDITURE_ITEM_ID                 = PCDLA.EXPENDITURE_ITEM_ID
    AND PEI.task_id                             = PCDLA.task_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 PRJ_XT.source_distribution_type(+)    = 'R'
AND PEI.original_dist_id                  = AP_XT.source_distribution_id_num_1(+)
AND AP_XT.source_distribution_type(+)     = 'AP_INV_DIST'
	AND (XEP.LEGAL_ENTITY_ID            		IN (:P_LEGAL_ENTITY)
    OR  'All'                					IN (:P_LEGAL_ENTITY||'All'))
	AND (GCC.SEGMENT2            >= (:P_SELL_METHOD_FROM )
    OR  'All'                					IN ((:P_SELL_METHOD_FROM)||'All'))
	AND (GCC.SEGMENT2             <= (:P_SELL_METHOD_TO )
    OR  'All'                					IN ((:P_SELL_METHOD_TO )||'All')) 
	
	AND (GCC.SEGMENT3            >= (:P_COST_CENTER_FROM )
    OR  'All'                					IN ((:P_COST_CENTER_FROM )||'All'))
	AND (GCC.SEGMENT3            <= (:P_COST_CENTER_TO)
    OR  'All'                					IN ((:P_COST_CENTER_TO)||'All'))
	
	AND (GCC.SEGMENT4             >= (:P_NATURAL_ACCOUNT_FROM )
    OR  'All'                					IN ((:P_NATURAL_ACCOUNT_FROM )||'All'))
	AND (GCC.SEGMENT4            <= (:P_NATURAL_ACCOUNT_TO)
    OR  'All'                					IN ((:P_NATURAL_ACCOUNT_TO)||'All')) 
	
	AND (GCC.SEGMENT5            >= (:P_GL_LOCATION_FROM)
    OR  'All'                					IN ((:P_GL_LOCATION_FROM)||'All'))
	AND (GCC.SEGMENT5            <= (:P_GL_LOCATION_TO)
    OR  'All'                					IN ((:P_GL_LOCATION_TO)||'All')) 
	
	AND (GCC.SEGMENT6            >= (:P_BRAND_FROM)
    OR  'All'                					IN ((:P_BRAND_FROM)||'All'))
	AND (GCC.SEGMENT6           <= (:P_BRAND_TO)
    OR  'All'                					IN ((:P_BRAND_TO)||'All'))  
	
	/*AND CAST(GCC.SEGMENT2 AS NUMERIC)		BETWEEN CAST(:P_SELL_METHOD_FROM AS NUMERIC) AND CAST(:P_SELL_METHOD_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT3 AS NUMERIC)		BETWEEN CAST(:P_COST_CENTER_FROM AS NUMERIC) AND CAST(:P_COST_CENTER_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT4 AS NUMERIC)		BETWEEN CAST(:P_NATURAL_ACCOUNT_FROM AS NUMERIC) AND CAST(:P_NATURAL_ACCOUNT_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT5 AS NUMERIC)		BETWEEN CAST(:P_GL_LOCATION_FROM AS NUMERIC) AND CAST(:P_GL_LOCATION_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT6 AS NUMERIC)		BETWEEN CAST(:P_BRAND_FROM AS NUMERIC) AND CAST(:P_BRAND_TO AS NUMERIC)*/
	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'))
	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)
UNION ALL
SELECT /*+ PARALLEL(48) */
	PPAV.PROJECT_ID,
	PPEV.PROJ_ELEMENT_ID,
	PPAV.SEGMENT1 PROJECT_NUMBER,
	PPAV.NAME PROJECT_NAME,
	PPAV.PROJECT_STATUS_CODE PROJECT_STATUS,
	PPTV.PROJECT_TYPE PROJECT_TYPE,
	PCCV.CLASS_CODE PROJECT_CLASS_CODE,
	PPNFV.LIST_NAME PROJECT_MANAGER,
	PPEV.NAME TASK_NAME,
	PPEV.ELEMENT_NUMBER TASK_NUMBER,
	PPEV.SERVICE_TYPE_CODE TASK_SERVICE_TYPE,
	null CIP_COST_AMOUNT,
	0 COST_INTERFACED_TO_FA,
	NULL MANUFACTURER_NAME,
	NULL CAPITAL_HOLD,
	NULL ASSET_NUMBER,
	NULL ASSET_NAME,
	NULL ESTIMATED_IN_SERVICE_DATE,
	NULL ACTUAL_IN_SERVICE_DATE,
	NULL SERIAL_NUMBER,
	NULL TAG_NUMBER,
	NULL PROJECT_ASSET_TYPE,
	NULL ASSET_BOOK,
	NULL ESTIMATED_ASSET_UNITS,
	NULL ACTUAL_ASSET_UNITS,
	NULL ASSET_CATEGORY,
	NULL TOOL_PROCESS_CODE,
	NULL BRAND,
	NULL TOOL_CLASS,
	NULL SKU,
	NULL CUSTOM_INTERCO_ASSET_TRANSFER,
	NULL LEGACY_ASSET_NUMBER,
	NULL TOOL_TONNAGE,
	NULL TOOL_MAKER,
	NULL FA_ADDITIONAL1,
	NULL FA_ADDITIONAL2,
	NULL ASSET_KEY,
	NULL COUNTRY,
	NULL STATE,
	NULL CITY,
	NULL BUILDING,
	NULL SITE,
	/*GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7||'.'||GCC.SEGMENT8||'.'||GCC.SEGMENT9 GL_CODE_COMBINATION,*/
	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_CODE_COMBINATION,
	HOUFT.NAME TASK_ORG_NAME,
	XEP.NAME LEGAL_ENTITY_NAME,
	PPAV.ATTRIBUTE1 GL_LOCATION,
	PPAV.ATTRIBUTE2 GL_SELLING_METHOD,
	3 CODE_COMBINATION_ID

FROM 
	PJF_PROJECTS_ALL_VL PPAV,
	PJF_PROJECT_TYPES_VL PPTV,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES PPC,
	PJF_LATESTPROJECTMANAGER_V PLV,
	PER_PERSON_NAMES_F_V PPNFV,
	PJF_PROJ_ELEMENTS_VL PPEV,
	PJC_COST_DIST_LINES_ALL PCDLA,
	HR_ORGANIZATION_UNITS_F_TL HOUFT,
	XLE_ENTITY_PROFILES XEP,
	PJF_P_PERIODS_ALL_V PPPAV,
	GL_CODE_COMBINATIONS GCC,
	pjc_exp_items_all PEI,
	SEC_TBL ST,
	--XLA_TBL XT
	xla_tbl PRJ_XT
	, xla_tbl AP_XT
WHERE
	1=1
	AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
	AND PPAV.PROJECT_TYPE_ID 					= PPTV.PROJECT_TYPE_ID
	--AND PPAV.SEGMENT1							= 'US CUSTOM Project6'
	AND PPAV.PROJECT_ID 						= PPC.PROJECT_ID
	AND PPC.CLASS_CODE_ID 						= PCCV.CLASS_CODE_ID
	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 PPAV.PROJECT_ID 						= PPEV.PROJECT_ID
	AND PPAV.project_id                  		= PCDLA.project_id
	AND PCDLA.task_id             				= PPEV.proj_element_id
	AND PPTV.ENABLE_CAPITALIZATION_FLAG 		= 'Y'
	AND PPAV.TEMPLATE_FLAG 						= 'N'
	AND PCDLA.CAPITALIZABLE_FLAG				= 'Y'
	AND PCCV.CLASS_CODE 						IN ('CER','MER','TER')
	--and PPAV.project_id = 300000012087477
	--AND GCC.CODE_COMBINATION_ID = XT.CODE_COMBINATION_ID
	--AND PCDLA.ACCT_EVENT_ID = XT.EVENT_ID
	AND pcdla.task_id not in (select task_id from PJC_COST_DIST_LINES_ALL, GL_CODE_COMBINATIONS where CODE_COMBINATION_ID = nvl(RAW_COST_CR_CCID, RAW_COST_DR_CCID))
	AND pcdla.task_id not in(select proj_element_id from PJF_PROJ_ELEMENTS_VL, PJC_PRJ_ASSET_LNS_ALL where proj_element_id = TASK_ID)
	--AND pcdla.task_id not in(select proj_element_id from PJF_PROJ_ELEMENTS_VL, PJC_PRJ_ASSETS_ALL where proj_element_id = TASK_ID) /*CHCEK WITH KHADIJA THERE IS NO TASK_ID IN PJC_PRJ_ASSETS_ALL TABLE*/
	AND PCDLA.EXPENDITURE_ITEM_ID				NOT IN (SELECT 
														PLAD.EXPENDITURE_ITEM_ID 
														FROM 
														PJC_PRJ_ASSET_LNS_ALL PLA,
														PJC_PRJ_ASSET_LN_DETS PLAD
														WHERE 
														1=1
														AND PLA.PROJECT_ID = PPAV.PROJECT_ID	
														AND PLA.PROJECT_ASSET_LINE_DETAIL_ID = PLAD.PROJECT_ASSET_LINE_DETAIL_ID
														)
	
															
	AND PPEV.CARRYING_OUT_ORGANIZATION_ID 		= HOUFT.ORGANIZATION_ID
	AND SYSDATE 								BETWEEN HOUFT.EFFECTIVE_START_DATE AND HOUFT.EFFECTIVE_END_DATE
	AND HOUFT.LANGUAGE							= USERENV('LANG')
	AND PPAV.LEGAL_ENTITY_ID 					= XEP.LEGAL_ENTITY_ID
	AND PCDLA.RECVR_PA_DATE						BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
	AND PPAV.ORG_ID								= PPPAV.ORG_ID
	/*ADDED*/
	and PPAV.PROJECT_ID                          = PEI.PROJECT_ID
    and PPEV.proj_element_id                     = PEI.TASK_ID
	and PEI.EXPENDITURE_ITEM_ID                 = PCDLA.EXPENDITURE_ITEM_ID
    AND PEI.task_id                             = PCDLA.task_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 PRJ_XT.source_distribution_type(+)    = 'R'
AND PEI.original_dist_id                  = AP_XT.source_distribution_id_num_1(+)
AND AP_XT.source_distribution_type(+)     = 'AP_INV_DIST'
	AND (XEP.LEGAL_ENTITY_ID            		IN (:P_LEGAL_ENTITY)
    OR  'All'                					IN (:P_LEGAL_ENTITY||'All'))
	AND (GCC.SEGMENT2            >= (:P_SELL_METHOD_FROM )
    OR  'All'                					IN ((:P_SELL_METHOD_FROM)||'All'))
	AND (GCC.SEGMENT2             <= (:P_SELL_METHOD_TO )
    OR  'All'                					IN ((:P_SELL_METHOD_TO )||'All')) 
	
	AND (GCC.SEGMENT3            >= (:P_COST_CENTER_FROM )
    OR  'All'                					IN ((:P_COST_CENTER_FROM )||'All'))
	AND (GCC.SEGMENT3            <= (:P_COST_CENTER_TO)
    OR  'All'                					IN ((:P_COST_CENTER_TO)||'All'))
	
	AND (GCC.SEGMENT4             >= (:P_NATURAL_ACCOUNT_FROM )
    OR  'All'                					IN ((:P_NATURAL_ACCOUNT_FROM )||'All'))
	AND (GCC.SEGMENT4            <= (:P_NATURAL_ACCOUNT_TO)
    OR  'All'                					IN ((:P_NATURAL_ACCOUNT_TO)||'All')) 
	
	AND (GCC.SEGMENT5            >= (:P_GL_LOCATION_FROM)
    OR  'All'                					IN ((:P_GL_LOCATION_FROM)||'All'))
	AND (GCC.SEGMENT5            <= (:P_GL_LOCATION_TO)
    OR  'All'                					IN ((:P_GL_LOCATION_TO)||'All')) 
	
	AND (GCC.SEGMENT6            >= (:P_BRAND_FROM)
    OR  'All'                					IN ((:P_BRAND_FROM)||'All'))
	AND (GCC.SEGMENT6           <= (:P_BRAND_TO)
    OR  'All'                					IN ((:P_BRAND_TO)||'All'))  
	
	/*AND CAST(GCC.SEGMENT2 AS NUMERIC)		BETWEEN CAST(:P_SELL_METHOD_FROM AS NUMERIC) AND CAST(:P_SELL_METHOD_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT3 AS NUMERIC)		BETWEEN CAST(:P_COST_CENTER_FROM AS NUMERIC) AND CAST(:P_COST_CENTER_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT4 AS NUMERIC)		BETWEEN CAST(:P_NATURAL_ACCOUNT_FROM AS NUMERIC) AND CAST(:P_NATURAL_ACCOUNT_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT5 AS NUMERIC)		BETWEEN CAST(:P_GL_LOCATION_FROM AS NUMERIC) AND CAST(:P_GL_LOCATION_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT6 AS NUMERIC)		BETWEEN CAST(:P_BRAND_FROM AS NUMERIC) AND CAST(:P_BRAND_TO AS NUMERIC)*/
	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'))
	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)
UNION ALL
SELECT /*+ PARALLEL(48) */
	PPAV.PROJECT_ID,
	PPEV.PROJ_ELEMENT_ID,
	PPAV.SEGMENT1 PROJECT_NUMBER,
	PPAV.NAME PROJECT_NAME,
	PPAV.PROJECT_STATUS_CODE PROJECT_STATUS,
	PPTV.PROJECT_TYPE PROJECT_TYPE,
	PCCV.CLASS_CODE PROJECT_CLASS_CODE,
	PPNFV.LIST_NAME PROJECT_MANAGER,
	PPEV.NAME TASK_NAME,
	PPEV.ELEMENT_NUMBER TASK_NUMBER,
	PPEV.SERVICE_TYPE_CODE TASK_SERVICE_TYPE,
	null CIP_COST_AMOUNT,
	0 COST_INTERFACED_TO_FA,
	NULL MANUFACTURER_NAME,
	NULL CAPITAL_HOLD,
	NULL ASSET_NUMBER,
	NULL ASSET_NAME,
	NULL ESTIMATED_IN_SERVICE_DATE,
	NULL ACTUAL_IN_SERVICE_DATE,
	NULL SERIAL_NUMBER,
	NULL TAG_NUMBER,
	NULL PROJECT_ASSET_TYPE,
	NULL ASSET_BOOK,
	NULL ESTIMATED_ASSET_UNITS,
	NULL ACTUAL_ASSET_UNITS,
	NULL ASSET_CATEGORY,
	NULL TOOL_PROCESS_CODE,
	NULL BRAND,
	NULL TOOL_CLASS,
	NULL SKU,
	NULL CUSTOM_INTERCO_ASSET_TRANSFER,
	NULL LEGACY_ASSET_NUMBER,
	NULL TOOL_TONNAGE,
	NULL TOOL_MAKER,
	NULL FA_ADDITIONAL1,
	NULL FA_ADDITIONAL2,
	NULL ASSET_KEY,
	NULL COUNTRY,
	NULL STATE,
	NULL CITY,
	NULL BUILDING,
	NULL SITE,
	/*GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7||'.'||GCC.SEGMENT8||'.'||GCC.SEGMENT9 GL_CODE_COMBINATION,*/
	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_CODE_COMBINATION,
	HOUFT.NAME TASK_ORG_NAME,
	XEP.NAME LEGAL_ENTITY_NAME,
	PPAV.ATTRIBUTE1 GL_LOCATION,
	PPAV.ATTRIBUTE2 GL_SELLING_METHOD,
	3 CODE_COMBINATION_ID

FROM 
	PJF_PROJECTS_ALL_VL PPAV,
	PJF_PROJECT_TYPES_VL PPTV,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES PPC,
	PJF_LATESTPROJECTMANAGER_V PLV,
	PER_PERSON_NAMES_F_V PPNFV,
	PJF_PROJ_ELEMENTS_VL PPEV,
	PJC_COST_DIST_LINES_ALL PCDLA,
	PJC_EXP_ITEMS_ALL PEIA,
	HR_ORGANIZATION_UNITS_F_TL HOUFT,
	XLE_ENTITY_PROFILES XEP,
	PJF_P_PERIODS_ALL_V PPPAV,
	GL_CODE_COMBINATIONS GCC,
	pjc_exp_items_all pei,
	SEC_TBL ST,
	xla_tbl PRJ_XT
	, xla_tbl AP_XT
WHERE
	1=1
	AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
	AND PPAV.PROJECT_TYPE_ID 					= PPTV.PROJECT_TYPE_ID
	AND PPAV.SEGMENT1							= 'US CUSTOM Project6'
	AND PPAV.PROJECT_ID 						= PPC.PROJECT_ID
	AND PPC.CLASS_CODE_ID 						= PCCV.CLASS_CODE_ID
	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 PPAV.PROJECT_ID 						= PPEV.PROJECT_ID
	AND PPAV.project_id                  		= PCDLA.project_id
	AND PCDLA.task_id             				= PPEV.proj_element_id
	AND PPTV.ENABLE_CAPITALIZATION_FLAG 		= 'Y'
	AND PPAV.TEMPLATE_FLAG 						= 'N'
	AND PCDLA.CAPITALIZABLE_FLAG				= 'Y'
	AND PCCV.CLASS_CODE 						IN ('CER','MER','TER')
	--and PPAV.project_id = 300000012087477
	AND PPAV.PROJECT_ID							= PEIA.PROJECT_ID
	AND PPEV.proj_element_id					= PEIA.TASK_ID
	AND PCDLA.EXPENDITURE_ITEM_ID				= PEIA.EXPENDITURE_ITEM_ID
	--AND GCC.CODE_COMBINATION_ID 				= nvl(pcdla.RAW_COST_CR_CCID,pcdla.RAW_COST_DR_CCID)
	--AND pcdla.task_id not in (select task_id from PJC_COST_DIST_LINES_ALL, GL_CODE_COMBINATIONS where CODE_COMBINATION_ID = RAW_COST_DR_CCID)
	AND PCDLA.EXPENDITURE_ITEM_ID				NOT IN (SELECT 
														PLAD.EXPENDITURE_ITEM_ID 
														FROM 
														PJC_PRJ_ASSET_LNS_ALL PLA,
														PJC_PRJ_ASSET_LN_DETS PLAD
														WHERE 
														1=1
														AND PLA.PROJECT_ID = PPAV.PROJECT_ID	
														AND PLA.PROJECT_ASSET_LINE_DETAIL_ID = PLAD.PROJECT_ASSET_LINE_DETAIL_ID
														)
	AND PPAV.PROJECT_ID NOT IN ( SELECT PPAVI.PROJECT_ID 
									FROM PJF_PROJECTS_ALL_VL PPAVI,
									PJC_PRJ_ASSETS_ALL PPAAI,
									PJC_PRJ_ASSET_ASGS PPAATI,
									PJF_PROJ_ELEMENTS_VL PPEVI
									WHERE PPAVI.PROJECT_ID= PPAAI.PROJECT_ID 
									AND PPAAI.PROJECT_ASSET_ID = PPAATI.PROJECT_ASSET_ID
									AND PPEVI.PROJ_ELEMENT_ID = PPAATI.TASK_ID(+))															
	AND PPEV.CARRYING_OUT_ORGANIZATION_ID 		= HOUFT.ORGANIZATION_ID
	AND SYSDATE 								BETWEEN HOUFT.EFFECTIVE_START_DATE AND HOUFT.EFFECTIVE_END_DATE
	AND HOUFT.LANGUAGE							= USERENV('LANG')
	AND PPAV.LEGAL_ENTITY_ID 					= XEP.LEGAL_ENTITY_ID
	AND PCDLA.RECVR_PA_DATE						BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
	AND PPAV.ORG_ID								= PPPAV.ORG_ID
	/*ADDED*/
	and PPAV.PROJECT_ID                          = PEI.PROJECT_ID
    and PPEV.proj_element_id                     = PEI.TASK_ID
	and PEI.EXPENDITURE_ITEM_ID                 = PCDLA.EXPENDITURE_ITEM_ID
    AND PEI.task_id                             = PCDLA.task_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 PRJ_XT.source_distribution_type(+)    = 'R'
AND PEI.original_dist_id                  = AP_XT.source_distribution_id_num_1(+)
AND AP_XT.source_distribution_type(+)     = 'AP_INV_DIST'
	--AND PCDLA.raw_cost_dr_ccid                = GCC.code_combination_id(+)
	AND (XEP.LEGAL_ENTITY_ID            		IN (:P_LEGAL_ENTITY)
    OR  'All'                					IN (:P_LEGAL_ENTITY||'All'))
	AND (GCC.SEGMENT2            >= (:P_SELL_METHOD_FROM )
    OR  'All'                					IN ((:P_SELL_METHOD_FROM)||'All'))
	AND (GCC.SEGMENT2             <= (:P_SELL_METHOD_TO )
    OR  'All'                					IN ((:P_SELL_METHOD_TO )||'All')) 
	
	AND (GCC.SEGMENT3            >= (:P_COST_CENTER_FROM )
    OR  'All'                					IN ((:P_COST_CENTER_FROM )||'All'))
	AND (GCC.SEGMENT3            <= (:P_COST_CENTER_TO)
    OR  'All'                					IN ((:P_COST_CENTER_TO)||'All'))
	
	AND (GCC.SEGMENT4             >= (:P_NATURAL_ACCOUNT_FROM )
    OR  'All'                					IN ((:P_NATURAL_ACCOUNT_FROM )||'All'))
	AND (GCC.SEGMENT4            <= (:P_NATURAL_ACCOUNT_TO)
    OR  'All'                					IN ((:P_NATURAL_ACCOUNT_TO)||'All')) 
	
	AND (GCC.SEGMENT5            >= (:P_GL_LOCATION_FROM)
    OR  'All'                					IN ((:P_GL_LOCATION_FROM)||'All'))
	AND (GCC.SEGMENT5            <= (:P_GL_LOCATION_TO)
    OR  'All'                					IN ((:P_GL_LOCATION_TO)||'All')) 
	
	AND (GCC.SEGMENT6            >= (:P_BRAND_FROM)
    OR  'All'                					IN ((:P_BRAND_FROM)||'All'))
	AND (GCC.SEGMENT6           <= (:P_BRAND_TO)
    OR  'All'                					IN ((:P_BRAND_TO)||'All'))  
	
	/*AND CAST(GCC.SEGMENT2 AS NUMERIC)		BETWEEN CAST(:P_SELL_METHOD_FROM AS NUMERIC) AND CAST(:P_SELL_METHOD_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT3 AS NUMERIC)		BETWEEN CAST(:P_COST_CENTER_FROM AS NUMERIC) AND CAST(:P_COST_CENTER_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT4 AS NUMERIC)		BETWEEN CAST(:P_NATURAL_ACCOUNT_FROM AS NUMERIC) AND CAST(:P_NATURAL_ACCOUNT_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT5 AS NUMERIC)		BETWEEN CAST(:P_GL_LOCATION_FROM AS NUMERIC) AND CAST(:P_GL_LOCATION_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT6 AS NUMERIC)		BETWEEN CAST(:P_BRAND_FROM AS NUMERIC) AND CAST(:P_BRAND_TO AS NUMERIC)*/
	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'))
	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)

	
	UNION ALL 
SELECT /*+ PARALLEL(48) */
	PPAV.PROJECT_ID,
	1 PROJ_ELEMENT_ID,
	PPAV.SEGMENT1 PROJECT_NUMBER,
	PPAV.NAME PROJECT_NAME,
	PPAV.PROJECT_STATUS_CODE PROJECT_STATUS,
	PPTV.PROJECT_TYPE PROJECT_TYPE,
	PCCV.CLASS_CODE PROJECT_CLASS_CODE,
	PPNFV.LIST_NAME PROJECT_MANAGER,
	Null TASK_NAME,
	null TASK_NUMBER,
	null TASK_SERVICE_TYPE,
	null CIP_COST_AMOUNT,
	0 COST_INTERFACED_TO_FA,
	NULL MANUFACTURER_NAME,
	NULL CAPITAL_HOLD,
	NULL ASSET_NUMBER,
	NULL ASSET_NAME,
	NULL ESTIMATED_IN_SERVICE_DATE,
	NULL ACTUAL_IN_SERVICE_DATE,
	NULL SERIAL_NUMBER,
	NULL TAG_NUMBER,
	NULL PROJECT_ASSET_TYPE,
	NULL ASSET_BOOK,
	NULL ESTIMATED_ASSET_UNITS,
	NULL ACTUAL_ASSET_UNITS,
	NULL ASSET_CATEGORY,
	NULL TOOL_PROCESS_CODE,
	NULL BRAND,
	NULL TOOL_CLASS,
	NULL SKU,
	NULL CUSTOM_INTERCO_ASSET_TRANSFER,
	NULL LEGACY_ASSET_NUMBER,
	NULL TOOL_TONNAGE,
	NULL TOOL_MAKER,
	NULL FA_ADDITIONAL1,
	NULL FA_ADDITIONAL2,
	NULL ASSET_KEY,
	NULL COUNTRY,
	NULL STATE,
	NULL CITY,
	NULL BUILDING,
	NULL SITE,
	/* GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7||'.'||GCC.SEGMENT8||'.'||GCC.SEGMENT9 */  null GL_CODE_COMBINATION,
	null TASK_ORG_NAME,
	XEP.NAME LEGAL_ENTITY_NAME,
	PPAV.ATTRIBUTE1 GL_LOCATION,
	PPAV.ATTRIBUTE2 GL_SELLING_METHOD,
	--GCC.
	2 CODE_COMBINATION_ID

FROM 
	PJF_PROJECTS_ALL_VL PPAV,
	PJF_PROJECT_TYPES_VL PPTV,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES PPC,
	PJF_LATESTPROJECTMANAGER_V PLV,
	PER_PERSON_NAMES_F_V PPNFV,
	PJC_COST_DIST_LINES_ALL PCDLA,
	PJC_EXP_ITEMS_ALL PEIA,
	XLE_ENTITY_PROFILES XEP,
	PJF_P_PERIODS_ALL_V PPPAV,
	 /* GL_CODE_COMBINATIONS GCC,
	XLA_TBL XT,  */
	SEC_TBL ST
WHERE
	1=1
	AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
	AND PPAV.PROJECT_TYPE_ID 					= PPTV.PROJECT_TYPE_ID
	--AND PPAV.SEGMENT1							= 'US CUSTOM Project6'
	AND PPAV.PROJECT_ID 						= PPC.PROJECT_ID
	AND PPC.CLASS_CODE_ID 						= PCCV.CLASS_CODE_ID
	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 PPAV.project_id                  		= PCDLA.project_id
	AND PPTV.ENABLE_CAPITALIZATION_FLAG 		= 'Y'
	AND PPAV.TEMPLATE_FLAG 						= 'N'
	AND PCDLA.CAPITALIZABLE_FLAG				= 'Y'
	AND PCCV.CLASS_CODE 						IN ('CER','MER','TER')
	--and PPAV.project_id = 300000012087477
	AND PPAV.PROJECT_ID							= PEIA.PROJECT_ID
	 AND ppav.project_id not in (select  ppav1.project_id
								from 	PJF_PROJECTS_ALL_VL PPAV1, 	
										PJC_COST_DIST_LINES_ALL PCDLA1,
										GL_CODE_COMBINATIONS GCC,
										(
SELECT DISTINCT
GCC.CODE_COMBINATION_ID,
XAH.EVENT_ID
FROM
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
GL_LEDGERS GL,
GL_CODE_COMBINATIONS GCC
WHERE
1=1
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.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 XAH.APPLICATION_ID = 10036
AND XAL.CODE_COMBINATION_ID <> -1
AND UPPER(XAL.ACCOUNTING_CLASS_CODE) NOT LIKE '%CLEARING%'
)xt
								where PPAV1.project_id  = PCDLA1.project_id
								AND PCDLA1.ACCT_EVENT_ID	= XT.EVENT_ID
								AND XT.CODE_COMBINATION_ID	= GCC.CODE_COMBINATION_ID ) 
	AND PCDLA.EXPENDITURE_ITEM_ID				= PEIA.EXPENDITURE_ITEM_ID
	AND PCDLA.EXPENDITURE_ITEM_ID				NOT IN (SELECT 
														PLAD.EXPENDITURE_ITEM_ID 
														FROM 
														PJC_PRJ_ASSET_LNS_ALL PLA,
														PJC_PRJ_ASSET_LN_DETS PLAD
														WHERE 
														1=1
														AND PLA.PROJECT_ID = PPAV.PROJECT_ID	
														AND PLA.PROJECT_ASSET_LINE_DETAIL_ID = PLAD.PROJECT_ASSET_LINE_DETAIL_ID
														)		 				    
	AND PPAV.LEGAL_ENTITY_ID 					= XEP.LEGAL_ENTITY_ID
	AND PCDLA.RECVR_PA_DATE						BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
	AND PPAV.ORG_ID								= PPPAV.ORG_ID
	/*  AND PCDLA.ACCT_EVENT_ID						= XT.EVENT_ID
	AND XT.CODE_COMBINATION_ID					= GCC.CODE_COMBINATION_ID  */
	AND (XEP.LEGAL_ENTITY_ID            		IN (:P_LEGAL_ENTITY)
    OR  'All'                					IN (:P_LEGAL_ENTITY||'All'))
	/* AND CAST(GCC.SEGMENT2 AS NUMERIC)		BETWEEN CAST(:P_SELL_METHOD_FROM AS NUMERIC) AND CAST(:P_SELL_METHOD_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT3 AS NUMERIC)		BETWEEN CAST(:P_COST_CENTER_FROM AS NUMERIC) AND CAST(:P_COST_CENTER_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT4 AS NUMERIC)		BETWEEN CAST(:P_NATURAL_ACCOUNT_FROM AS NUMERIC) AND CAST(:P_NATURAL_ACCOUNT_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT5 AS NUMERIC)		BETWEEN CAST(:P_GL_LOCATION_FROM AS NUMERIC) AND CAST(:P_GL_LOCATION_TO AS NUMERIC)
	AND CAST(GCC.SEGMENT6 AS NUMERIC)		BETWEEN CAST(:P_BRAND_FROM AS NUMERIC) AND CAST(:P_BRAND_TO AS NUMERIC) */
	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'))
	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)
)
GROUP BY
	PROJECT_ID,
	PROJ_ELEMENT_ID,
	PROJECT_NUMBER,
	PROJECT_NAME,
	PROJECT_STATUS,
	PROJECT_TYPE,
	PROJECT_CLASS_CODE,
	PROJECT_MANAGER,
	TASK_NAME,
	TASK_NUMBER,
	TASK_SERVICE_TYPE,
	MANUFACTURER_NAME,
	CAPITAL_HOLD,
	ASSET_CATEGORY,
	ASSET_NUMBER,
	ASSET_NAME,
	ESTIMATED_IN_SERVICE_DATE,
	ACTUAL_IN_SERVICE_DATE,
	TOOL_PROCESS_CODE,
	BRAND,
	TOOL_CLASS,
	SKU,
	CUSTOM_INTERCO_ASSET_TRANSFER,
	LEGACY_ASSET_NUMBER,
	TOOL_TONNAGE,
	TOOL_MAKER,
	FA_ADDITIONAL1,
	FA_ADDITIONAL2,
	ASSET_KEY,
	SERIAL_NUMBER,
	TAG_NUMBER,
	COUNTRY,
	STATE,
	CITY,
	BUILDING,
	SITE,
	PROJECT_ASSET_TYPE,
	ASSET_BOOK,
	ESTIMATED_ASSET_UNITS,
	ACTUAL_ASSET_UNITS,
	GL_CODE_COMBINATION,
	TASK_ORG_NAME,
	GL_LOCATION,
	GL_SELLING_METHOD,
	LEGAL_ENTITY_NAME,
	CODE_COMBINATION_ID
) MAIN_TBL,
RAW_COST RC
WHERE
1=1
AND MAIN_TBL.PROJECT_ID = RC.PROJECT_ID
AND MAIN_TBL.PROJ_ELEMENT_ID = RC.TASK_ID(+)
AND MAIN_TBL.CODE_COMBINATION_ID = RC.COST_CCID(+)
AND (NVL(RC.ACCT_RAW_COST, 0)-NVL(MAIN_TBL.COST_INTERFACED_TO_FA, 0))!=0
----AND rc.ACCT_RAW_COST <> 0
order BY
MAIN_TBL.PROJECT_NUMBER,
MAIN_TBL.TASK_NUMBER
:P_BRAND_FROM :P_BRAND_TO :P_COST_CENTER_FROM :P_COST_CENTER_TO :P_END_GL_PERIOD :P_GL_LOCATION_FROM :P_GL_LOCATION_TO :P_LEGAL_ENTITY :P_MAJOR_CATEGORY :P_MINOR_CATEGORY :P_NATURAL_ACCOUNT_FROM :P_NATURAL_ACCOUNT_TO :P_PROJECT_CLASS_CODE :P_PROJECT_NUMBER :P_SELL_METHOD_FROM :P_SELL_METHOD_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
XLA_AE_HEADERS	dimension	dimension
GL_CODE_COMBINATIONS	dimension	dimension
PJC_EXP_ITEMS_ALL	dimension	dimension
PJF_PROJECTS_ALL_VL	dimension	dimension
Cost Line	measure	measure
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
XLA_AE_LINES	23	17
XLA_AE_HEADERS	16	19
GL_CODE_COMBINATIONS	7	61
PJC_EXP_ITEMS_ALL	98	8
PJF_PROJECTS_ALL_VL	31	67
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.