ECC Project Procurement, Task Summary

select
x.*
from
(
SELECT * FROM (
				 SELECT 'PROC_PROJ_TASK_LIST' RECORD_TYPE,
				 'PROC_PROJ_TASK_LIST-' || TO_CHAR(pts.PROJECT_ID)
				 ||decode(pts.task_id, null,null,'-')
				 ||TO_CHAR(pts.task_id)  AS ECC_SPEC_ID ,
				  pts.PROJECT_NUMBER ,
				  pts.PROJECT_ID,
				  pts.PROJECT_NAME,
				  pts.ORG_ID,
				  pts.project_id PROJ_ID,
				  proj_ou.name PROJECT_ORG,
				  task_ou.name TASK_ORG,
				  pts.PROJECT_STATUS,
				  pts.TASK_ID,
				  pts.TASK_NAME,
				  pts.TASK_NUMBER,
				  pts.STRUCTURE_VERSION_NUMBER,
				  pts.ELEMENT_VERSION_ID,
				  pts.TASK_ELEMENT_ID,
				  pts.PA_SRC,
				  pts.TASK_MANAGER,
				  pts.TASK_LOCATION,
				  pts.TASK_STATUS,
				  pts.TASK_TYPE,
				  wt.name WORK_TYPE,
				  PA_PPCC_ECC_PVT.GET_LOOKUP_MEANING('PA_TASK_LVL_ETC_SRC',PTS.ETC_SOURCE, LANG.LANGUAGE_CODE)		 AS ETC_SOURCE,
				  PA_PPCC_ECC_PVT.pa_get_yes_no(PTS.RECIEVE_PROJECT_INVOICE_FLAG, LANG.LANGUAGE_CODE) AS RCV_PROJECT_INVOICE_FLAG,
				  PA_PPCC_ECC_PVT.pa_get_yes_no(PTS.CAPITALIZABLE_FLAG, LANG.LANGUAGE_CODE)  AS CAPITALIZABLE_FLAG,
				  PA_PPCC_ECC_PVT.pa_get_yes_no(PTS.BILLABLE_FLAG, LANG.LANGUAGE_CODE)  AS BILLABLE_FLAG,
				  PA_PPCC_ECC_PVT.pa_get_yes_no(PTS.CHARGEABLE_FLAG, LANG.LANGUAGE_CODE)              AS CHARGEABLE_FLAG,
				  PA_PPCC_ECC_PVT.pa_get_yes_no(PTS.LAT_PUB_MILESTONE_FLAG, LANG.LANGUAGE_CODE)			AS LAT_PUB_MILESTONE_FLAG,
				  PA_PPCC_ECC_PVT.pa_get_yes_no(PTS.LAT_PUB_CRITICAL_FLAG, LANG.LANGUAGE_CODE)			AS LAT_PUB_CRITICAL_FLAG,
				 PA_PPCC_ECC_PVT.pa_get_yes_no(PTS.WORKING_MILESTONE_FLAG, LANG.LANGUAGE_CODE)			AS WORKING_MILESTONE_FLAG,
				 PA_PPCC_ECC_PVT.pa_get_yes_no(PTS.WORKING_CRITICAL_FLAG, LANG.LANGUAGE_CODE)			AS WORKING_CRITICAL_FLAG,
				 PTS.TASK_PROGR_STATUS,
				  PA_PPCC_ECC_PVT.GET_LOOKUP_MEANING('SERVICE TYPE',PTS.SERVICE_TYPE, LANG.LANGUAGE_CODE)                    AS SERVICE_TYPE,
				  PTS.TASK_PRIORITY,
				  PTS.LAT_PUB_SCHEDULED_START_DATE,
				  PTS.LAT_PUB_ACTUAL_START_DATE,
				  PTS.LAT_PUB_SCHEDULED_FINISH_DATE ,
				  PTS.LAT_PUB_ACTUAL_FINISH_DATE,
				  PTS.WORKING_SCHEDULED_START_DATE,
				  PTS.WORKING_SCHEDULED_FINISH_DATE,
				  PTS.PROJECT_CURRENCY ,
				  PTS.PROJ_CURRENCY_NAME,
				  PTS.TOT_PLAN_COST_PC ,
				  PTS.TOT_PROC_PLAN_COST_PC ,
				  PTS.TOT_PLAN_REV_PC ,
				  PTS.TOT_MARGIN_PC ,
				  PTS.MARGIN_PERCENT_PC ,
				  PTS.PROC_PERC_REV_PC ,
				  PTS.TOT_ACT_COST_PC ,
				  PTS.TOT_ACT_PROC_COST_PC ,
				  PTS.TOT_FC_COST_PC ,
				  PTS.TOT_FC_REV_PC ,
				  PTS.FC_MARGIN_PERCENT_PC ,
				  PTS.PROJECT_FUNC_CURRENCY,
				  PTS.PROJFUNC_CURRENCY_NAME,
				  PTS.TOT_PLAN_COST_PFC ,
				  PTS.TOT_PROC_PLAN_COST_PFC ,
				  PTS.TOT_PLAN_REV_PFC ,
				  PTS.TOT_MARGIN_PFC ,
				  PTS.MARGIN_PERCENT_PFC ,
				  PTS.PROC_PERC_REV_PFC ,
				  PTS.TOT_ACT_COST_PFC ,
				  PTS.TOT_ACT_PROC_COST_PFC ,
				  PTS.TOT_FC_COST_PFC ,
				  PTS.TOT_FC_REV_PFC ,
				  PTS.FC_MARGIN_PERCENT_PFC ,
				  PTS.PER_PROC_ACT_COST_DEL_OT ,
				  PTS.PER_PROC_ACT_COST_DELAYED ,
				  PTS.TOT_PAYMENTS_PC ,
				  PTS.TOT_PAYMENTS_PFC ,
				  PTS.TOT_PAY_HOLD_PC ,
				  PTS.TOT_PAY_HOLD_PFC,
				  PTS.PROC_PER_TOT_COST ,
				  PTS.PER_TOT_COST_STK ,
				  PTS.PER_TOT_COST_RCV ,
				  PTS.PER_TOT_COST_RET ,
				  PTS.PER_TOT_COST_TST ,
				  PTS.PER_TOT_COST_ORD ,
				  PTS.PER_TOT_COST_CNT ,
				  PTS.PER_TOT_COST_REQ ,
				  PTS.PER_TOT_COST_REM ,
				  PTS.TOT_PROC_COST_PCT_DISP,
				  PTS.OTHER_COST_PCT_DISP,
				  PTS.PER_TOT_COST_ORD_DISP,
				  PTS.PER_TOT_COST_RCV_DISP,
				  PTS.PER_TOT_COST_REM_DISP,
				  PTS.PER_TOT_COST_REQ_DISP,
				  PTS.PER_TOT_COST_RET_DISP,
				  PTS.TOT_MARGIN_PCT_DISP,
				  PTS.TOT_PLAN_BURD_COST_PC,
				  PTS.TOT_PLAN_BURD_COST_PFC,
				  PTS.TOT_PROJ_COMMITMENTS_PC ,
				  PTS.TOT_ACT_BURD_COST_PC,
				  PTS.TOT_ACT_MARGIN_PC,
				  PTS.TOT_ACT_BURD_COST_VAR,
				  PTS.TOT_ACT_PROC_PLAN_COST_VAR,
				  PTS.TOT_ACT_MARGIN_VAR,
				  PA_PPCC_ECC_PVT.pa_get_yes_no(PTS.CURRENCY_CONV_RATE_FLAG, LANG.LANGUAGE_CODE) CURRENCY_CONV_RATE_FLAG,
				  PTS.TASK_DISPLAY_SEQUENCE  ,
				  LANG.LANGUAGE_CODE LANGUAGE
				FROM PA_ENDECA_TASK_SUMM_TEMP pts ,
				     PO_PROC_PLAN_HEADER PPPH,
				     	hr_all_organization_units_tl proj_ou,
				     	hr_all_organization_units_tl task_ou,
				      pa_tasks pat,
				     pa_work_types_tl wt,
				     fnd_languages lang
				WHERE PTS.PROJECT_ID = PPPH.PROJECT_ID
				  AND pts.task_id = pat.task_id
				  AND pts.ORG_ID = proj_ou.ORGANIZATION_ID
				  AND proj_ou.ORGANIZATION_ID (+) = pts.ORG_ID
				  AND nvl(proj_ou.LANGUAGE, lang.language_code)  = lang.language_code
				  AND lang.installed_flag in ( 'I', 'B')
				  AND task_ou.ORGANIZATION_ID (+) = pts.ORG_ID
				  AND nvl(task_ou.LANGUAGE, lang.language_code)  = lang.language_code
				  AND pat.work_type_id = wt.work_type_id (+)
				  AND nvl(wt.LANGUAGE, lang.language_code)  = lang.language_code
				  and  LANG.LANGUAGE_CODE in ('US')
				   ) PIVOT (
						MAX(PROJECT_ORG) AS PROJECT_ORG ,
						MAX(TASK_ORG) AS TASK_ORG ,
						MAX(WORK_TYPE) AS WORK_TYPE ,
						MAX(ETC_SOURCE) AS ETC_SOURCE ,
						MAX(RCV_PROJECT_INVOICE_FLAG) AS RCV_PROJECT_INVOICE_FLAG ,
						MAX(CAPITALIZABLE_FLAG) AS CAPITALIZABLE_FLAG ,
						MAX(BILLABLE_FLAG) AS BILLABLE_FLAG ,
						MAX(CHARGEABLE_FLAG) AS CHARGEABLE_FLAG ,
						MAX(LAT_PUB_MILESTONE_FLAG) AS LAT_PUB_MILESTONE_FLAG ,
						MAX(LAT_PUB_CRITICAL_FLAG) AS LAT_PUB_CRITICAL_FLAG ,
						MAX(WORKING_MILESTONE_FLAG) AS WORKING_MILESTONE_FLAG ,
						MAX(WORKING_CRITICAL_FLAG) AS WORKING_CRITICAL_FLAG ,
						MAX(SERVICE_TYPE) AS SERVICE_TYPE ,
						MAX(CURRENCY_CONV_RATE_FLAG) AS CURRENCY_CONV_RATE_FLAG
				   for LANGUAGE in ('US' "US")
        )
) x
where
2=2