How to find Scheduled Concurrent Program Details

How to find Scheduled Concurrent Program Details

SELECT fcr.request_id
     , fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
     , fu.user_name requestor
     , fu.description requested_by
     , fu.email_address
     , frt.responsibility_name requested_by_resp
     , trim(fl.meaning) status
     , fcr.phase_code
     , fcr.status_code
     , fcr.argument_text "PARAMETERS"
      , TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
     , TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start  
     , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
     , DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
     , CASE
          WHEN fcr.hold_flag = 'Y'
             Then Substr(
                    fu.description
                  , 0
                  , 40
                 )
       END last_update_by
     , CASE
          WHEN fcr.hold_flag = 'Y'
             THEN fcr.last_update_date
       END last_update_date
     , fcr.increment_dates
     , CASE WHEN fcrc.CLASS_INFO IS NULL THEN
        'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
       ELSE
        'n/a'
       END run_once
     , CASE WHEN fcrc.class_type = 'P' THEN
        'Repeat every ' ||
        substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
               'N', ' minutes',
               'M', ' months',
               'H', ' hours',
               'D', ' days') ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
               'S', ' from the start of the prior run',
               'C', ' from the completion of the prior run')
       ELSE
         'n/a'
       END set_days_of_week
       , CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
          'Days of week: ' ||
                  decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
                  decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
                  decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
                  decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
                  decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
                  decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
                  decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
         ELSE
           'n/a'
         end  days_of_week
  FROM fnd_concurrent_requests fcr
     , fnd_user fu
     , fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpt
     , fnd_printer_styles_tl fpst
     , fnd_conc_release_classes fcrc
     , fnd_responsibility_tl frt
     , fnd_lookups fl
 WHERE fcp.application_id = fcpt.application_id
   AND fcr.requested_by = fu.user_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fcr.concurrent_program_id = fcpt.concurrent_program_id
   AND fcr.responsibility_id = frt.responsibility_id
   AND fcr.print_style = fpst.printer_style_name(+)
   AND fcr.release_class_id = fcrc.release_class_id(+)
   AND fcr.status_code = fl.lookup_code
   AND fl.lookup_type = 'CP_STATUS_CODE'
   AND fcr.phase_code = 'P'
   AND 1=1
Order By Fu.Description, Fcr.Requested_Start_Date Asc;

How to Rollback SSHR Transaction in Oracle Self Service

How to Rollback SSHR Transaction in Oracle Self Service

How to Rollback SSHR Transaction in oracle apps.

begin
hr_transaction_api.rollback_transaction(p_transaction_id);
end;

commit;

Note:

p_transaction_id is the transaction_id that you will find in hr_api_transaction table.

After executing the above statement you will not find any record in hr_api_transaction

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;