HR Person API - Update Person (Employee and Contingent Worker)

HR Person API - Update Person (Employee and Contingent Worker)

//Update Employee and Contingent Worker API. Also help you for integration between Oracle ERP HR and SOA

DECLARE
   v_api_error                   VARCHAR2 (5000);
   v_error_msg                   VARCHAR2 (5000) := NULL;
   p_nationality                 VARCHAR2 (200);
   p_person_type_id              NUMBER;
   p_employee_number             NUMBER;
   p_person_id                   NUMBER;
   p_assignment_id               NUMBER;
   p_per_object_version_number   NUMBER:=19;
   p_asg_object_version_number   NUMBER;
   p_per_effective_start_date    DATE;
   p_per_effective_end_date      DATE;
   p_full_name                   VARCHAR2 (150);
   p_per_comment_id              NUMBER;
   p_assignment_sequence         NUMBER;
   p_assignment_number           VARCHAR2 (100);
   p_name_combination_warning    BOOLEAN;
   p_assign_payroll_warning      BOOLEAN;
   p_orig_hire_warning           BOOLEAN;
   p_business_group_id           NUMBER;
   p_object_version_number       NUMBER;
   p_validate                    BOOLEAN;
   p_effective_date              DATE;
   p_effective_start_date        DATE;
   p_effective_end_date          DATE;
   p_comment_id                  NUMBER;
   p_emp_num                     VARCHAR2 (50);
 
  BEGIN
         p_emp_num := '769123';
         p_validate := FALSE;
         p_effective_date := TRUNC (SYSDATE);
     
         hr_person_api.update_person
                    (p_email_address                 => 'k@b.com',
                     p_title                         => 'MR',
                     p_person_type_id                => 3186,
                     p_validate                      => p_validate,
                     p_effective_date                => p_effective_date,
                     p_datetrack_update_mode         => 'CORRECTION',
                     p_person_id                     => 221187,
                     p_object_version_number         => p_per_object_version_number,
                     p_employee_number               => p_emp_num,
                     p_full_name                     => p_full_name,
                     p_effective_start_date          => p_effective_start_date,
                     p_effective_end_date            => p_effective_end_date,
                     p_comment_id                    => p_comment_id,
                     p_name_combination_warning      => p_name_combination_warning,
                     p_assign_payroll_warning        => p_assign_payroll_warning,
                     p_orig_hire_warning             => p_orig_hire_warning
                    );
      EXCEPTION
         WHEN OTHERS
         THEN
            p_emp_num := NULL;
            p_validate := NULL;
            p_effective_date := NULL;
            p_person_id := NULL;
            p_effective_start_date := NULL;
            p_effective_end_date := NULL;
            p_comment_id := NULL;
 END;

How to get Net Entitlement of Leaves in Oracle HRMS Payroll

How to get Net Entitlement of Leaves in Oracle HRMS Payroll

How to get net entitlement of leaves of an employee on a specific date in oracle hrms??

Please refer to the function code below.





CREATE OR REPLACE FUNCTION CUST_GET_NET_ENTITLEMENT (
   P_ASSG_ID            IN   NUMBER,
   P_PAYROLL_ID         IN   NUMBER,
   P_CALCULATION_DATE   IN   DATE
)
   RETURN NUMBER
IS
   L_START_DATE         DATE;
   L_END_DATE           DATE;
   L_ACC_END_DATE       DATE;
   L_VALUE              NUMBER         := 0;
   L_NET_VALUE          NUMBER (15, 2);
   P_PLAN_ID            NUMBER;
--   L_CALCULATION_DATE   DATE;
BEGIN
   SELECT PAP.ACCRUAL_PLAN_ID
     INTO P_PLAN_ID
     FROM PAY_ACCRUAL_PLANS PAP
    WHERE UPPER (PAP.ACCRUAL_PLAN_NAME) LIKE
             (SELECT UPPER (PETF.ELEMENT_NAME)
                FROM PAY_ELEMENT_ENTRIES_F PEEF, PAY_ELEMENT_TYPES_F PETF
               WHERE PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
                 AND UPPER (PETF.ELEMENT_NAME) LIKE '%PLAN%'
                 AND PETF.PROCESSING_TYPE = 'R'
                 AND PEEF.ASSIGNMENT_ID = P_ASSG_ID
                 AND P_CALCULATION_DATE BETWEEN PEEF.EFFECTIVE_START_DATE
                                            AND PEEF.EFFECTIVE_END_DATE);

/* QUERY ABOVE AUTOMATICALLY GETS THE PLAN ID OF THE ACCRUAL PLAN ID ATTACHED AS ON CALCULATION DATE*/
   PER_ACCRUAL_CALC_FUNCTIONS.GET_NET_ACCRUAL
                           (P_ASSIGNMENT_ID               => P_ASSG_ID,
                            P_PLAN_ID                     => P_PLAN_ID,
                            P_PAYROLL_ID                  => P_PAYROLL_ID,
                            P_BUSINESS_GROUP_ID           => 81,   -- Kindly change your business group id accordingly
                            P_ASSIGNMENT_ACTION_ID        => -1,
                            P_CALCULATION_DATE            => TO_DATE
                                                               (P_CALCULATION_DATE
                                                                )
-- DATE YOU WANT TO CHECK THE NET ENTITLEMENT EX. TO_DATE('01-MAR-2009', 'DD-MON-YYYY')
   ,
                            P_ACCRUAL_START_DATE          => NULL,
                            P_ACCRUAL_LATEST_BALANCE      => NULL,
                            P_CALLING_POINT               => 'FRM',
                            P_START_DATE                  => L_START_DATE,
                            P_END_DATE                    => L_END_DATE,
                            P_ACCRUAL_END_DATE            => L_ACC_END_DATE,
                            P_ACCRUAL                     => L_VALUE,
                            P_NET_ENTITLEMENT             => L_NET_VALUE
                           );
   RETURN NVL (L_NET_VALUE, 0);
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN 0;
END;
/

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'

Hierarchical Queries using SYS_CONNECT_BY_PATH clause

Hierarchical Queries using SYS_CONNECT_BY_PATH clause


Purpose

SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row

returned by CONNECT BY condition.

Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as

column.

Examples

The following example returns the path of employee names from employee KING to all employees of KING (and their employees):


SELECT LPAD (' ', 2 * LEVEL - 1) || SYS_CONNECT_BY_PATH (ename, '/') "Path"
      FROM scott.emp
START WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr;

Path
---------------------------------------------------------------
  /KING
   /KING/JONES
     /KING/JONES/SCOTT
       /KING/JONES/SCOTT/ADAMS
     /KING/JONES/FORD
       /KING/JONES/FORD/SMITH
   /KING/BLAKE
     /KING/BLAKE/ALLEN
     /KING/BLAKE/WARD
     /KING/BLAKE/MARTIN
     /KING/BLAKE/TURNER
     /KING/BLAKE/JAMES
   /KING/CLARK
     /KING/CLARK/MILLER

How to Get Privelege Leave Initial Balance Using Seeded Oracle Package

How to Get Privelege Leave Initial Balance Using Seeded Oracle Package


SELECT papf.person_id,
         paaf.assignment_id,
         papf.employee_number,
         papf.FULL_NAME,
         'Privelege Leave Initial Balance' element_name,
         per_accrual_calc_functions.Get_Other_Net_Contribution (
            paaf.assignment_id,
            1063,  --ACCRUAL_PLAN_ID from PAY_ACCRUAL_PLANS table
            '01-APR-2014',  --Calculation_Date
            '01-JAN-2014') --Start_Date
            Privelege_Leave_Initial_Bal
    FROM per_all_people_f papf, per_all_assignments_f paaf
   WHERE 1 = 1 AND papf.person_id = paaf.person_id
         AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.EFFECTIVE_START_DATE)
                                 AND TRUNC (papf.EFFECTIVE_END_DATE)
         AND TRUNC (SYSDATE) BETWEEN TRUNC (paaf.EFFECTIVE_START_DATE)
                                 AND TRUNC (paaf.EFFECTIVE_END_DATE)
         AND EMPLOYEE_NUMBER IS NOT NULL
--and papf.employee_number='500015'
ORDER BY 1;

PO Approval and Hierarchy Details Script

PO Approval and Hierarchy Details Script

PO Approval and Hierarchy Details Script



PO Hierarchy Details Script

SELECT *
  FROM (SELECT POS_STRUCTURE_VERSION_ID,
               b.name structure_name,
               PARENT_POSITION_ID,
               (SELECT name
                  FROM hr_all_positions_f
                 WHERE position_id = PARENT_POSITION_ID)
                  PARENT_POSITION_NAME,
               SUBORDINATE_POSITION_ID,
               (SELECT name
                  FROM hr_all_positions_f
                 WHERE position_id = SUBORDINATE_POSITION_ID)
                  SUBORDINATE_POSITION_NAME
          --(select full_name from per_all_people_f where person_id in(select person_id from per_all_assignments_f where position_id=SUBORDINATE_POSITION_ID))
          FROM per_pos_structure_elements_v a, PER_POSITION_STRUCTURES b
         WHERE     1 = 1
               AND a.POS_STRUCTURE_VERSION_ID = b.POSITION_STRUCTURE_ID
               AND POS_STRUCTURE_VERSION_ID <> 0)
 WHERE STRUCTURE_NAME = 'XX_STRUTURE_NAME'          




PO Approval Group Details Script

SELECT decode(ppca.org_id,83,'XXORG_NAME',181,'XXORG_NAME') Org_Name,
--       ppca.position_id,
       (select name from per_positions where position_id=ppca.position_id)Position_Name,
--       ppca.job_id,
       (select name from per_jobs where job_id=ppca.job_id) Job_Name,
--       ppca.control_group_id,
--       ppca.control_function_id,
       control_function_name Document_Type,
       control_group_name Approval_Group
  FROM po_position_controls_all ppca,
       po_control_groups_all pcga,
       po_control_functions pcf
 WHERE     1 = 1
       AND ppca.control_group_id = pcga.control_group_id
       AND ppca.control_function_id = pcf.control_function_id;

Inventory Item Details Script

Inventory Item Details Script

SELECT
       a.ORGANIZATION_ID,
       a.ORGANIZATION_CODE,
       a.SUBINVENTORY_CODE,
       a.LOCATOR_ID,
       a.LOCATOR,
       a.INVENTORY_ITEM_ID,
       a.ITEM,
       a.ITEM_DESCRIPTION,
       a.UOM,
       sum(a.ON_HAND) ON_HAND_QTY,
       b.ITEM_COST,
       (sum(a.ON_HAND) *  b.ITEM_COST) Item_Value
  FROM mtl_onhand_total_mwb_v a
  ,cst_item_costs b
 WHERE a.ORGANIZATION_ID = 121
 and a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID(+)
 and a.ORGANIZATION_ID=b.ORGANIZATION_ID(+)
-- and INVENTORY_ITEM_ID=1381681
 group by a.ORGANIZATION_ID,
       a.ORGANIZATION_CODE,
       a.SUBINVENTORY_CODE,
       a.LOCATOR_ID,
       a.LOCATOR,
       a.INVENTORY_ITEM_ID,
       a.ITEM,
       a.ITEM_DESCRIPTION,
       a.UOM,
       b.ITEM_COST order by 6 desc;