Project Listing Details Report

<![CDATA[WITH LEDGER_TBL 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 project_number
, project_name
, project_description
, project_type_class_code
, project_type
, project_legal_entity
, project_location
, project_selling_method
, project_status
, project_manager
, project_start_date
, project_finish_date
, assortment
, toy_year_class_code
, item_complexity
, global_cer_class_code
, global_cer_dff

, proj_year
FROM (
SELECT PPAV.segment1 project_number
   , PPAV.name project_name
   , PPAV.description project_description
   
   , ( SELECT DISTINCT  PCCV.CLASS_CODE 
   FROM PJF_CLASS_CATEGORIES_VL PCCB,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES_V PPC,
	PJF_PROJECTS_ALL_VL PPAV1
	WHERE PPAV.PROJECT_ID = PPAV1.PROJECT_ID
	 AND PPAV1.PROJECT_ID = PPC.PROJECT_ID
	AND PPC.CLASS_CATEGORY_ID=PCCB.CLASS_CATEGORY_ID
	AND PCCB.CLASS_CATEGORY_ID=PCCV.CLASS_CATEGORY_ID
	AND PPC.CLASS_CODE_ID= PCCV.CLASS_CODE_ID
	AND UPPER(PCCB.CLASS_CATEGORY) IN ( 'PROJECT TYPE CLASS' )
   ) project_type_class_code
   
   , PPTV.project_type 
   , XEP.name project_legal_entity
   , PPAV.attribute1 project_location
   , PPAV.attribute2 project_selling_method
   , PPSV.project_status_name project_status
   , PPNFV.list_name project_manager
   , PPAV.start_date project_start_date
   , PPAV.completion_date project_finish_date
   , PPAV.ATTRIBUTE3 assortment
   
   , ( SELECT DISTINCT  PCCV.CLASS_CODE 
   FROM PJF_CLASS_CATEGORIES_VL PCCB,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES_V PPC,
	PJF_PROJECTS_ALL_VL PPAV1
	WHERE PPAV.PROJECT_ID = PPAV1.PROJECT_ID
	 AND PPAV1.PROJECT_ID = PPC.PROJECT_ID
	AND PPC.CLASS_CATEGORY_ID=PCCB.CLASS_CATEGORY_ID
	AND PCCB.CLASS_CATEGORY_ID=PCCV.CLASS_CATEGORY_ID
	AND PPC.CLASS_CODE_ID= PCCV.CLASS_CODE_ID
	AND UPPER(PCCB.CLASS_CATEGORY) IN ( 'TOY YEAR' )
   ) toy_year_class_code
   
   , ( SELECT DISTINCT  PCCV.CLASS_CODE 
   FROM PJF_CLASS_CATEGORIES_VL PCCB,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES_V PPC,
	PJF_PROJECTS_ALL_VL PPAV1
	WHERE PPAV.PROJECT_ID = PPAV1.PROJECT_ID
	 AND PPAV1.PROJECT_ID = PPC.PROJECT_ID
	AND PPC.CLASS_CATEGORY_ID=PCCB.CLASS_CATEGORY_ID
	AND PCCB.CLASS_CATEGORY_ID=PCCV.CLASS_CATEGORY_ID
	AND PPC.CLASS_CODE_ID= PCCV.CLASS_CODE_ID
	AND UPPER(PCCB.CLASS_CATEGORY) IN ( 'ITEM COMPLEXITY' )
   )  item_complexity
   
   , ( SELECT DISTINCT  PCCV.CLASS_CODE 
   FROM PJF_CLASS_CATEGORIES_VL PCCB,
	PJF_CLASS_CODES_VL PCCV,
	PJF_PROJECT_CLASSES_V PPC,
	PJF_PROJECTS_ALL_VL PPAV1
	WHERE PPAV.PROJECT_ID = PPAV1.PROJECT_ID
	 AND PPAV1.PROJECT_ID = PPC.PROJECT_ID
	AND PPC.CLASS_CATEGORY_ID=PCCB.CLASS_CATEGORY_ID
	AND PCCB.CLASS_CATEGORY_ID=PCCV.CLASS_CATEGORY_ID
	AND PPC.CLASS_CODE_ID= PCCV.CLASS_CODE_ID
	AND UPPER(PCCB.CLASS_CATEGORY) IN ( 'GLOBAL CER' )
   ) global_cer_class_code
   , PPAV.ATTRIBUTE4 global_cer_dff
   
   , SUBSTR(PPAV.SEGMENT1 ,8,2)  proj_year
   
FROM pjf_projects_all_vl PPAV
, pjf_project_types_vl PPTV
, pjf_project_statuses_vl PPSV
, pjf_latestprojectmanager_v PLV
, per_person_names_f_v PPNFV
, ledger_tbl LT
, xle_entity_profiles XEP
, SEC_TBL ST

WHERE 1 = 1
AND PPAV.project_type_id     = PPTV.project_type_id
AND PPAV.project_status_code = PPSV.project_status_code
AND PPAV.template_flag                  = 'N'
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.legal_entity_id 					= XEP.legal_entity_id

AND PPAV.org_id								= LT.org_id
AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
   
AND (LT.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 (PPAV.SEGMENT1		            		IN (:P_PROJ_NUM)
OR  'All'                					IN (:P_PROJ_NUM||'All'))

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

	
AND (PPNFV.list_name	                    IN (:P_PROJ_MANAGER)
OR  'All'                                   IN (:P_PROJ_MANAGER||'All'))

AND (SUBSTR(PPAV.SEGMENT1 ,8,2)             >= (:P_YEAR_FROM)
OR  'All'                					IN (:P_YEAR_FROM||'All'))
AND (SUBSTR(PPAV.SEGMENT1 ,8,2)             <= (:P_YEAR_TO)
OR  'All'                					IN (:P_YEAR_TO||'All'))

AND (PPAV.attribute2		                >= (:P_SELLING_FROM)
OR  'All'                			        IN (:P_SELLING_FROM||'All'))
AND (PPAV.attribute2            		    <= (:P_SELLING_TO)
OR  'All'                			        IN (:P_SELLING_TO||'All'))

AND (PPAV.attribute1		                >= (:P_GL_LOC_FROM)
OR  'All'                			        IN (:P_GL_LOC_FROM||'All'))
AND (PPAV.attribute1            		    <= (:P_GL_LOC_TO)
OR  'All'                			        IN (:P_GL_LOC_TO||'All'))
)
WHERE 1 = 1

AND (project_type_class_code                IN (:P_PROJ_TYPE_CLASS)
OR  'All'                					IN (:P_PROJ_TYPE_CLASS||'All'))

AND (toy_year_class_code	            IN (:P_TOY_YEAR)
OR  'All'                               IN (:P_TOY_YEAR||'All'))

ORDER BY project_number]]>