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;