ECC Field Service, Field Service Tasks (1)

select
x.*
from
(
select * from (select tmp.ECC_SPEC_ID,dfv.*, tmp.TASK_ID,tmp.TASK_NUMBER,tmp.TASK_NAME,tmp.DESCRIPTION,tmp.LAST_UPDATE_DATE
,tmp.TASK_TYPE,tmp.TASK_STATUS,tmp.TASK_PRIORITY,tmp.PLANNED_START_DATE,tmp.PLANNED_END_DATE,tmp.SCHEDULED_START_DATE,tmp.SCHEDULED_END_DATE
,tmp.TASK_CONFIRMATION_STATUS,tmp.TASK_CONFIRMATION_COUNTER,tmp.SOURCE_OBJECT_TYPE_CODE,tmp.SOURCE_OBJECT_NAME,tmp.TASK_CREATION_DATE
,tmp.TASK_TYPE_RULE,tmp.DAY_OF_WEEK,tmp.WEEK,tmp.CUSTOMER_NAME,tmp.CITY,tmp.POSTAL_CODE
,tmp.STATE,tmp.COUNTRY,tmp.ADDRESS,tmp.ITEM,tmp.ITEM_DESCRIPTION,tmp.CATEGORY,tmp.INCIDENT_ID ,tmp.SERVICE_REQUEST_NUMBER,tmp.SUMMARY,tmp.INCIDENT_DATE,tmp.INCIDENT_OCCURRED_DATE
,tmp.RESOLUTION_SUMMARY,tmp.SERVICE_REQUEST_STATUS,tmp.REQUEST_TYPE,tmp.SEVERITY,tmp.RESPOND_BY,tmp.RESOLVE_BY,tmp.RESOLVED_ON,tmp.RESPONDED_ON,tmp.ACTUAL_RESOLUTION_DATE
,tmp.SR_DATE_CLOSED,tmp.AFTER_HOURS_FLAG,tmp.AFTER_HOURS,tmp.ACCESS_HOURS_FLAG,tmp.ACCESS_HOURS,tmp.CLOSED_FLAG,tmp.CLOSED,tmp.OWNER_TYPE,tmp.OWNER,tmp.ACTUAL_START_DATE
,tmp.ACTUAL_END_DATE,tmp.START_ON_TIME_CODE,tmp.START_ON_TIME,tmp.FINISH_ON_TIME_CODE,tmp.FINISH_ON_TIME,tmp.START_DIFFERENCE,tmp.FINISH_DIFFERENCE
,tmp.RESOLUTION_TYPE,tmp.PROBLEM_TYPE,tmp.SERIAL_NUMBER,tmp.INSTANCE_NUMBER,tmp.TAG_NUMBER,tmp.SERVICE_REQUEST_OWNER,tmp.SR_ASSIGNED_GROUP
,tmp.CUSTOMER_CONFIRMATION_FLAG,tmp.CUSTOMER_CONFIRMATION,tmp.GEOMETRY,tmp.PLANNED_EFFORT_W_UOM,tmp.ACTUAL_EFFORT_W_UOM,tmp.EFFORT,tmp.EFFORT_DIFFERENCE,tmp.TASK_ASSIGNMENT_ID
,tmp.RESOURCE_NAMES,tmp.SCHED_TRAVEL_DISTANCE,tmp.SCHED_TRAVEL_DURATION,tmp.SCHED_TRAVEL_DURATION_UOM,tmp.ACTUAL_TRAVEL_DISTANCE,tmp.ACTUAL_TRAVEL_DURATION
,tmp.ACTUAL_TRAVEL_DURATION_UOM,tmp.MORE_TRAVEL_DISTANCE,tmp.MORE_TRAVEL_TIME,tmp.FIRST_TIME_FIX_CODE,tmp.FIRST_TIME_FIX,tmp.RESCHEDULE_COUNT
,tmp.ASSIGNMENT_CREATION_DATE,tmp.SCHEDULE_TIME,tmp.REQUIRED_PARTS,tmp.REQUIRED_SKILLS,tmp.LANGUAGE,tmp.COMPLETED_FLAG,tmp.WORKING_FLAG
,tmp.PLANNED_EFFORT,tmp.PLANNED_EFFORT_UOM,tmp.ACTUAL_EFFORT,tmp.ACTUAL_EFFORT_UOM,tmp.T_SCHD_TRVL_DUR_W_UOM,tmp.T_ACTL_TRVL_DUR_W_UOM ,tmp.INCIDENT_TYPE_ID
,tmp.INCIDENT_OWNER_ID,tmp.OWNER_GROUP_ID,tmp.STARTED_ON_TIME_Y,tmp.STARTED_ON_TIME_YN,tmp.FINISHED_ON_TIME_Y,tmp.FINISHED_ON_TIME_YN
,tmp.FIRST_TIME_FIX_Y,tmp.FIRST_TIME_FIX_YN,tmp.RSTC_PRESENT,tmp.RESOURCE_IDS,tmp.PARTS_PRESENT_FLAG,tmp.SKILLS_REQ_FLAG
,tmp.TASK_AT_RISK_FLAG ,tmp.OVERDUE_TASK_FLAG,tmp.UNSCHEDULED_TASK_FLAG,tmp.UNASSIGNED_TASK_FLAG
,CSF_ECC_TK_UTIL_PVT.get_lookup_meaning_mls (tmp.working_flag,'YES_NO',0,language) work_in_progress
,CSF_ECC_TK_UTIL_PVT.get_lookup_meaning_mls (tmp.unscheduled_task_flag,'YES_NO',0,language) unscheduled_task
,CSF_ECC_TK_UTIL_PVT.get_lookup_meaning_mls (tmp.unassigned_task_flag,'YES_NO',0,language) unassigned_task
,CSF_ECC_TK_UTIL_PVT.get_lookup_meaning_mls (tmp.task_at_risk_flag,'YES_NO',0,language) task_at_risk
,CSF_ECC_TK_UTIL_PVT.get_lookup_meaning_mls (tmp.overdue_task_flag,'YES_NO',0,language) overdue_task
,CSF_ECC_TK_UTIL_PVT.get_lookup_meaning_mls (tmp.parts_present_flag,'YES_NO',0,language) parts_present
,CSF_ECC_TK_UTIL_PVT.get_lookup_meaning_mls (tmp.skills_req_flag,'YES_NO',0,language) skills_req
FROM CSF_ECC_TASK_TEMP TMP , JTF_TASKS_B JTB  , (select "ROW_ID" "'JTB_ROW_ID'","CONTEXT_VALUE" "'JTB_CONTEXT_VALUE'","TASK_COMPLETION" "'JTB_TASK_COMPLETION'","CONCATENATED_SEGMENTS" "'JTB_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT_VALUE",ATTRIBUTE15 "TASK_COMPLETION",ATTRIBUTE15 "CONCATENATED_SEGMENTS" from JTF_TASKS_B )) dfv  WHERE JTB.rowid = dfv."'JTB_ROW_ID'"(+) AND TMP.task_id=JTB.task_id    AND  LANGUAGE in ('US') )
PIVOT(
   MAX(TASK_NAME) AS TASK_NAME,
   MAX(DESCRIPTION) AS DESCRIPTION,
   MAX(TASK_TYPE) AS TASK_TYPE,
   MAX(TASK_STATUS) AS TASK_STATUS,
   MAX(TASK_PRIORITY) AS TASK_PRIORITY,
   MAX(DAY_OF_WEEK) AS DAY_OF_WEEK,
   MAX(CITY) AS CITY,
   MAX(ITEM_DESCRIPTION) AS ITEM_DESCRIPTION,
   MAX(SUMMARY) AS SUMMARY,
   MAX(RESOLUTION_SUMMARY) AS RESOLUTION_SUMMARY,
   MAX(SERVICE_REQUEST_STATUS) AS SERVICE_REQUEST_STATUS,
   MAX(REQUEST_TYPE) AS REQUEST_TYPE,
   MAX(SEVERITY) AS SEVERITY,
   MAX(AFTER_HOURS) AS AFTER_HOURS,
   MAX(ACCESS_HOURS) AS ACCESS_HOURS,
   MAX(CLOSED) AS CLOSED,
   MAX(OWNER) AS OWNER,
   MAX(START_ON_TIME) AS START_ON_TIME,
   MAX(FINISH_ON_TIME) AS FINISH_ON_TIME,
   MAX(RESOLUTION_TYPE) AS RESOLUTION_TYPE,
   MAX(PROBLEM_TYPE)AS PROBLEM_TYPE,
   MAX(SERVICE_REQUEST_OWNER) AS SERVICE_REQUEST_OWNER,
   MAX(SR_ASSIGNED_GROUP) AS SR_ASSIGNED_GROUP,
   MAX(CUSTOMER_CONFIRMATION) AS  CUSTOMER_CONFIRMATION,
   MAX(PLANNED_EFFORT_W_UOM) AS PLANNED_EFFORT_W_UOM,
   MAX(ACTUAL_EFFORT_W_UOM) AS ACTUAL_EFFORT_W_UOM,
   MAX(RESOURCE_NAMES) AS RESOURCE_NAMES,
   MAX(FIRST_TIME_FIX) AS FIRST_TIME_FIX,
   MAX(T_SCHD_TRVL_DUR_W_UOM) AS T_SCHD_TRVL_DUR_W_UOM,
   MAX(T_ACTL_TRVL_DUR_W_UOM) AS T_ACTL_TRVL_DUR_W_UOM,
   max(work_in_progress) as work_in_progress,
   max(unscheduled_task) as unscheduled_task,
   max(unassigned_task) as unassigned_task,
   max(task_at_risk) as task_at_risk,
   max(overdue_task) as overdue_task,
   max(parts_present) as parts_present,
   max(skills_req) as skills_req
for LANGUAGE in ('US' "US"))
) x
where
2=2