Dilli ERP Notes: October 2015

Monday 26 October 2015

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

Monday 12 October 2015

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;

Thursday 8 October 2015

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;
/