OTL SQl Scripts

Alert to Users who have not filled time sheet:

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

RICE Components

The acronym, RICE, stands for reports, interfaces, conversions, and enhancements (or extensions).  

During a Oracle Applications implementation, additional configuration, design, or programming requirements are necessary to satisfy functional requirements and achieve the desired functionality. The objective during any implementation would be to keep new object development to an absolute minimum. 

RICE objects represent requirements not currently supported within the core functionality of an Oracle Application Module being implemented and thus require additional technical development to satisfy the functional requirement of a organization.

During an Implementation development of RICE components involves:

1. Creation of Reports that are not available as standard reports in the oracle apps modules.

2. Creation of the Interfaces to external systems.

3. Creation of Conversion programs to transfer data from a legacy system to the new system.

4. Enhancements (or extensions) to the existing system to add additional functionality to existing system without disturbing the core software functionality of the system.

A RICE object has a lifecycle throughout the project that includes approval to develop, functional design specification, technical design specification, coding, testing and implementation. The effort required to complete this development cycle can be significant.

Oracle HRMS SQL Queries

Active Employee in FND USER:

select * from fnd_user where end_date is null;
select * from fnd_user where sysdate between start_date and nvl(end_date, sysdate)

Inactive Employee in FND USER:

select * from fnd_user where end_date is not null;
select * from fnd_user where nvl(end_date, sysdate) <= sysdate 

Active Employee in HRMS per_all_people_f:

select * from per_all_people_f where sysdate between effective_start_date and effective_end_date

Inactive Employee in HRMS per_all_people_f:

select * from per_all_people_f where effective_end_date <= sysdate

Active Employee in FND USER and  Active Employee in HRMS PER_ALL_PEOPLE_F:

  SELECT   user_id,
           user_name,
           full_name,
           fu.end_date,
           effective_start_date,
           effective_end_date,
           last_name,
           full_name
    FROM   fnd_user fu, per_all_people_f papf
   WHERE   papf.person_id = fu.employee_id
           AND SYSDATE BETWEEN fu.start_date AND NVL (fu.end_date, SYSDATE)
           AND SYSDATE BETWEEN papf.effective_start_date
                           AND  NVL (papf.effective_end_date, SYSDATE)
ORDER BY   2;

Active Employee in FND USER and Inactive Employee in HRMS PER_ALL_PEOPLE_F:

SELECT   user_id,
         user_name,
         full_name,
         fu.start_Date,
         fu.end_date,
         papf.effective_start_date,
         papf.effective_end_date
  FROM   fnd_user fu, per_all_people_f papf
 WHERE       papf.person_id = fu.employee_id
         AND effective_end_date <= SYSDATE
    --     AND end_date >= effective_end_date
         AND SYSDATE BETWEEN fu.start_date AND NVL (fu.end_date, SYSDATE)
         
To get Active Employees and Terminated Employees in HRMS:

SELECT   PAAF.ASSIGNMENT_ID,
         PAPF.PERSON_ID AS PERSON_ID2,
         PAPF.FIRST_NAME AS FIRST_NAME,
         PAPF.LAST_NAME AS LAST_NAME,
         PAPF.EMAIL_ADDRESS AS EMAIL_ADDRESS,
         TO_CHAR (PPS.ACTUAL_TERMINATION_DATE) AS ACTUAL_TERMINATION_DATE,
         TO_CHAR (PAPF.EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE,
         PAPF.EMPLOYEE_NUMBER AS EMPLOYEE_NUMBER,
         TO_CHAR (PAPF.EFFECTIVE_END_DATE) AS EFFECTIVE_END_DATE,
         PAPF.BUSINESS_GROUP_ID AS BUSINESS_GROUP_ID,
         PAAF.SUPERVISOR_ID AS SUPERVISOR_ID,
         PAPF.LAST_UPDATE_DATE papf_update_date,
         PAAF.LAST_UPDATE_DATE paaf_update_date,
         ppt.user_person_type
  FROM   PER_ALL_PEOPLE_F PAPF,
         PER_ALL_ASSIGNMENTS_F PAAF,
         PER_PERIODS_OF_SERVICE PPS,
         hr.per_person_type_usages_f pptu,
         hr.per_person_types ppt
 WHERE   PAAF.PERSON_ID = PAPF.PERSON_ID 
         AND PAAF.PRIMARY_FLAG = 'Y' 
         AND paaf.assignment_type != 'B'
         AND pptu.effective_start_date BETWEEN papf.effective_start_date
                                           AND  papf.effective_end_date
         AND papf.person_id = pptu.person_id
         AND papf.person_type_id = pptu.person_type_id
         AND pptu.person_type_id = ppt.person_type_id
         AND papf.person_type_id = ppt.person_type_id
         AND papf.business_group_id = ppt.business_group_id
         AND PAAF.period_of_service_id = PPS.period_of_service_id
         AND papf.person_id = pps.person_id
         AND ( (ppt.user_person_type LIKE 'Ex-employee%'
                AND PPS.ACTUAL_TERMINATION_DATE BETWEEN paaf.effective_start_date
                                                    AND  PAAF.EFFECTIVE_END_DATE)
              OR (ppt.user_person_type LIKE 'Employee%'
                  AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                          AND  PAAF.EFFECTIVE_END_DATE))
         AND ( (ppt.user_person_type LIKE 'Ex-employee%'
                AND PPS.ACTUAL_TERMINATION_DATE BETWEEN papf.effective_start_date
                                                    AND  PAPF.EFFECTIVE_END_DATE)
              OR (ppt.user_person_type LIKE 'Employee%'
                  AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                          AND  PAPF.EFFECTIVE_END_DATE))

HRMS Employee details Query:
              
  SELECT   PAPF.EMPLOYEE_NUMBER,
           PAPF.TITLE || ' ' || PAPF.FULL_NAME Emp_name,
           PAPF.SEX,
           MARITAL_STATUS,
           PPT.USER_PERSON_TYPE,
           PAPF.NATIONALITY,
           HOU.ORGANIZATION_ID ORG_ID,
           HOU.NAME ORG,
           PJT.NAME JOB,
           PGT.NAME GRADE,
           PA.ADDRESS_LINE1,
           PA.ADDRESS_LINE2,
           PA.ADDRESS_LINE3,
           PA.TOWN_OR_CITY,
           PA.POSTAL_CODE,
           PA.COUNTRY,
           PPG.GROUP_NAME,
           PPB.NAME PAY,
           PPB.PAY_BASIS
    FROM   PER_ALL_ASSIGNMENTS_F PAAF,
           HR_OPERATING_UNITS HOU,
           PER_JOBS_TL PJT,
           PER_GRADES_TL PGT,
           PER_ADDRESSES PA,
           PAY_PEOPLE_GROUPS PPG,
           PER_PAY_BASES PPB,
           PER_ALL_PEOPLE_F PAPF,
           PER_PERSON_TYPES PPT
   WHERE       PAAF.ORGANIZATION_ID = HOU.ORGANIZATION_ID
           AND PAAF.JOB_ID = PJT.JOB_ID
           AND PAAF.GRADE_ID = PGT.GRADE_ID
           AND PAAF.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID
           AND PAAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
           AND PAAF.PERSON_ID = PAPF.PERSON_ID
           AND PAPF.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
           AND PA.PERSON_ID = PAPF.PERSON_ID
           AND TO_DATE (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                                     AND  PAPF.EFFECTIVE_END_DATE
           AND TO_DATE (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
                                     AND  PAAF.EFFECTIVE_END_DATE
           AND PPT.USER_PERSON_TYPE = 'Employee'
ORDER BY   PAPF.EMPLOYEE_NUMBER


To Get the Hierarchical Information:

SELECT   a.employee_id,
         a.first_name,
         a.last_name,
         a.manager_id,
         b.first_name mgr_first_name,
         b.last_name mgr_last_name
  FROM   (    SELECT   employee_id,
                       first_name,
                       last_name,
                       manager_id
                FROM   employees
          START WITH   employee_id = 100 
          CONNECT BY   PRIOR employee_id = manager_id) a, employees b
 WHERE   a.manager_id = b.employee_id(+)

Retrieve ex-employees for last 3 months:

SELECT   *
  FROM   per_all_people_f papf, per_periods_of_service ppos
 WHERE   papf.person_id = ppos.person_id
         AND ppos.actual_termination_date BETWEEN ADD_MONTHS (SYSDATE, -3)
                                              AND  SYSDATE                               
         AND ppos.ACTUAL_TERMINATION_DATE BETWEEN papf.effective_start_date
                                            AND  papf.effective_end_date