Retreving HRMS / PAYROLL Element Entry Values

 SELECT DISTINCT
         PET.ELEMENT_TYPE_ID,
         PET.ELEMENT_NAME,
         PEEV.EFFECTIVE_START_DATE ELE_ENTR_EFF_STDT,
         PEEV.EFFECTIVE_END_DATE ELE_ENTR_EFF_ENDT,
         SUM (TO_NUMBER (NVL (PEEV.SCREEN_ENTRY_VALUE, 0))) SCRN_ENTRY_VALUE,
         PEE.ELEMENT_ENTRY_ID,
         PIV.NAME,
         PEL.PAYROLL_ID,
         PRF.PAYROLL_NAME,
         PEE.ASSIGNMENT_ID,
         PAPF.EMPLOYEE_NUMBER,
         PAPF.FULL_NAME
    FROM PAY_ELEMENT_TYPES_F PET,
         PAY_INPUT_VALUES_F PIV,
         PAY_ELEMENT_LINKS_F PEL,
         PAY_ELEMENT_ENTRIES_F PEE,
         PER_ALL_ASSIGNMENTS_F PAAF,
         PER_ALL_PEOPLE_F PAPF,
         PAY_ELEMENT_ENTRY_VALUES_F PEEV,
         PAY_ALL_PAYROLLS_F PRF
   WHERE     PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
         AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
         AND PIV.INPUT_VALUE_ID = PEEV.INPUT_VALUE_ID
         AND PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID
         AND PEE.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
         AND TRUNC (SYSDATE) BETWEEN TRUNC (PEE.EFFECTIVE_START_DATE)
                                 AND TRUNC (PEE.EFFECTIVE_END_DATE)
         AND PAAF.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
         AND TRUNC (SYSDATE) BETWEEN TRUNC (PAAF.EFFECTIVE_START_DATE)
                                 AND TRUNC (PAAF.EFFECTIVE_END_DATE)
         AND PAPF.PERSON_ID = PAAF.PERSON_ID
         AND PAPF.CURRENT_EMP_OR_APL_FLAG = 'Y'
         AND TRUNC (SYSDATE) BETWEEN TRUNC (PAPF.EFFECTIVE_START_DATE)
                                 AND TRUNC (PAPF.EFFECTIVE_END_DATE)
         AND PEL.PAYROLL_ID = PRF.PAYROLL_ID
         AND :PAY_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
                                 AND PET.EFFECTIVE_END_DATE
         AND :PAY_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE
                                 AND PIV.EFFECTIVE_END_DATE
         AND :PAY_START_DATE BETWEEN PEL.EFFECTIVE_START_DATE
                                 AND PEL.EFFECTIVE_END_DATE
         AND :PAY_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE
                                 AND PEE.EFFECTIVE_END_DATE
         AND :PAY_START_DATE BETWEEN PEEV.EFFECTIVE_START_DATE
                                 AND PEEV.EFFECTIVE_END_DATE
         AND PEE.ASSIGNMENT_ID = :ASSIGN_ID
         AND PET.ELEMENT_NAME LIKE :ELE_NAME
         --and piv.name='Pay Value'
         AND PIV.NAME = 'Amount'
GROUP BY PET.ELEMENT_TYPE_ID,
         PET.ELEMENT_NAME,                                      
         PEEV.EFFECTIVE_START_DATE,
         PEEV.EFFECTIVE_END_DATE,
         PEE.ELEMENT_ENTRY_ID,
         PEEV.SCREEN_ENTRY_VALUE,
         PIV.NAME,
         PEE.ASSIGNMENT_ID,
         PEL.PAYROLL_ID,
         PRF.PAYROLL_NAME,
         PAPF.EMPLOYEE_NUMBER,
         PAPF.FULL_NAME
ORDER BY PEE.ASSIGNMENT_ID, PEEV.EFFECTIVE_START_DATE DESC;

Query to get EIT and SIT in HRMS

Query to get EIT and SIT in HRMS

Query to get Extra Information Types (EIT) and Special Information Types (SIT) values from HRMS Module:

Say 'Certification Course Details', 'Visa Information'...etc are SITs in HRMS. Below example is used to get 'Certification Course Details' of employees:

SELECT   papf.employee_number, papf.full_name, ppt.user_person_type emptype,
         SUBSTR (hsck.concatenated_segments,
                 1,
                 INSTR (hsck.concatenated_segments, '|') - 1
                ) company_name,
         paaf.effective_start_date,
         TO_CHAR (TO_DATE (pac.segment2, 'YYYY/MM/DD HH24:MI:SS'),
                  'DD-MON-YYYY'
                 ) paid_date,
         pac.segment3 amount, pac.segment4 amnt_type, pac.segment1 course
    FROM per_all_people_f papf,
         per_all_assignments_f paaf,
         hr_soft_coding_keyflex hsck,
         per_person_analyses ppa,
         fnd_id_flex_structures fifs,
         per_special_info_types psit,
         per_analysis_criteria pac,
         per_person_types ppt
   WHERE paaf.person_id = papf.person_id
     AND ppt.person_type_id = papf.person_type_id
     AND pac.id_flex_num = fifs.id_flex_num
     AND fifs.id_flex_structure_code = 'Certification Course Details'
     AND ppt.person_type_id(+) = papf.person_type_id
     AND psit.id_flex_num = pac.id_flex_num
     AND ppa.person_id (+) = papf.person_id
     AND pac.analysis_criteria_id (+) = ppa.analysis_criteria_id
     AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id(+)
     AND paaf.assignment_type = 'E'
     AND ppt.user_person_type <> 'Ex-employee'
     AND papf.business_group_id = :p_business_group_id
     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.effective_start_date between :p_from_date and :p_to_date
GROUP BY papf.full_name,
         papf.employee_number,
         ppt.user_person_type,
         paaf.effective_start_date,
         hsck.concatenated_segments,
         pac.segment2,
         pac.segment3,
         pac.segment4,
         pac.segment1;



Say 'Passport Details' is one the EITs in HRMS, then find the below query to get the passport information for particular employee

-- Data may have only in one field OR all fields
SELECT pei_information1,
       pei_information2,              
       pei_information3,
       pei_information4,
       pei_information5,
       pei_information6,
       pei_information7,
       pei_information8,
       pei_information9,
       pei_information10,
       pei_information11,
       pei_information12,
       pei_information13            
  FROM per_people_extra_info
 WHERE pei_information_category = 'Passport Details'    -- Could be any other EIT category
   AND person_id = :p_person_id;

HRMS API's

HRMS API's

Updating the Per_periods_of_service table using
hr_ex_employee_api.update_term_details_emp

Terminating using
hr_ex_employee_api.actual_termination_emp

Applying the final process in the shared instance using hr_ex_employee_api.final_process_emp

Re - Hire
hr_employee_api.re_hire_ex_employee

Updation On Already Existing Records
hr_person_api.update_us_person

New Hire
hr_employee_api.create_us_employee

Costing
pay_cost_allocation_api.create_cost_allocation

Load Update Assign
hr_assignment_api.update_us_emp_asg

Load Update Assign Criteria
hr_assignment_api.update_emp_asg_criteria

IF CONTACT PERSON ALREADY CREATED AND ONLY RELATION SHIP IS TO BE CREATED -
Note : Contact Person Id Is To Be Passed
hr_contact_rel_api.create_contact

If Contact Person Already Not Created
Note : Contact Person Id is passed as Null(default of API)
hr_contact_rel_api.create_contact

Load Phones
hr_phone_api.create_phone
hr_person_address_api.update_person_address

Load Addresses
hr_person_address_api.update_person_address
hr_person_address_api.create_person_address

Load Payment Methods
hr_personal_pay_method_api.create_us_personal_pay_method

Element Loading
py_element_entry_api.create_element_entry
py_element_entry_api.update_element_entry

Load Salaries
hr_upload_proposal_api.upload_salary_proposal

Approve Salary Proposal
hr_maintain_proposal_api.approve_salary_proposal

Starts To Validate/Load Federal Tax For A Person pay_federal_tax_rule_api.update_fed_tax_rule

State tax rules
pay_state_tax_rule_api.create_state_tax_rule
pay_state_tax_rule_api.update_state_tax_rule

County Tax Rules
pay_county_tax_rule_api.create_county_tax_rule
pay_county_tax_rule_api.update_county_tax_rule

City Tax Rules
pay_city_tax_rule_api.create_city_tax_rule
pay_city_tax_rule_api.update_city_tax_rule

Schools and Colleges
per_esa_upd.upd
per_esa_ins.ins

Performance Reviews
hr_perf_review_api.create_perf_review
hr_perf_review_api.update_perf_review

State Information Taxes
hr_sit_api.update_sit
hr_sit_api.create_sit

Qualifications
per_qualifications_api.create_qualification
per_qualifications_api.update_qualification

Locations
hr_location_api.update_location
hr_location_api.create_location

Organization
hr_organization_api.update_organization
hr_organization_api.create_org_classification

If any Change in Organization information.
if information2 = 'Y' then
hr_organization_api.enable_org_classification

If any Change in Organization information.
if information2 = 'N' then
hr_organization_api.disable_org_classification

If Organization does not exist in instance
hr_organization_api.create_organization api

Jobs
hr_job_api.update_job
hr_job_api.create_job

Positions
hr_position_api.update_position
hr_position_api.create_position

The query to get the list of HRMS API's in Oracle is as follows:

select *
from all_objects
where object_name like 'HR%\_API' escape '\'
and object_type = 'PACKAGE'
union
select *
from all_objects
where object_name like 'PAY%\_API' escape '\'
and object_type = 'PACKAGE'
union
select *
from all_objects
where object_name like 'PER%\_API' escape '\'
and object_type = 'PACKAGE'

Deleting Value Set Values From Backend

DECLARE
   l_err_msg   VARCHAR2 (500) := NULL;
   CURSOR c1
   IS
      SELECT ffv.flex_value_id, ffv.flex_value
        FROM fnd_flex_value_sets ffvs,
             fnd_flex_values ffv,
             fnd_flex_values_tl ffvt
       WHERE     flex_value_set_name = 'testtest' -- Value Set Name
             AND ffv.flex_value_set_id = ffvs.flex_value_set_id
             AND ffvt.flex_value_id = ffv.flex_value_id
             AND ffvs.flex_value_set_id = ffv.flex_value_set_id
             AND ffvt.language = 'US'
             AND ffv.enabled_flag = 'Y'
             AND ffv.summary_flag = 'N';
BEGIN
   FOR i IN c1
   LOOP
      fnd_flex_values_pkg.delete_row (i.flex_value_id);
      COMMIT;
      DBMS_OUTPUT.put_line (i.flex_value_id || ' Deleted  Successfully !!!!');
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Exception: ' || l_err_msg);

END;

Steps For Getting The Output in Excel Format in Oracle APPS

Steps For Getting The Output in Excel Format in Oracle APPS

Logon to Oracle Applications , use System Administrator Responsibility and Navigate as :-
Install -> Viewer Options
to open the Form “Viewer Options”

Then do the entry as shown below :-

File format - Mime Type --Description
-------------------------------------------
Text - application/msword - Microsoft Word(.doc)
Text - application/vnd.ms-excel - Excel (.xls)
Text - Notepad(.txt)

Ensure that the profile option
Viewer: Application for Text
is set to
BLANK
at the Site Level

Then ensure that the profile option
Viewer: Text
is set to
Browser
at the Site Level

Then restart the Concurrent Processing Server

Submit a Concurrent Request that generates Text Output File
Click on View Output
Button
You will see a LOV showing
Microsoft Word(.doc)
Excel (.xls)
Notepad(.txt)

When you choose Microsoft Word(.doc) , the Text File will be transferred using the Mime Type application/msword
, so the browser will open the Text File in Word

When you choose Excel (.xls) , the Text File will be transferred using the Mime Type application/vnd.ms-excel
, so the browser will open the Text File in Excel

When you choose Notepad(.txt) , the Text File will be transferred using the Mime Type text/plain
, so the browser will open the Text File in Notepad

Update using multiple conditions from multiple tables

update bom_operation_resources  set
usage_rate_or_amount = 0,
usage_rate_or_amount_inverse = 0
where exists (select c.usage_rate_or_amount,c.usage_rate_or_amount_inverse
from bom_operational_routings a,
bom_operation_sequences b,
bom_operation_resources c,
mtl_system_items d
where a.routing_sequence_id = b.routing_sequence_id
and b.operation_sequence_id = c.operation_sequence_id
and d.inventory_item_id = a.assembly_item_id
and a.organization_id = d.organization_id
and d.segment1 like 'FG%'
and bom_operation_resources.operation_sequence_id =c.operation_sequence_id)
--and c.operation_sequence_id = 3759) 

get financial year from a date in oracle

SELECT    EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -3))
       || '-'
       || EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, 9))
  FROM DUAL;