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

      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)