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)

Following query will list out all the Approvers for a given time period

Parameters: P_START_DATE  -- Timecard building block start date
                  P_END_DATE      --  Timecard building block end date

SELECT   ppxr.person_id resource_id,
         ppxr.employee_number resource_emp_num,
         ppxr.full_name resource_emp_name,
         tbbda.start_time, tbbda.stop_time,
         pp.project_id, pp.segment1 project_number,
         pp.name project_name, pt.task_id,
         pt.task_name, pt.task_number,
         pt.attribute7 billable_flag,
         tbb.approval_style_id,
         tbs.timecard_id timecard_id,
         haps.application_period_id,
         haps.application_period_ovn,
         ppx.person_id approver_id,
         ppx.employee_number approver_empnum,
         ppx.full_name approver_name,
         tbbde.time_building_block_id detail_building_block_id
    FROM hxc_app_period_summary haps,
         hxc_timecard_summary tbs,
         hxc.hxc_tc_ap_links htal,
         per_people_x ppx,
         per_people_x ppxr,
         hxc_time_building_blocks tbb,
         hxc_time_attribute_usages taup,
         hxc_time_attributes tap,
         hxc_time_building_blocks tbbda,
         hxc_time_building_blocks tbbde,
         pa_projects_all pp,
         pa_tasks pt
   WHERE 1 = 1
     AND htal.timecard_id = tbs.timecard_id
     AND htal.application_period_id = haps.application_period_id
     AND haps.application_period_ovn = tbs.timecard_ovn
     AND haps.resource_id = tbs.resource_id
     AND TRUNC (haps.start_time) = TRUNC (tbs.start_time)
     AND TRUNC (haps.stop_time) = TRUNC (tbs.stop_time)
     AND haps.application_period_ovn =
                   (SELECT MAX (application_period_ovn)
                      FROM hxc_app_period_summary
                     WHERE application_period_id = haps.application_period_id)
     AND haps.approver_id = ppx.person_id
     AND haps.start_time BETWEEN ppx.effective_start_date
                             AND ppx.effective_end_date
     AND TRUNC (haps.start_time) = TRUNC (tbb.start_time)
     AND TRUNC (haps.stop_time) = TRUNC (tbb.stop_time)
     AND tbb.object_version_number =
            (SELECT MAX (object_version_number)
               FROM hxc_time_building_blocks
              WHERE tbb.time_building_block_id = time_building_block_id
                AND tbb.approval_style_id = approval_style_id)
     AND tbb.resource_id = haps.resource_id
     AND tbb.SCOPE = 'TIMECARD'
     AND tbb.approval_style_id = 1021
     AND tbbda.SCOPE = 'DAY'
     AND tbbde.SCOPE = 'DETAIL'
     AND tbb.time_building_block_id = tbs.timecard_id
     AND tbb.object_version_number = tbs.timecard_ovn
     AND tbbda.parent_building_block_id = tbb.time_building_block_id
     AND tbbda.parent_building_block_ovn = tbbda.object_version_number
     AND tbbde.parent_building_block_id = tbbda.time_building_block_id
     AND tbbde.parent_building_block_ovn = tbb.object_version_number
     AND tbbde.time_building_block_id = taup.time_building_block_id
     AND tbbde.object_version_number = taup.time_building_block_ovn
     AND tap.attribute1 IS NOT NULL
     AND tap.bld_blk_info_type_id = 13
     AND tap.time_attribute_id = taup.time_attribute_id
     AND tap.attribute_category = 'PROJECTS'
     AND TO_NUMBER (tap.attribute1) = pp.project_id
     AND pt.project_id = pp.project_id
     AND TO_NUMBER (tap.attribute2) = pt.task_id
     AND tbbde.resource_id = tbb.resource_id
     AND tbbde.resource_id = tbbda.resource_id
     AND ppxr.person_id = tbs.resource_id
     AND TRUNC (tbs.start_time) BETWEEN TO_DATE (:p_start_date, 'DD-MON-RRRR')
                                    AND TO_DATE (:p_end_date, 'DD-MON-RRRR')
     AND tbbda.object_version_number =
            (SELECT MAX (object_version_number)
               FROM hxc_time_building_blocks
              WHERE tbbda.time_building_block_id = time_building_block_id
                AND tbbda.approval_style_id = approval_style_id)
     AND tbbde.object_version_number =
            (SELECT MAX (object_version_number)
               FROM hxc_time_building_blocks
              WHERE tbbde.time_building_block_id = time_building_block_id
                AND tbbde.approval_style_id = approval_style_id)
ORDER BY 1, tbs.timecard_id, 4;

OTL: Oracle Time and Labor - Timecard/Timesheet related queries -Part 3

Alert to Users who have not filled time sheet:
(Data was taken from 1st march, 2010 after go-live)

SELECT
                        (case when trunc(add_months(last_day(sysdate),-3)+1) >= '01-MAR-2010'
            then add_months(last_day(sysdate),-3)+1
            else to_date('01-MAR-2010','dd-mon-rrrr')
            end) from_date,
                        sysdate,
                        papf.full_name employee_name,
                        nvl(papf.employee_number,papf.npw_number) employee_number,
                        papf.email_address,
                        exp.expenditure_ending_date-6 week_start_date,
                        exp.expenditure_ending_date week_ending_date,
son_time_sheet_approval_status(exp.expenditure_ending_date-6,papf.person_id)
INTO    &from_date,
                        &to_date,
                        &emp_name,
                        &emp_no,
                        &email_address
                        &week_start_date,
                        &week_end_date,
                        &timesheet_status
FROM
 per_person_types ppt,
 per_person_type_usages_f puf,
 per_people_f papf,
  (select distinct
   expenditure_ending_date
   from pa_expenditures_all
   where expenditure_ending_date-6 between add_months(last_day(sysdate),-3)+1 and sysdate-7
and to_char(expenditure_ending_date,'DAY-MON-YYYY') like '%SUNDAY%'
and expenditure_ending_date-6 >= '01-MAR-2010') exp
WHERE papf.person_id=puf.person_id
  and puf.person_type_id=ppt.person_type_id
  and ppt.system_person_type in ('EMP','CWK')
  and sysdate between papf.effective_start_date and papf.effective_end_date
  and sysdate between puf.effective_start_date and puf.effective_end_date
  and (papf.employee_number is not null or papf.npw_number is not null)
 and son_time_sheet_approval_status(exp.expenditure_ending_date-6,papf.person_id) not in ('APPROVED','SUBMITTED')
and papf.original_date_of_hire<=exp.expenditure_ending_date
order by exp.expenditure_ending_date

Alert to Project Managers with the details of employees who have not filled their timesheets:

WITH q1 AS
     (
        SELECT   (CASE
                     WHEN TRUNC (ADD_MONTHS (LAST_DAY (SYSDATE), -3) + 1) >=
                                                                 '01-MAR-2010'
                        THEN ADD_MONTHS (LAST_DAY (SYSDATE), -3) + 1
                     ELSE TO_DATE ('01-MAR-2010', 'dd-mon-rrrr')
                  END
                 ) from_date,
                 SYSDATE TO_DATE, papf.person_id,
                 papf.full_name employee_name,
                 NVL (papf.employee_number, papf.npw_number) employee_number,
                 papf.email_address,
                 EXP.expenditure_ending_date - 6 week_start_date,
                 EXP.expenditure_ending_date week_ending_date,
                 son_time_sheet_approval_status (EXP.expenditure_ending_date,
                                                 papf.person_id
                                                )
            FROM per_person_types ppt,
                 per_person_type_usages_f puf,
                 fnd_user fu,               -- added to exclude group mail ids
                 per_people_f papf,
                 (SELECT DISTINCT expenditure_ending_date
                             FROM pa_expenditures_all
                            WHERE expenditure_ending_date - 6
                                     BETWEEN   ADD_MONTHS (LAST_DAY (SYSDATE),
                                                           -3
                                                          )
                                             + 1
                                         AND SYSDATE - 7
                              AND TO_CHAR (expenditure_ending_date,
                                           'DAY-MON-YYYY'
                                          ) LIKE '%SUNDAY%'
                              AND expenditure_ending_date - 6 >= '01-MAR-2010') EXP
           WHERE papf.person_id = puf.person_id
             AND puf.person_type_id = ppt.person_type_id
             AND ppt.system_person_type IN ('EMP', 'CWK')
             AND SYSDATE BETWEEN papf.effective_start_date
                             AND papf.effective_end_date
             AND SYSDATE BETWEEN puf.effective_start_date
                             AND puf.effective_end_date
             AND (   papf.employee_number IS NOT NULL
                  OR papf.npw_number IS NOT NULL
                 )
             AND son_time_sheet_approval_status (EXP.expenditure_ending_date,
                                                 papf.person_id
                                                ) NOT IN
                                                    ('APPROVED', 'SUBMITTED')
             AND papf.original_date_of_hire <= EXP.expenditure_ending_date
             AND papf.person_id = fu.employee_id
             AND fu.user_id = fu1.user_id)
--and papf.employee_number='7283'
        ORDER BY EXP.expenditure_ending_date),
     q3 AS
     (
        SELECT ppa.project_id, ppal.segment1, ppa.assignment_effort,
               ppa.start_date, ppa.end_date, papf.person_id,
               pa_otc_api.getprojectmanager (ppa.project_id) pm_person_id
          FROM pa_project_assignments ppa,
               pa_resource_txn_attributes prta,
               pa_projects_all ppal,
               per_all_people_f papf
         WHERE ppa.resource_id = prta.resource_id
           AND prta.person_id = papf.person_id
           AND ppa.project_id = ppal.project_id
           AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
                                   AND TRUNC (papf.effective_end_date)
           AND assignment_type = 'STAFFED_ASSIGNMENT'
           AND apprvl_status_code = 'ASGMT_APPRVL_APPROVED')
SELECT q1.person_id, q1.from_date, q1.TO_DATE, q1.employee_name,
       q1.employee_number, q1.email_address, q3.start_date, q3.end_date,
       q1.week_start_date, q1.week_ending_date, q3.project_id,
       q3.segment1 project, q3.pm_person_id,
       (SELECT full_name
          FROM per_all_people_f
         WHERE person_id = q3.pm_person_id
           AND TRUNC (SYSDATE) BETWEEN TRUNC (effective_start_date)
                                   AND TRUNC (effective_end_date))
                                                              project_manager,
       (SELECT email_address
          FROM per_all_people_f
         WHERE person_id = q3.pm_person_id
           AND TRUNC (SYSDATE) BETWEEN TRUNC (effective_start_date)
                                   AND TRUNC (effective_end_date))
                                                             pm_email_address
  FROM q1, q3
 WHERE q3.person_id = q1.person_id
   AND (   (q1.week_ending_date BETWEEN q3.start_date AND q3.end_date)
        OR (q1.week_ending_date - 6 BETWEEN q3.start_date AND q3.end_date)
        OR (q1.week_ending_date - 5 BETWEEN q3.start_date AND q3.end_date)
        OR (q1.week_ending_date - 4 BETWEEN q3.start_date AND q3.end_date)
        OR (q1.week_ending_date - 3 BETWEEN q3.start_date AND q3.end_date)
        OR (q1.week_ending_date - 2 BETWEEN q3.start_date AND q3.end_date)
        OR (q1.week_ending_date - 1 BETWEEN q3.start_date AND q3.end_date)
       )



Alert to Approvers who have not taken any action on time sheet notification:


select papf.full_name,
               papf.email_address,
               wn.subject
  into &approver_name,
       &approver_email,
               &approver_subject
  from wf_item_activity_statuses wias,
       wf_process_activities wpa,
       wf_notifications wn,
               fnd_user fndu,
               per_all_people_f papf
where wias.item_type = 'HXCEMP'
 and wias.item_key = wn.item_key
 and wn.recipient_role=fndu.user_name
 and fndu.employee_id=papf.person_id
 and sysdate between papf.effective_start_date and papf.effective_end_date
 and wias.process_activity = wpa.instance_id
 and wpa.activity_name = 'TC_APR_NOTIFICATION'
 and wn.status='OPEN'
 and wias.notification_id = wn.notification_id




 Query to get basic timesheet/timecard details:

SELECT   hts.timecard_id, hts.resource_id, hts.start_time,
                  hts.stop_time, hts.submission_date,
                  htb1.start_time each_day, hta.attribute1 project_id,
                  hta.attribute2 task_id, htb2.measure, pt.task_name
             FROM hxc_time_building_blocks htb,
                  hxc_time_building_blocks htb1,
                  hxc_time_building_blocks htb2,
                  hxc_time_attribute_usages htau,
                  hxc_time_attributes hta,
                  pa_projects_all papa,
                  hxc_timecard_summary hts,
                  pa_tasks pt
            WHERE htb1.parent_building_block_id = htb.time_building_block_id
              AND htb1.parent_building_block_ovn = htb.object_version_number
              AND htb.date_to = hr_general.end_of_time
              AND htb.SCOPE = 'TIMECARD'
              AND htb1.SCOPE = 'DAY'
              AND htb1.date_to = hr_general.end_of_time
              AND htb2.parent_building_block_id = htb1.time_building_block_id
              AND htb2.parent_building_block_ovn = htb1.object_version_number
              AND htb2.SCOPE = 'DETAIL'
              AND htb2.date_to = hr_general.end_of_time
              AND htau.time_building_block_id = htb2.time_building_block_id
              AND htau.time_building_block_ovn = htb2.object_version_number
              AND htau.time_attribute_id = hta.time_attribute_id
              AND papa.project_id = hta.attribute1
              AND hts.start_time = htb.start_time
              AND hts.resource_id = htb.resource_id
           --AND htb.resource_id = p_resource_id
              AND hts.timecard_id = :p_timecard_id
              AND hta.attribute_category = 'PROJECTS'
              AND hts.approval_status = 'WORKING'
              AND hta.attribute2 = pt.task_id
              AND hta.attribute1 = pt.project_id
         ORDER BY htb1.start_time;

OTL queries- Part2

Query to check if for a person a Time card has been submitted or not for the start and stop time


SELECT time_building_block_id, object_version_number, start_time,
comment_text, created_by, creation_date, last_updated_by,
last_update_date, last_update_login
FROM hxc_time_building_blocks
WHERE resource_id =
AND SCOPE = 'TIMECARD'
AND start_time =
AND stop_time =
AND date_to = hr_general.end_of_time;


Query to get the Complete Timecard Data:


SELECT *
FROM HXC_TIME_BUILDING_BLOCKS
WHERE date_to=hr_general.end_of_time
CONNECT BY prior time_building_block_id=parent_building_block_id
AND object_version_number=parent_building_block_ovn
START WITH time_building_block_id=< Timecard Id>
ORDER BY 1




Query to get all Attributes Information for a complete Timecard


SELECT htb.resource_id Person_id,
htb.start_time Start_time, htb.stop_time Stop_time,
htb2.time_building_block_id, hta.time_attribute_id,
htb2.measure, htb2.start_time, htb2.stop_time,
hta.Attribute_category
FROM hxc_time_building_blocks htb,
hxc_time_building_blocks htb1,
hxc_time_building_blocks htb2,
hxc_time_attribute_usages htau,
hxc_time_attributes hta
WHERE htb1.parent_building_block_id = htb.time_building_block_id
AND htb1.parent_building_block_ovn = htb.object_version_number
AND htb.date_to = hr_general.end_of_time
AND htb.SCOPE = 'TIMECARD'
AND htb1.SCOPE = 'DAY'
AND htb1.date_to = hr_general.end_of_time
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number
AND htb2.SCOPE = 'DETAIL'
AND htb2.date_to = hr_general.end_of_time
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND TRUNC(htb.start_time) =
AND TRUNC(htb.stop_time) =
AND htb.resource_id =




Use the preference to get the setup for a particular resource id


hxc_preference_evaluation.resource_preferences
(<.Resource_id>,'TC_W_TCRD_LAYOUT',1,SYSDATE);


Table : hxc_pref_definitions
l_pref_table hxc_preference_evaluation.t_pref_table;


hxc_preference_evaluation.resource_preferences(,l_pref_table,sysdate);
l_index := l_pref_table.first;
LOOP
EXIT WHEN
(NOT l_pref_table.exists(l_index));
IF (l_pref_table(l_index).preference_code = 'TC_W_TCRD_LAYOUT') THEN ....
l_index := l_pref_table.next(l_index);
END LOOP;

OTL- Query To get Employee Time Card details with elements name

SELECT   hts.timecard_id, hts.resource_id, hts.start_time,hta.attribute_category,petf.element_name,(SUBSTR(hta.attribute_category,11,LENGTH(hta.attribute_category))) Element_ID,
                  hts.stop_time, hts.submission_date,
                  htb1.start_time each_day, hta.attribute1 project_id,
                  hta.attribute2 task_id, htb2.measure--, pt.task_name
             FROM hxc_time_building_blocks htb,
                  hxc_time_building_blocks htb1,
                  hxc_time_building_blocks htb2,
                  hxc_time_attribute_usages htau,
                  hxc_time_attributes hta,
                  --pa_projects_all papa,
                  hxc_timecard_summary hts,
                  pay_element_types_f petf
                  --pa_tasks pt
            WHERE htb1.parent_building_block_id = htb.time_building_block_id
              AND htb1.parent_building_block_ovn = htb.object_version_number
              AND htb.date_to = hr_general.end_of_time
              AND htb.SCOPE = 'TIMECARD'
              AND htb1.SCOPE = 'DAY'
              AND htb1.date_to = hr_general.end_of_time
              AND htb2.parent_building_block_id = htb1.time_building_block_id
              AND htb2.parent_building_block_ovn = htb1.object_version_number
              AND htb2.SCOPE = 'DETAIL'
              AND htb2.date_to = hr_general.end_of_time
              AND htau.time_building_block_id = htb2.time_building_block_id
              AND htau.time_building_block_ovn = htb2.object_version_number
              AND htau.time_attribute_id = hta.time_attribute_id
              --AND papa.project_id = hta.attribute1
              AND hts.start_time = htb.start_time
              AND hts.resource_id = htb.resource_id
              AND to_char(petf.element_type_id) =(SUBSTR(hta.attribute_category,11,LENGTH(hta.attribute_category)))
           --AND htb.resource_id = p_resource_id
              AND hts.timecard_id = :p_timecard_id
              --AND hta.attribute_category = 'PROJECTS'
            --  AND hts.approval_status = 'WORKING'
         --     AND hta.attribute2 = pt.task_id
           --   AND hta.attribute1 = pt.project_id
              --and hts.timecard_id=38673
             and  hta.attribute_category is not null
             and hta.attribute_category not like 'SEC%'
         ORDER BY htb1.start_time;