Projects Asset Management Report

WITH LEDGER_NAME AS
(
	SELECT DISTINCT
	GL.NAME,
	PPAV.ORG_ID
	FROM
	PJF_PROJECTS_ALL_VL PPAV,
	HR_ORGANIZATION_INFORMATION_F HO,
	GL_LEDGERS GL
	WHERE
	PPAV.ORG_ID =HO.ORGANIZATION_ID
	AND HO.ORG_INFORMATION3=GL.LEDGER_ID

),
SEC_TBL AS
(
SELECT 
	DISTINCT 
	HOU.ORGANIZATION_ID
	FROM 
	HR_OPERATING_UNITS HOU,
	FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
	PER_ROLES_DN PRD,
	PER_USER_ROLES PUR,
	PER_USERS PU
WHERE 
	1=1
	AND FURDA.ORG_ID = HOU.ORGANIZATION_ID
	AND UPPER(FURDA.ROLE_NAME) IN ('XXC_PROJECT_MANAGER_JOB','XXC_PROJECT_INQUIRY_JOB','XXC_PROJECT_ACCOUNTANT_JOB','XXC_PROJECT_ACCOUNTING_MANAGER_JOB')
	AND UPPER(FURDA.ACTIVE_FLAG) = 'Y'
	AND UPPER(FURDA.ROLE_NAME) = UPPER(PRD.ROLE_COMMON_NAME)
	AND PRD.ROLE_ID = PUR.ROLE_ID
	AND PUR.USER_ID = PU.USER_ID
	AND UPPER(PU.USERNAME) = UPPER(:XDO_USER_NAME)
	AND PU.USER_GUID = FURDA.USER_GUID
)

SELECT 
	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,
	PPEV.NAME TASK_NAME,
	PPEV.ELEMENT_NUMBER TASK_NUMBER,
	PPEV.SERVICE_TYPE_CODE TASK_SERVICE_TYPE,
	PPAA.MANUFACTURER_NAME MANUFACTURER_NAME,
	PPAA.ASSET_NUMBER ASSET_NUMBER,
	PPAA.ASSET_NAME ASSET_NAME,
	PPAA.ASSET_DESCRIPTION,
	PPAA.ESTIMATED_IN_SERVICE_DATE ESTIMATED_IN_SERVICE_DATE,
	PPAA.DATE_PLACED_IN_SERVICE ACTUAL_IN_SERVICE_DATE,
	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,
	FAK.SEGMENT1||'.'||FAK.SEGMENT2||'.'||FAK.SEGMENT3||'.'||FAK.SEGMENT4||'.'||FAK.SEGMENT5||'.'||FAK.SEGMENT6||'.'||FAK.SEGMENT7||'.'||FAK.SEGMENT8||'.'||FAK.SEGMENT9||'.'||FAK.SEGMENT10 ASSET_KEY,
	GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7||'.'||GCC.SEGMENT8||'.'||GCC.SEGMENT9 GL_CODE_COMBINATION,
	FL.SEGMENT1 ||'-'||FL.SEGMENT2 ||'-'||FL.SEGMENT3||'-'||FL.SEGMENT4||'-'||FL.SEGMENT5 ASSET_LOCATION,
	PPAA.DEPRECIATE_FLAG
FROM 
	PJF_PROJECTS_ALL_VL PPAV,
	PJF_PROJECT_TYPES_VL PPTV,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES PPC,
	PJF_PROJ_ELEMENTS_VL PPEV,
	PJC_PRJ_ASSETS_ALL PPAA,
	PJC_PRJ_ASSET_ASGS PPAAS,
	FA_CATEGORIES_VL FCV,
	FA_ASSET_KEYWORDS FAK,
	GL_CODE_COMBINATIONS GCC,
	XLE_ENTITY_PROFILES XEP,
	FA_LOCATIONS FL,
	LEDGER_NAME LN,
	SEC_TBL ST
WHERE
	1=1
	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 PPTV.ENABLE_CAPITALIZATION_FLAG 		= 'Y'
	AND PPAV.TEMPLATE_FLAG 						= 'N'
	AND PCCV.CLASS_CODE							IN ('CER','MER','TER')
	--AND PPAV.SEGMENT1							= 'CER337124OP0005'
	AND PPAV.PROJECT_ID 						= PPAA.PROJECT_ID
	AND PPAA.ASSET_CATEGORY_ID 					= FCV.CATEGORY_ID
	AND PPAA.PROJECT_ASSET_ID					= PPAAS.PROJECT_ASSET_ID(+)
	AND PPAAS.PROJECT_ID 						= PPEV.PROJECT_ID(+)
	AND PPAAS.TASK_ID							= PPEV.PROJ_ELEMENT_ID(+)
	AND PPAA.ASSET_KEY_CCID 					= FAK.CODE_COMBINATION_ID
	AND PPAA.DEPRECIATION_EXPENSE_CCID 			= GCC.CODE_COMBINATION_ID
	AND PPAA.LOCATION_ID 						= FL.LOCATION_ID
	AND PPAV.LEGAL_ENTITY_ID 					= XEP.LEGAL_ENTITY_ID
	AND PPAV.ORG_ID								= LN.ORG_ID
	AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
	AND (LN.NAME            					IN (:P_LEDGER)
    OR  'ALL'                					IN (:P_LEDGER||'ALL'))
	AND (XEP.LEGAL_ENTITY_ID            		IN (:P_LEGAL_ENTITY)
    OR  'ALL'                					IN (:P_LEGAL_ENTITY||'ALL'))
	AND CAST(PPAV.ATTRIBUTE2 AS NUMERIC)		BETWEEN CAST(:P_SELL_METHOD_FROM AS NUMERIC) AND CAST(:P_SELL_METHOD_TO AS NUMERIC)
	AND CAST(PPAV.ATTRIBUTE1 AS NUMERIC)		BETWEEN CAST(:P_GL_LOCATION_FROM AS NUMERIC) AND CAST(:P_GL_LOCATION_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 (PPAV.PROJECT_STATUS_CODE		        IN (:P_STATUS)
    OR  'ALL'                					IN (:P_STATUS||'ALL'))
	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'))
	AND (PPAA.PROJECT_ASSET_TYPE            	IN (:P_ASSET_TYPE)
    OR  'ALL'                					IN (:P_ASSET_TYPE||'ALL'))