Skip to main content

Query for Employee's Time Card information retrieval



SELECT DISTINCT paaf.assignment_id, hshwf.date_worked,
                         TO_CHAR (hshwf.date_worked, 'YYYY') YEAR,
                         TO_CHAR (hshwf.date_worked, 'Month') MONTH,
                         papf.employee_number,
                         hshwf.time_in, hshwf.time_out, hshwf.hours,
                         --petf.element_name,
                         (select petf1.element_name
                          from hr.pay_element_types_f petf1
                          where petf1.element_type_id = hshwf.element_type_id
                          AND TRUNC (hshwf.date_worked) BETWEEN petf1.effective_start_date
                                                      AND petf1.effective_end_date) element_name,
                         (SELECT MAX (htbb.creation_date)
                            FROM hxc.hxc_time_building_blocks htbb,
                                 hr.per_all_assignments_f paaf1
                           WHERE htbb.resource_id = paaf.person_id
                             AND paaf1.assignment_id = paaf.assignment_id
                             AND TO_CHAR (htbb.start_time, 'Month') =
                                           TO_CHAR (hshwf.date_worked, 'Month')
                             AND TO_CHAR (htbb.start_time, 'RRRR') =
                                           TO_CHAR (hshwf.date_worked, 'RRRR')
                             AND htbb.SCOPE = 'APPLICATION_PERIOD'
                             AND htbb.approval_status = 'SUBMITTED'
                             AND paaf1.primary_flag = 'Y'
                             AND ROWNUM = 1
                             AND TRUNC (hshwf.date_worked)
                                    BETWEEN paaf1.effective_start_date
                                        AND paaf1.effective_end_date)
                                                                creation_date,
                         (SELECT MAX (htbb.last_update_date)
                            FROM hxc.hxc_time_building_blocks htbb,
                                 hr.per_all_assignments_f paaf1
                           WHERE htbb.resource_id = paaf.person_id
                             AND paaf1.assignment_id = paaf.assignment_id
                             AND TO_CHAR (htbb.start_time, 'Month') =
                                           TO_CHAR (hshwf.date_worked, 'Month')
                             AND TO_CHAR (htbb.start_time, 'RRRR') =
                                           TO_CHAR (hshwf.date_worked, 'RRRR')
                             AND htbb.SCOPE = 'APPLICATION_PERIOD'
                             AND htbb.approval_status = 'APPROVED'
                             AND TRUNC (hshwf.date_worked)
                                    BETWEEN paaf1.effective_start_date
                                        AND paaf1.effective_end_date
                             AND paaf1.primary_flag = 'Y'
                             AND htbb.date_to = '31-Dec-4712'
                             AND ROWNUM = 1) approval_date                      
         FROM            hxt.hxt_sum_hours_worked_f hshwf,
                          --hxt.hxt_det_hours_worked_f hdhf,
                         --hr.pay_element_types_f petf,
                         hxt.hxt_add_assign_info_f haaif,
                         hr.per_all_people_f papf,
                         hr.per_all_assignments_f paaf,
                         hr.per_jobs pj,
                         hr.per_grades pg,
                         hr.per_person_type_usages_f pptuf,
                         hr.per_person_types ppt
                   WHERE --petf.element_type_id = hshwf.element_type_id
                     --AND
                     haaif.assignment_id = hshwf.assignment_id
                     AND hshwf.date_worked BETWEEN haaif.effective_start_date
                                              AND haaif.effective_end_date
                     --AND petf.element_name <> 'GP Regular Time'
                     AND hshwf.hours > 0
                     AND paaf.assignment_id = hshwf.assignment_id
                     AND papf.current_employee_flag = 'Y'
                     AND (TRUNC (hshwf.date_worked)
                             BETWEEN papf.effective_start_date
                                 AND papf.effective_end_date
                         )
                     AND paaf.primary_flag = 'Y'
                     AND paaf.grade_id = pg.grade_id(+)
                     AND haaif.assignment_id = paaf.assignment_id(+)
                     AND paaf.job_id = pj.job_id(+)
                     AND (TRUNC (hshwf.date_worked)
                             BETWEEN haaif.effective_start_date
                                 AND haaif.effective_end_date
                         )
                     AND (TRUNC (hshwf.date_worked)
                             BETWEEN paaf.effective_start_date
                                 AND paaf.effective_end_date
                         )
                     AND (TRUNC (hshwf.date_worked)
                             BETWEEN pptuf.effective_start_date
                                 AND pptuf.effective_end_date
                         )
                     AND papf.person_id = paaf.person_id
                     --AND papf.PERSON_ID = 199564
                     AND papf.person_id = pptuf.person_id
                     AND ppt.person_type_id = pptuf.person_type_id
                     --AND paaf.assignment_id = p_assignment_id
                     --AND TO_CHAR (hshwf.date_worked, 'Month') = 'Jun'
                     --AND TO_CHAR (hshwf.date_worked, 'YYYY') = '2013'
                     /*AND (SELECT TRUNC (MAX (htbb.last_update_date))
                            FROM hxc.hxc_time_building_blocks htbb,
                                 hr.per_all_assignments_f paaf1
                           WHERE htbb.resource_id = paaf.person_id
                             AND paaf1.assignment_id = paaf.assignment_id
                             AND TO_CHAR (htbb.start_time, 'Month') =
                                           TO_CHAR (hshwf.date_worked, 'Month')
                             AND TO_CHAR (htbb.start_time, 'RRRR') =
                                           TO_CHAR (hshwf.date_worked, 'RRRR')
                             AND htbb.SCOPE = 'APPLICATION_PERIOD'
                             AND htbb.approval_status = 'APPROVED'
                             AND TRUNC (hshwf.date_worked)
                                    BETWEEN paaf1.effective_start_date
                                        AND paaf1.effective_end_date
                             AND paaf1.primary_flag = 'Y'
                             AND htbb.date_to = '31-Dec-4712'
                             AND ROWNUM = 1) BETWEEN '01-Jun-13' AND '30-Jun-13' */
                        AND hshwf.date_worked BETWEEN '01-Jun-13'AND '30-Jun-13'
Labels:

Comments

Post a Comment

Popular posts from this blog

How to get iRecruitment Vacancy details in Oracle HRMS before Vacancy is approved.

Generally details of Vacancies created in Oracle iRecruitment are store in ‘PER_ALL_VACANCIES’ table. But you cant see the vacancy details in ‘PER_ALL_VACANCIES’ table if the vacancy is NOT approved . Where does Vacancy details get stored  before Approval/Rejection As soon as vacancy is created the vacancy related data goes and resides in some temporary tables. This data is retained within the temporary tables until the vacancy is either Rejected or Approved. These temporary table names begin with name ‘HR_API%’. Not only vacancies but also other Self Service HRMS details are stored in temporary table before completion of it entire transaction, i.e either Approved or Rejected. Coming to iRecruitment vacancy, the data entered by the user is either just stored within one table HR_API_TRANSACTIONS or within HR_API_TRANSACTION_VALUES. The entire data entered by the user is captured in a CLOB column of HR_API_TRANSACTIONS. The data is captured into this CLOB Column be...
1 comment
Read more

How to deploy Oracle Custom Application developed in OAF in Oracle Application Server

Step 1:Create your necessary Database Obejcts i.e.Tables,Views,Sequences,Synonims in Apps schema. i) CREATE TABLE apps.XXGP_JDMS_APP_TABLE (   JD_ID               VARCHAR2 ( 100 BYTE),   APP_USER_NAME       VARCHAR2 ( 100 BYTE),   APP_STATUS          VARCHAR2 ( 100 BYTE),   APP_LEVEL           NUMBER ,   APPROVER_COMMENTS   VARCHAR2 ( 1000 BYTE),   NOTIFICATION_ID     VARCHAR2 ( 100 BYTE),   CREATED_BY          NUMBER ,   CREATION_DATE       DATE ,   LAST_UPDATE_LOGIN   NUMBER ,   LAST_UPDATED_BY     NUMBER ,   LAST_UPDATE_DATE    DATE ) ii) DROP SEQUENCE APPS . GP_JDMS_TRX_SEQ ; CREATE SEQUEN...
Post a Comment
Read more