Oracle Time Card Query

SELECT *
FROM
  (SELECT DISTINCT NVL(NVL (ppf.employee_number, ppf.npw_number ),'') employee_number ,
    NVL(ppf.last_name,'') L_Name ,
    NVL(ppf.first_name,'') F_Name ,
    NVL(paaf.ass_attribute1,'') ADP_ID ,
    NVL(hxc_day.start_time,'') Start_Date ,
    NVL(hxc_detail.measure,'') hours ,
NVL(
    (SELECT hta.attribute3
    FROM hxc_time_attribute_usages htau ,
      hxc_time_attributes hta
    WHERE 1                              =1
    AND hta.time_attribute_id            = htau.time_attribute_id
    AND htau.time_building_block_id      = hxc_detail.time_building_block_id
    AND hta.attribute_category           ='PAEXPITDFF - Labor'
    AND hxc_detail.object_version_number = htau.time_building_block_ovn
    GROUP BY hta.attribute3
    ),'') COUNTRY_WHERE_PERFORMED ,
 nvl(
 
    (SELECT distinct t.description
  FROM Apps.Fnd_Flex_Values_Vl t,
       Apps.Fnd_Flex_Value_Sets s
WHERE s.Flex_Value_Set_Name = 'XXAA_COA_COMPANY'
   AND t.Flex_Value_Set_Id   = s.Flex_Value_Set_Id
   and t.enabled_flag = 'Y'
   and t.flex_value = haou_ou.ATTRIBUTE1
    ),'') PROJECT_COUNTRY,
(  select  distinct decode(FT.TERRITORY_CODE,'GB','UK','DE','GR',FT.TERRITORY_CODE) from  per_all_assignments_f paa, hr_locations_all hla, fnd_territories ft
where paa.person_id = ppf.person_id
and NVL(hxc_day.start_time,'') between paa.effective_start_Date and paa.effective_end_date
and paa.location_id = hla.location_id
and hla.country = ft.territory_code) country_code,
(  select distinct hla.country  from  per_all_assignments_f paa, hr_locations_all hla
where paa.person_id = ppf.person_id
and NVL(hxc_day.start_time,'') between paa.effective_start_Date and paa.effective_end_date
and paa.location_id = hla.location_id
) employee_home_country,

    CASE
      WHEN hxc_detail.measure<5 p="">      THEN '0'
      WHEN hxc_detail.measure>=5
      THEN '1'
      WHEN hxc_detail.measure IS NULL
      THEN ''
    END DAYS ,
    NVL(
    (SELECT hta.attribute4
    FROM hxc_time_attribute_usages htau ,
      hxc_time_attributes hta
    WHERE 1                              =1
    AND hta.time_attribute_id            = htau.time_attribute_id
    AND htau.time_building_block_id      = hxc_detail.time_building_block_id
    AND hta.attribute_category           ='PAEXPITDFF - Labor'
    AND hxc_detail.object_version_number = htau.time_building_block_ovn
    GROUP BY hta.attribute4
    ),'') State ,
    NVL(ppa.segment1,'') Project_number,
    hxc_timecard.*
  FROM
    (SELECT time_building_block_id,
      parent_building_block_id ,
      parent_building_block_ovn ,
      object_version_number ,
      measure ,
      resource_id ,
      approval_status ,
      comment_text ,
      translation_display_key ,
      date_from
    FROM hxc_time_building_blocks
    WHERE SCOPE          = 'DETAIL'
    AND approval_status IN ('SUBMITTED', 'APPROVED')
    ) hxc_detail ,
    (SELECT time_building_block_id,
      parent_building_block_id ,
      parent_building_block_ovn ,
      object_version_number ,
      start_time ,
      stop_time ,
      approval_status ,
      resource_id
    FROM hxc_time_building_blocks
    WHERE SCOPE          = 'DAY'
    AND approval_status IN ('SUBMITTED', 'APPROVED')
    ) hxc_day ,
    (SELECT time_building_block_id,
      start_time ,
      approval_status ,
      object_version_number ,
      comment_text ,
      resource_id
    FROM hxc_time_building_blocks
    WHERE SCOPE          = 'TIMECARD'
    AND approval_status IN ('SUBMITTED', 'APPROVED')
    ) hxc_timecard ,
    pa_projects_all ppa ,
    pa_tasks pt ,
    hxc_time_building_blocks htbb_details,
    hxc_time_building_blocks htbb_range ,
    hxc_time_attribute_usages htau ,
    hxc_time_attributes hta ,
    hxc_time_attributes hta1 ,
    per_people_f ppf ,
    per_all_assignments_f paaf ,
    hr_all_organization_units haou_ou ,
    hr_all_organization_units haou_org
  WHERE htbb_details.SCOPE              = 'DETAIL'
  AND htbb_details.approval_status     IN ('SUBMITTED', 'APPROVED')
  AND htbb_details.resource_id          = ppf.person_id
  AND htau.time_building_block_id       = htbb_details.time_building_block_id
  AND hta.time_attribute_id             = htau.time_attribute_id
  AND hta1.time_attribute_id            = htau.time_attribute_id
  AND hta.attribute_category            = 'PROJECTS'
  AND htbb_range.time_building_block_id = htbb_details.parent_building_block_id
  AND ppa.project_id                    = TO_NUMBER (hta.attribute1)
  AND pt.project_id                     = ppa.project_id
  AND pt.task_id                        = TO_NUMBER (hta.attribute2)
    --AND NVL (ppf.employee_number, ppf.npw_number) NOT LIKE 'Z%'
  AND paaf.person_id                    = ppf.person_id
  AND haou_org.organization_id          = paaf.organization_id
  AND haou_ou.organization_id           = ppa.org_id
  AND hxc_detail.time_building_block_id =htau.time_building_block_id
  AND hxc_detail.object_version_number  = htau.time_building_block_ovn
  AND TRUNC(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
  AND TRUNC(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
  AND hxc_day.parent_building_block_id     = hxc_timecard.time_building_block_id
  AND hxc_day.parent_building_block_ovn    = hxc_timecard.object_version_number
  AND hxc_detail.parent_building_block_id  = hxc_day.time_building_block_id
  AND hxc_detail.parent_building_block_ovn = hxc_day.object_version_number
  AND hxc_timecard.object_version_number   =
    (SELECT MAX (object_version_number)
    FROM hxc_time_building_blocks
    WHERE SCOPE                = 'TIMECARD'
    AND approval_status       IN ('SUBMITTED', 'APPROVED')
    AND time_building_block_id = hxc_timecard.time_building_block_id
    )
  AND hxc_detail.object_version_number =
    (SELECT MAX (object_version_number)
    FROM hxc_time_building_blocks
    WHERE SCOPE                = 'DETAIL'
    AND approval_status       IN ('SUBMITTED', 'APPROVED')
    AND time_building_block_id = hxc_detail.time_building_block_id
    )
  ) TC_out
WHERE 1           =1
--AND TC_out.State IS NOT NULL
AND TC_out.start_date BETWEEN to_date(substr(:PD_START_DATE,1,10), 'YYYY/MM/DD') AND to_date(substr(:PD_END_DATE,1,10), 'YYYY/MM/DD')

and TC_Out.country_code = nvl(ltrim(RTRIM(LTRIM((select short_code from hr_operating_units where name like :PS_EMP_HOME_CNTRY),'XXAA'),' OU'),' '),TC_Out.country_code)