Leave History details in HRMS

select paa.person_id,
       paat.name LeaveType,
       paat.name LeaveType,
       paa.absence_days Leave_Days,
       paa.date_start,
       paa.date_end,
       hr_general.decode_lookup('ABSENCE_REASON',paar.name) Absence_Reason,
       paa.attribute1 Exam_Status,
       paa.attribute2 Study_Status,
       hr_general.decode_lookup('YES_NO',paa.attribute3) Leave_Allowance,
       hr_general.decode_lookup('YES_NO',paa.attribute4) Leave_Salary,
NVL(abs( (SELECT DISTINCT SUM(TO_NUMBER(peev.screen_entry_value)) VALUE                       
                          FROM pay_element_entries_f peef,
                               pay_element_entry_values_f peev,
                               pay_element_links_f LINK,
                               pay_element_types_f TYPE,
                               pay_input_values_f input
                         WHERE TYPE.element_name = 'Annual Leave Adjustment'
                           AND input.NAME = 'Plan Days'
                           AND TYPE.business_group_id = 81
                           AND peef.assignment_id =PAAF.assignment_id
                           AND peev.element_entry_id IN (SELECT DISTINCT a.ELEMENT_ENTRY_ID                   
                          FROM pay_element_entries_f  a,
                               pay_element_entry_values_f  b,
                               pay_element_links_f  c,
                               pay_element_types_f  d,
                               pay_input_values_f e
                         WHERE d.element_name = 'Annual Leave Adjustment'
                           AND e.NAME ='Adjusted Leave Period'
                           AND b.screen_entry_value=PAA.ABSENCE_ATTENDANCE_ID
                           AND d.business_group_id = 81                         
                           AND a.assignment_id =PAAF.assignment_id
                           AND d.business_group_id = c.business_group_id
                           AND d.business_group_id =
                                                       e.business_group_id
                           AND a.element_entry_id = b.element_entry_id
                           AND a.element_link_id = c.element_link_id
                           AND c.element_type_id = d.element_type_id
                           AND d.element_type_id = e.element_type_id
                           AND b.input_value_id = e.input_value_id
                           AND paa.date_start
                                  BETWEEN d.effective_start_date
                                      AND d.effective_end_date
                           AND paa.date_start
                                  BETWEEN c.effective_start_date
                                      AND c.effective_end_date
                           AND paa.date_start
                                  BETWEEN a.effective_start_date
                                      AND a.effective_end_date
                           AND paa.date_start
                                  BETWEEN b.effective_start_date
                                      AND b.effective_end_date
                           AND paa.date_start
                                  BETWEEN e.effective_start_date
                                      AND e.effective_end_date)
                           AND TYPE.business_group_id = LINK.business_group_id
                           AND TYPE.business_group_id =
                                                       input.business_group_id
                           AND peef.element_entry_id = peev.element_entry_id
                           AND peef.element_link_id = LINK.element_link_id
                           AND LINK.element_type_id = TYPE.element_type_id
                           AND TYPE.element_type_id = input.element_type_id
                           AND peev.input_value_id = input.input_value_id
                           AND paa.date_start
                                  BETWEEN TYPE.effective_start_date
                                      AND TYPE.effective_end_date
                           AND paa.date_start
                                  BETWEEN LINK.effective_start_date
                                      AND LINK.effective_end_date
                           AND paa.date_start
                                  BETWEEN peef.effective_start_date
                                      AND peef.effective_end_date
                           AND paa.date_start
                                  BETWEEN peev.effective_start_date
                                      AND peev.effective_end_date
                           AND paa.date_start
                                  BETWEEN input.effective_start_date
                                      AND input.effective_end_date)),0) ADJUSTMENT_DAYS,
Case When paat.name='Annual Leave' Then paa.absence_days-nvl(abs( (SELECT DISTINCT sum(to_number(peev.screen_entry_value)) VALUE                       
                          FROM pay_element_entries_f peef,
                               pay_element_entry_values_f peev,
                               pay_element_links_f LINK,
                               pay_element_types_f TYPE,
                               pay_input_values_f input
                         WHERE TYPE.element_name = 'Annual Leave Adjustment'
                           AND input.NAME = 'Plan Days'
                           AND TYPE.business_group_id = 81
                           AND peef.assignment_id =PAAF.assignment_id
                           AND TYPE.business_group_id = LINK.business_group_id
                           AND TYPE.business_group_id =
                                                       input.business_group_id
                           AND peef.element_entry_id = peev.element_entry_id
                           AND peef.element_link_id = LINK.element_link_id
                           AND LINK.element_type_id = TYPE.element_type_id
                           AND TYPE.element_type_id = input.element_type_id
                           AND peev.input_value_id = input.input_value_id
                           AND peev.element_entry_id IN (SELECT DISTINCT a.ELEMENT_ENTRY_ID                   
                          FROM pay_element_entries_f  a,
                               pay_element_entry_values_f  b,
                               pay_element_links_f  c,
                               pay_element_types_f  d,
                               pay_input_values_f e
                         WHERE d.element_name = 'Annual Leave Adjustment'
                           AND e.NAME ='Adjusted Leave Period'
                           AND b.screen_entry_value=PAA.ABSENCE_ATTENDANCE_ID
                           AND d.business_group_id = 81                         
                           AND a.assignment_id =PAAF.assignment_id
                           AND d.business_group_id = c.business_group_id
                           AND d.business_group_id =
                                                       e.business_group_id
                           AND a.element_entry_id = b.element_entry_id
                           AND a.element_link_id = c.element_link_id
                           AND c.element_type_id = d.element_type_id
                           AND d.element_type_id = e.element_type_id
                           AND b.input_value_id = e.input_value_id
                           AND paa.date_start
                                  BETWEEN d.effective_start_date
                                      AND d.effective_end_date
                           AND paa.date_start
                                  BETWEEN c.effective_start_date
                                      AND c.effective_end_date
                           AND paa.date_start
                                  BETWEEN a.effective_start_date
                                      AND a.effective_end_date
                           AND paa.date_start
                                  BETWEEN b.effective_start_date
                                      AND b.effective_end_date
                           AND paa.date_start
                                  BETWEEN e.effective_start_date
                                      AND e.effective_end_date)
                           AND paa.date_start
                                  BETWEEN TYPE.effective_start_date
                                      AND TYPE.effective_end_date
                           AND paa.date_start
                                  BETWEEN LINK.effective_start_date
                                      AND LINK.effective_end_date
                           AND paa.date_start
                                  BETWEEN peef.effective_start_date
                                      AND peef.effective_end_date
                           AND paa.date_start
                                  BETWEEN peev.effective_start_date
                                      AND peev.effective_end_date
                           AND paa.date_start
                                  BETWEEN input.effective_start_date
                                      AND input.effective_end_date)),0) Else 0 End TOTAL_ANNUAL
from   per_absence_attendance_types paat,
       per_absence_attendances paa,
       per_abs_attendance_reasons paar,
per_all_assignments_f paaf
where  1=1
AND    paat.absence_attendance_type_id=paa.absence_attendance_type_id
and paa.person_id=paaf.person_id
AND PAAF.PRIMARY_FLAG='Y'
and paa.date_start between paaf.effective_start_date and paaf.effective_end_date
AND    paa.abs_attendance_reason_id =paar.abs_attendance_reason_id(+)
AND    paat.name =nvl(:p_leavetype,paat.name)
/*AND (paa.date_start BETWEEN fnd_date.canonical_to_date(:P_START_DATE) AND fnd_date.canonical_to_date(:P_END_DATE) OR
     paa.date_end BETWEEN fnd_date.canonical_to_date(:P_START_DATE) AND fnd_date.canonical_to_date(:P_END_DATE) OR
     (fnd_date.canonical_to_date(:P_START_DATE) < paa.date_start AND fnd_date.canonical_to_date(:P_END_DATE) > paa.date_end))*/
AND ((paa.date_start BETWEEN :P_START_DATE AND :P_END_DATE) OR
     (paa.date_end BETWEEN :P_START_DATE AND :P_END_DATE) OR
     (:P_START_DATE < paa.date_start AND :P_END_DATE > paa.date_end))
AND paa.person_id = :person_id
ORDER BY paat.name,paa.date_start

Performance Rating query

SELECT PAPF.EMPLOYEE_NUMBER,
         PAPF.GLOBAL_NAME                     STAFF_NAME,
         to_char(PAPF.START_DATE , 'DD-MON-YYYY') Hire_date,
         A.RATING_MEANING                     RATING,
         TO_CHAR (A.REVIEW_DATE, 'DD-MON-YYYY') REVIEW_DATE,
         A.CREATION_DATE,
         haou.name                            Department
    FROM PER_PERFORMANCE_REVIEWS_V a,
         per_all_people_f        papf,
         per_all_assignments_f   paaf,
         hr_all_organization_units haou
   WHERE     a.person_id = papf.person_id
         AND papf.person_id = paaf.person_id
         AND paaf.organization_id = haou.organization_id
         AND papf.business_group_id = 81
         AND papf.current_employee_flag = 'Y'
         and paaf.primary_flag='Y'
         AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE
                         AND PAPF.EFFECTIVE_END_DATE
         AND SYSDATE BETWEEN PAaF.EFFECTIVE_START_DATE
                         AND PAaF.EFFECTIVE_END_DATE
       --  AND PAPF.EMPLOYEE_NUMBER = NVL (:P_EMP_NO, PAPF.EMPLOYEE_NUMBER)
         AND TO_DATE (A.REVIEW_DATE) > TO_DATE ('30-JUN-2016')
         and to_date(PAPF.START_DATE)<=TO_DATE('30-JUN-2016')
        -- and PAPF.EMPLOYEE_NUMBER ='1376'
ORDER BY PAPF.EMPLOYEE_NUMBER

Oracle HRMS Payroll Process

Step 1: Payroll run
Step2: Prepayment
Step3: Costing
Step4: Transfer to GL
Step5: Post the Journals

Uploading Data to Batch Element Entries

Uploading Data to Batch Element Entries

Migrating Data from Legacy System to Oracle Apps is one hell of a task, i am sure you all would agree to that, and for the same reason Oracle provides a lot of options to make our lives easier. With options like WebADI, Pl/SQL APIs, third party Data Loaders,Data Warehousing, etc uploading data onto Oracle Apps becomes really easy and fast.

Here we are going to see one such example, Uploading Data to Batch Element Entries. We would be uploading data to this in two ways, WebADI and PAY_BATCH_ELEMENT_ENTRY_API

• WebADI

Steps;

1. Open the Batch Element Entry form (HRMS Super User -> Mass Information eXchange: MIX -> Batch Element Entry)

2. Create a Batch that you would like to create Entries for. Check appropriate Batch Control Options and Save.

3. Now Navigate to the BEE Spreadsheeter function (HRMS Super User -> Mass Information eXchange: MIX -> Bee Spreadsheeter). A JSP page will open, as shown below.

4. Select the Batch you just created under the Batch Name field and click Go.

5. Select the Batch and choose the Batch Process throught the LOV. In this case, Create Lines

6. Click on the Process button. You would be redirected to a new page, where you will be prompted to enter the Name of the Element for which you want to create the Batch Element Entries.

7. Select the Element and click Go.

8. A .xl file will download onto your machine. Open the file and it will load onto your machine a template for uploading the Data.(Make sure Macros are allowed in you Microsoft Office.)

9. In Microsoft Office Excel

i. Document Loads : Template is created.

ii. Fill in the template with the data.

iii. Upload the Data

iv. Select the appropriate Parameters and click Upload

v. Once the Data is uploaded, you will get a confirmation.

Your work here is done.

Navigate back to the Batch Element Entry Screen and query for the Batch. Click on the Element Lines button to check the Element Entries.

You can now process the Batch to Validate and Transfer this BEE Lines to Assignment Entries.

• PAY_BATCH_ELEMENT_ENTRY_API

Following are the API's to create BEE Headers and Lines.
1. To Create Batch Header

DECLARE
l_batch_id NUMBER := NULL;
l_object_version_number NUMBER := NULL;
BEGIN
pay_batch_element_entry_api.create_batch_header (
p_session_date => TRUNC (SYSDATE),
p_batch_name => <<BATCH_NAME>>,
p_business_group_id => <<Business_Group_ID>>,
p_action_if_exists => 'I',
p_batch_id => l_batch_id,
p_object_version_number => l_object_version_number
);
COMMIT;
DBMS_OUTPUT.put_line (l_batch_id || ': SUCCESS');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
-- QUERY
SELECT batch_id, batch_name
FROM pay_batch_headers
ORDER BY batch_id DESC
2. To Create Batch Line (Batch Element Entry)
DECLARE
v_eff_start_date DATE := NULL;
v_eff_end_date DATE := NULL;
v_input_value_id NUMBER := NULL;
v_element_link_id NUMBER := NULL;
v_element_name VARCHAR2 (500) := NULL;
v_element_type_id NUMBER := NULL;
v_batch_id NUMBER := NULL;
v_batch_line_id NUMBER := NULL;
v_batch_object_version_number NUMBER := NULL;
CURSOR emp_details
is
Select assignment_number, assignment_id
from <<temp_table>> -- table where your data is uploaded.

BEGIN
-- BLOCK to Get Batch ID
BEGIN
SELECT batch_id
INTO v_batch_id
FROM pay_batch_headers pbh
WHERE TRIM (pbh.batch_name) = <<Batch_Name>>;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- Get Element Details as per your Element
BEGIN
SELECT pivf.input_value_id,
pelf.element_link_id,
petf.element_name,
petf.element_type_id
INTO v_input_value_id,
v_element_link_id,
v_element_name,
v_element_type_id
FROM pay_element_types_f petf,
pay_element_links_f pelf,
pay_input_values_f pivf
WHERE pelf.element_type_id = petf.element_type_id
AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN pelf.effective_start_date
AND pelf.effective_end_date
AND pelf.payroll_id = <<payroll_id>>
AND pivf.element_type_id = pivf.element_type_id
AND pivf.NAME = <<Name of the Input value>>
AND TRUNC (SYSDATE) BETWEEN pivf.effective_start_date
AND pivf.effective_end_date
AND pivf.element_type_id = pelf.element_type_id
AND petf.element_name = <<Element_name>>;
EXCEPTION
WHEN OTHERS
THEN
v_input_value_id := NULL;
v_element_link_id := NULL;
END;
FOR i IN emp_details
LOOP
BEGIN
-- API to create ELEMENT
pay_batch_element_entry_api.create_batch_line (
p_session_date => TRUNC (SYSDATE),
p_batch_id => v_batch_id,
p_assignment_id => i.assignment_id,
p_assignment_number => i.assignment_number,
p_date_earned => TRUNC (SYSDATE),
p_effective_date => TRUNC (SYSDATE),
p_effective_start_date => v_eff_start_date,
p_effective_end_date => v_eff_end_date,
p_element_name => v_element_name,
p_element_type_id => v_element_type_id,
p_value_3 => <<value for the input value>>,
p_batch_line_id => v_batch_line_id,
p_object_version_number => v_batch_object_version_number
);
COMMIT;
DBMS_OUTPUT.put_line (v_batch_id || ': SUCCESS');
DBMS_OUTPUT.put_line (v_batch_line_id || ': SUCCESS');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception'||i.assignment_number||' '||SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
-- QUERY
SELECT *
FROM PAY_BATCH_LINES
WHERE BATCH_LINE_ID = <<batch_line_id>> -- Batch_line_id generated above