SQL Query to Extract Employee Contacts

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = ‘CONTACT’
AND hl.lookup_code(+) = pcr.contact_type

Purchase Orders - Approval Hierarchy

Purchasing > Setup > Organizations > Financial Options > Human Resources 
To Use Approval Hierarchies,the checkbox needs to be enabled.
On enabling, it shows that Position Hierarchy is used and then only then Positions are enabled in the assignment screen. 
To use Job based approval, disable the use approval hierarchy checkbox.

Note: Though you create Position in the Human Resources , if the above setup is not done ,then the Field is greyed out. 

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

Oracle Project Public API - Project Billing Agreements

Package Name: Create Agreement

Internal Name: PA_AGREEMENT_PUB

Description: This package contains the public AMG APIs for agreement and Funding procedures that will be used by the external system

APIs (Functions and Procedures):

Name	Internal Name	Description
Create Multiple Project Agreements - Clear	CLEAR_AGREEMENT	This API clears the globals that were set up during initialization
Create Multiple Project Agreements - Execute Create	EXECUTE_CREATE_AGREEMENT	This API creates an agreement with the funding using the data stored in the global tables during the load phase
Create Multiple Project Agreements-Initialize	INIT_AGREEMENT	This API sets the global tables used by load-execute-fetch procedures that create a new agreement or update an existing agreement
Create Multiple Project Agreements-Load	LOAD_AGREEMENT	This API loads an agreement to a PL/SQL record
Create Multiple Project Fundings - Fetch	FETCH_FUNDING	This API gets the return status that was returned during creation of funds and stored in a global PL/SQL table
Create Multiple Project Fundings-load	LOAD_FUNDING	This API loads funding to a PL/SQL table
Create Project Agreement	CREATE_AGREEMENT	This API creates an agreement with associated funds from an external system
Create Project Funding	ADD_FUNDING	This API adds funding to an agreement
Delete Project Agreement	DELETE_AGREEMENT	This API deletes an agreement with associated funds from an external system
Delete Project Funding	DELETE_FUNDING	This API deletes a fund from an agreement
Update Multiple Project   Agreements	EXECUTE_UPDATE_AGREEMENT	This API updates an agreement with the funding using the data stored in the global tables during the load phase In order to execute this API the following list of API's should be executed in the order of sequence.
Update Project Agreement	UPDATE_AGREEMENT	This API updates an agreement and associated funds
Update Project Funding	UPDATE_FUNDING	This API updates a fund for an agreement
Validate Project Agreement Deletion	CHECK_DELETE_AGREEMENT_OK	This API checks whether an agreement can be deleted.
Validate Project Funding Creation	CHECK_ADD_FUNDING_OK	This API checks whether a fund can be added.
Validate Project Funding Deletion	CHECK_DELETE_FUNDING_OK	This API checks whether a fund can be deleted
Validate Project Funding Update	CHECK_UPDATE_FUNDING_OK	This API checks whether a fund can be added.

Oracle Project Public API - Project Budgets

Package Name: Create Budget

Internal Name: PA_BUDGET_PUB

Description: This package contains the public AMG APIs for Budgets that will be used by the external system for preparing budget and use the API's to interface the budget and budget line into Oracle Projects.

APIs (Functions and Procedures):

Name	Internal Name	Description
Create Budget Line	ADD_BUDGET_LINE	This API is used to add a budget line to a working budget in Oracle Projects for a given project and budget type.
Create Draft Budget	CREATE_DRAFT_BUDGET	This API is used to create a draft budget and its budget lines in Oracle Projects for a given project, using a selected budget type and budget entry method.
Create Multiple Budgets- Load Budget Line	LOAD_BUDGET_LINE	This API is used to load a budget line to a global PL/SQL table.
Create Multiple Budgets-Clear	CLEAR_BUDGET	This API is used clear the global data structures set up during the Initialize step.
Create Multiple Budgets-Create Draft Budget	EXECUTE_CREATE_DRAFT_BUDGET	This API is used to create a budget and its budget lines using the data stored in the global tables during the Load process.
Create Multiple Budgets-Execute Calculate Amounts	EXECUTE_CALCULATE_AMOUNTS	This API is used to calculate the raw cost, burdened cost, and revenue amounts using existing budget lines for a given project and budget type In order to execute this API, the following list of API's should be executed in order of sequence.
Create Multiple Budgets-Fetch Calculate Amounts	FETCH_CALCULATE_AMOUNTS	This API is used to get the raw cost, burdened cost, and revenue amounts by budget line from global records updated by the API In order to execute this API, the following list of API's should be executed in order of sequence.
Create Multiple Budgets-Initialize	INIT_BUDGET	This API is used to set up the global data structures that other Load Execute Fetch procedures use to create a new or update an existing draft budget in Oracle Projects In order to execute this API, the following list of API's should be
Create Multiple Budgets-Initialize Calculate Amounts	INIT_CALCULATE_AMOUNTS	This API issued to set up the global data structures used by the Load Execute Fetch API CALCULATE_AMOUNTS In order to execute this API, the following list of API's should be executed in order of sequence.
Create Multiple Budgets-Load Fetch Line	FETCH_BUDGET_LINE	This API is used to retrieve the return status returned during the creation of a budget line from a global PL/SQL table.
Delete Budget Line	DELETE_BUDGET_LINE	This API is used to delete a budget line from a working budget in Oracle Projects for a given project and budget type.
Delete Draft Budget	DELETE_DRAFT_BUDGET	This API is used to delete a working budget in Oracle Projects for a given project and budget type.
Fetch Calculate Amounts	FETCH_CALCULATE_AMOUNTS	This API is used to get the raw cost, burdened cost, and revenue amounts by budget line from global records updated by the API
Get Budget Amounts	CALCULATE_AMOUNTS	Using the PA_CLIENT_EXTN_BUDGET extension, you can use the public API CALCULATE_AMOUNTS to recalculate raw cost, burdened cost, and revenue amounts for existing budget lines.
Update Budget	UPDATE_BUDGET	This API is used to update the working budget with its budget lines in Oracle Projects for a given project.
Update Budget Line	UPDATE_BUDGET_LINE	This API is used to update an existing budget line of a working budget in Oracle Projects for a given project and budget type.
Update Multiple Budgets	EXECUTE_UPDATE_BUDGET	This API is used to update a budget and its budget lines using the data stored in the global tables during the Load process.
Verify Budget Amounts	BASELINE_BUDGET	This API is used to baseline an existing budget in Oracle Projects for a given project and budget type