Oracle Apps Solutions: September 2014

Monday, September 8, 2014

Concurrent Request Delivery Option (Email) - Unable to determine SMTP server to use

Concurrent Request log of the concurrent program where delivery options were set -- +------------- 2) DELIVER -------------+ Emailing output to xyz@abc.com .............. .............. One or more post-processing actions failed. Consult the OPP service log for details. -- Get the OPP service log ----------------------- SYstem Administrator > Concurrent > Manager > Administer >> Select Outout Post Processor > Click on 'Processes' >> Now click on the 'Manager Log' and look for your request id and error details. OPP log giving below error. oracle.apps.fnd.cp.opp.PostProcessorException: Unable to determine SMTP server to use: set FND_SMTP_HOST Fix --- You just need to set below profile options (SYstem Administrator > Profile > System) FND: SMTP Host FND: SMTP Port How to get the SMTP Host Name ----------------------------- Application ----------- Oracle Application Manager > Workflow >> Click on the 'Notification Mailers' >> Click on "Workflow Notification Mailer" >> Check 'EMail Servers' section >> Check Outbound EMail Account >> Get the value 'Outbound Server Name' Database -------- select fscpv.parameter_value "SMTP Host Name" --SMTP protocol uses default port number 25 for outgoing emails ,25 "SMTP Port Number" ,fscpt.description from fnd_svc_comp_params_tl fscpt ,fnd_svc_comp_param_vals fscpv where fscpt.parameter_id = fscpv.parameter_id and fscpt.display_name = 'Outbound Server Name' --'Inbound Server Name' and fscpt.language = 'US';

Get FND User's Password

you might need it for user migration.

1> create a function
create or replace function xxta_get (key in varchar2, value in varchar2)
return varchar2
as
language java
name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'
;
/

2> use
>> 2.1 when you know the apps password
select xxta_get(<apps password in capital>,encrypted_user_password)
from fnd_user
where user_name = 'XXUSER' ;

>> 2.2 when you do not know the apps password
select usr.user_name,
xxta_get
((select (select xxta_get
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
from dual) as apps_password
from fnd_user usertable
where usertable.user_name =
(select substr
(fnd_web_sec.get_guest_username_pwd,
1,
instr
(fnd_web_sec.get_guest_username_pwd,
'/'
)
- 1
)
from dual)),
usr.encrypted_user_password
) password
from fnd_user usr
where usr.user_name = :user_name

OR

1> create below packaged procedure

>> package specification
create or replace package xxta_get
as
function ini_decrypt (key in varchar2, value in varchar2)
return varchar2;

function decrypt (p_user_name in varchar2)
return varchar2;
end xxta_get;
/
>> package body
create or replace package body xxta_get
as
function ini_decrypt (key in varchar2, value in varchar2)
return varchar2
as
language java
name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
function decrypt (p_user_name in varchar2)
return varchar2
as
l_pwd varchar2(40);
begin
select --usr.user_name,
ini_decrypt
((select (select ini_decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
from dual) as apps_password
from fnd_user usertable
where usertable.user_name =
(select substr
(fnd_web_sec.get_guest_username_pwd,
1,
instr
(fnd_web_sec.get_guest_username_pwd,
'/'
)
- 1
)
from dual)),
usr.encrypted_user_password
) password
into l_pwd
from fnd_user usr
where usr.user_name = p_user_name;
return l_pwd;
end;
end xxta_get;
/

use

select xxta_get.decrypt('XXUSER')
from dual;

--

FND User/Responsibility Create/Update/Delete

--
declare
--
l_mode varchar2(10) := 'CreateUser';
--
l_user_name fnd_user.user_name%type := 'AKTEST';
l_employee_id per_all_people_f.person_id%type := 12348;
l_email varchar2(40) := 'aktest@ak.com';
l_begin_date date := trunc(sysdate);
l_resp_app fnd_application.application_short_name%type := 'XXCUST';
l_resp_key fnd_responsibility.responsibility_key%type := 'XXAK_TEST_RESP';
l_security_group fnd_security_groups.security_group_key%type := 'STANDARD';
l_responsibility_id fnd_responsibility.responsibility_id%type := 12345;
l_app_id fnd_application.application_id%type := 67891;
l_security_group_id fnd_security_groups.security_group_id%type := 23456;
--
begin
--
if l_action = 'CreateUser' then
--
--Insert new user record into FND_USER table
fnd_user_pkg.createuser ( x_user_name => l_user_name
, x_employee_id => l_employee_id
, x_email_address => l_email
, x_start_date => l_begin_date
, x_unencrypted_password => 'Welcome1'
, x_owner => 'CUST'
, x_password_lifespan_days => 90 );

-- For a given user, attach a valid responsibility
fnd_user_pkg.addresp ( start_date => l_begin_date
, end_date => null
, username => l_user_name
, resp_app => l_resp_app
, resp_key => l_resp_key
, security_group => l_security_group
, description => null);

elsif l_action = 'UpdateUser' then

--Update any column for a particular user record.here I am just updating the Email Address
fnd_user_pkg.updateuser (x_user_name => l_user_name
,x_owner => 'CUST'
,x_email_address => l_email
);

--Update an existing user/resp/group assignment
fnd_user_resp_groups_api.Update_Assignment( user_id => l_user_id
,responsibility_id => l_responsibility_id
,responsibility_application_id => l_app_id
,security_group_id => l_security_group_id
,start_date => l_begin_date
,end_date => hr_general.end_of_time
,description => NULL);

elsif l_action = 'Delresp' then

-- Detach a responsibility which is currently attached to this given user
fnd_user_pkg.delresp(username => l_user_name
,resp_app => l_resp_app
,resp_key => l_resp_key
,security_group => l_security_group);

end if;
--
commit;
--
--
exception
when others then
dbms_output.put_line('Error :'||sqlerrm);
rollback;
end;
--

Thursday, September 4, 2014

Reverse Termination Employee/Contingent Worker - HRMS APIs

--
declare
--
--Common Variables
l_sys_person_type varchar2(40);
l_person_id number := 123456;

--- Declare variables for reverse termination API
l_act_term_date per_periods_of_service.actual_termination_date%type;
l_clear_details varchar2(1) := 'Y';
l_fut_actns_exist_warning boolean;
--
begin
--
select ppt.system_person_type
into l_sys_person_type
from per_all_people_f papf
,per_person_types ppt
where papf.person_id = l_person_id
and papf.person_type_id = ppt.person_type_id;
--
if l_sys_person_type like 'EX_EMP%' then

select pos.actual_termination_date
into l_act_term_date
from per_all_people_f papf
,per_periods_of_service pos
where papf.person_id = l_person_id
--AND papf.effective_start_date =
and pos.person_id = papf.person_id ;

/*
* This API is not published, hence not meant for public calls.
*/
hr_ex_employee_api.reverse_terminate_employee
( p_validate => false
, p_person_id => l_person_id
, p_actual_termination_date => l_act_term_date
, p_clear_details => l_clear_details
);

dbms_output.put_line('Employee Reverse Terminated ');
--
commit;
--
else --CWK

select pos.actual_termination_date
into l_act_term_date
from per_all_people_f papf
,per_periods_of_placement pos
where papf.person_id = l_person_id
-- AND papf.effective_start_date =
and pos.person_id = papf.person_id ;

/* This API reverses a contingent worker termination.
* This API removes the end date from the period of placement and the
* contingent worker assignments, and reverts the person type to Contingent Worker
*/
hr_contingent_worker_api.reverse_terminate_placement
( p_validate => false
, p_person_id => l_person_id
, p_actual_termination_date => l_act_term_date
, p_clear_details => l_clear_details
, p_fut_actns_exist_warning => l_fut_actns_exist_warning
);

if l_fut_actns_exist_warning then
dbms_output.put_line('Reverse Termination failed for CWK '||sqlerrm);
rollback;
else
dbms_output.put_line('CWK Reverse Terminated ');
commit;
end if;

end if;
--
exception
when others then
dbms_output.put_line('Reverse Termination failed. Error Others: '||sqlerrm);
rollback;
end;
--

Wednesday, September 3, 2014

Update Employment/Placement Details - HRMS APIs

--
declare
--
--Common Variables
l_type varchar2(10) := 'CWK';--'EMP'
l_person_id number := 123456;

--- DECLARE variables for hr_periods_of_placement_api.update_pdp_details
l_effective_date date := trunc(sysdate);
l_termination_reason per_periods_of_placement.leaving_reason%type := 'AGE_REACHED';
l_object_version_number per_periods_of_placement.object_version_number%type;
l_start_date per_periods_of_placement.date_start%type;
--
cursor cur_emp_res is
select rowid row_id --HR_GENERAL.DECODE_LOOKUP ('LEAV_REAS',a.leaving_REASON) ter_reason
,pos.*
from per_periods_of_service pos
where person_id = 123456;
--
begin
--
if l_type = 'CWK' then --Contingent Worker

-- HR_GENERAL.DECODE_LOOKUP ('HR_CWK_TERMINATION_REASONS',a.TERMINATION_REASON) termination_reason
select pos.object_version_number, date_start
into l_object_version_number, l_start_date
from per_periods_of_placement pos
where pos.person_id = l_person_id;

/*
* This API updates period of placement information for a contingent worker.
* Typically, this API is used to update the flexfields associated with periods
* of placement.
*/
hr_periods_of_placement_api.update_pdp_details(
p_validate => false
,p_effective_date => l_effective_date
,p_object_version_number => l_object_version_number
,p_person_id => l_person_id
,p_date_start => l_start_date
,p_termination_reason => l_termination_reason
--,p_attribute_category =>
--,p_attribute1 =>
--,p_attribute2 =>
--,p_attribute3 =>
);
else --Employee

for rec_per IN cur_emp_res loop

per_periods_of_service_pkg.update_row(
p_row_id => rec_per.row_id
,p_period_of_service_id => rec_per.period_of_service_id
,p_business_group_id => rec_per.business_group_id
,p_person_id => rec_per.person_id
,p_date_start => rec_per.date_start
,p_termination_accepted_per_id => rec_per.termination_accepted_person_id
,p_accepted_termination_date => rec_per.accepted_termination_date
,p_actual_termination_date => rec_per.actual_termination_date
,p_comments => rec_per.comments
,p_final_process_date => rec_per.final_process_date
,p_last_standard_process_date => rec_per.last_standard_process_date
,p_leaving_reason => l_termination_reason
,p_notified_termination_date => rec_per.notified_termination_date
,p_projected_termination_date => rec_per.projected_termination_date
,p_request_id => rec_per.request_id
,p_program_application_id => rec_per.program_application_id
,p_program_id => rec_per.program_id
,p_program_update_date => rec_per.program_update_date
,p_attribute_category => rec_per.attribute_category
,p_attribute1 => rec_per.attribute1
,p_attribute2 => rec_per.attribute2
,p_attribute3 => rec_per.attribute3
,p_attribute4 => rec_per.attribute4
,p_attribute5 => rec_per.attribute5
,p_attribute6 => rec_per.attribute6
,p_attribute7 => rec_per.attribute7
,p_attribute8 => rec_per.attribute8
,p_attribute9 => rec_per.attribute9
,p_attribute10 => rec_per.attribute10
,p_attribute11 => rec_per.attribute11
,p_attribute12 => rec_per.attribute12
,p_attribute13 => rec_per.attribute13
,p_attribute14 => rec_per.attribute14
,p_attribute15 => rec_per.attribute15
,p_attribute16 => rec_per.attribute16
,p_attribute17 => rec_per.attribute17
,p_attribute18 => rec_per.attribute18
,p_attribute19 => rec_per.attribute19
,p_attribute20 => rec_per.attribute20
,p_pds_information_category => rec_per.pds_information_category
,p_pds_information1 => rec_per.pds_information1
,p_pds_information2 => rec_per.pds_information2
,p_pds_information3 => rec_per.pds_information3
,p_pds_information4 => rec_per.pds_information4
,p_pds_information5 => rec_per.pds_information5
,p_pds_information6 => rec_per.pds_information6
,p_pds_information7 => rec_per.pds_information7
,p_pds_information8 => rec_per.pds_information8
,p_pds_information9 => rec_per.pds_information9
,p_pds_information10 => rec_per.pds_information10
,p_pds_information11 => rec_per.pds_information11
,p_pds_information12 => rec_per.pds_information12
,p_pds_information13 => rec_per.pds_information13
,p_pds_information14 => rec_per.pds_information14
,p_pds_information15 => rec_per.pds_information15
,p_pds_information16 => rec_per.pds_information16
,p_pds_information17 => rec_per.pds_information17
,p_pds_information18 => rec_per.pds_information18
,p_pds_information19 => rec_per.pds_information19
,p_pds_information20 => rec_per.pds_information20
,p_pds_information21 => rec_per.pds_information21
,p_pds_information22 => rec_per.pds_information22
,p_pds_information23 => rec_per.pds_information23
,p_pds_information24 => rec_per.pds_information24
,p_pds_information25 => rec_per.pds_information25
,p_pds_information26 => rec_per.pds_information26
,p_pds_information27 => rec_per.pds_information27
,p_pds_information28 => rec_per.pds_information28
,p_pds_information29 => rec_per.pds_information29
,p_pds_information30 => rec_per.pds_information30
,p_adjusted_svc_date => rec_per.adjusted_svc_date
);
end loop;

end if;

--
commit;
--
exception
when others then
dbms_output.put_line('Update Placement/Employement Details. Error OTHERS while validating: '||sqlerrm);
rollback;
end;
--

Terminate Contingent Worker - HRMS APIs

--
declare
--
--Common Variables
l_terminate_cwk_flag varchar2(1) := 'N';
l_terminate_msg varchar2(600);
l_person_id number := 123456;
l_le_terminate_cwk_exception exception;

--- DECLARE variables for hr_contingent_worker_api.actual_termination_placement
--- IN variables
l_effective_date date := trunc(sysdate);
l_termination_reason per_periods_of_placement.leaving_reason%type := 'AGE_REACHED';
l_person_type_id per_person_types.person_type_id%type := 1120;
l_period_of_service_id per_periods_of_placement.period_of_service_id%type;
l_actual_termination_date per_periods_of_placement.actual_termination_date%type :=trunc(sysdate);
l_last_standard_process_date per_periods_of_placement.last_standard_process_date%type := trunc(sysdate+10);
l_object_version_number per_periods_of_placement.object_version_number%type;
l_start_date per_periods_of_placement.date_start%type;
l_notif_term_date date;

--- OUT variables
l_supervisor_warning boolean := false;
l_event_warning boolean := false;
l_interview_warning boolean := false;
l_review_warning boolean := false;
l_recruiter_warning boolean := false;
l_asg_future_changes_warning boolean := false;
l_entries_changed_warning varchar2(300);
l_pay_proposal_warning boolean := false;
l_dod_warning boolean := false;

--- DECLARE variables for hr_contingent_worker_api.final_process_placement
--- IN variables
l_final_process_date per_periods_of_service.final_process_date%type;

--- OUT variables
l_org_now_no_manager_warning boolean := false;
--
begin
--
begin
select pos.period_of_placement_id, pos.object_version_number, date_start
into l_period_of_service_id, l_object_version_number, l_start_date
from per_periods_of_placement pos
where pos.person_id = l_person_id;

exception
when others then
l_terminate_msg := 'Error while selecting cwk details : '||substr(sqlerrm,1,150);
raise l_le_terminate_cwk_exception;
end;
--
savepoint terminate_cwk_s1;
--
begin
/*
This API covers the first step in terminating a period of placement and
all current assignments for a cwk, identified by person_id and date_start.
You can use the API to set the actual termination date, the last standard
process date, the new assignment status and the new person type
*/
hr_contingent_worker_api.actual_termination_placement
(p_validate => false
,p_effective_date => l_effective_date
,p_person_id => l_person_id
,p_date_start => l_start_date
,p_person_type_id => l_person_type_id
,p_actual_termination_date => l_actual_termination_date
,p_termination_reason => l_termination_reason
--In/Out
,p_object_version_number => l_object_version_number
,p_last_standard_process_date => l_last_standard_process_date
--Out
,p_supervisor_warning => l_supervisor_warning
,p_event_warning => l_event_warning
,p_interview_warning => l_interview_warning
,p_review_warning => l_review_warning
,p_recruiter_warning => l_recruiter_warning
,p_asg_future_changes_warning => l_asg_future_changes_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_pay_proposal_warning => l_pay_proposal_warning
,p_dod_warning => l_dod_warning
);

if l_object_version_number is null then
l_terminate_cwk_flag := 'N';
l_terminate_msg := 'Warning validating API: hr_contingent_worker_api.actual_termination_placement';
raise l_le_terminate_cwk_exception;
end if;

l_terminate_cwk_flag := 'Y';
exception
when others then
l_terminate_msg := 'Error validating API: hr_contingent_worker_api.actual_termination_placement : '||substr(sqlerrm,1,150);
raise l_le_terminate_cwk_exception;
end; --hr_contingent_worker_api.actual_termination_placement

if l_terminate_cwk_flag = 'Y' then
begin
/*
This API covers the second step in terminating a period of placement and
all current assignments for an cwk. It updates the period of placement
details and date-effectively deletes all the contingent worker assignments
as of the final process date.
*/
hr_contingent_worker_api.final_process_placement (
p_validate => false
,p_person_id => l_person_id
,p_date_start => l_start_date
--In/Out
,p_object_version_number => l_object_version_number
,p_final_process_date => l_final_process_date
--Out
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_asg_future_changes_warning => l_asg_future_changes_warning
,p_entries_changed_warning => l_entries_changed_warning );
exception
when others then
l_terminate_msg := 'Error validating API: hr_contingent_worker_api.final_process_placement : '||substr(sqlerrm,1,150);
raise l_le_terminate_cwk_exception;
end; --hr_contingent_worker_api.final_process_placement

end if;
--
commit;
--
exception
when l_le_terminate_cwk_exception then
dbms_output.put_line(l_terminate_msg);
rollback to terminate_cwk_s1;
when others then
dbms_output.put_line('Terminate CWK. Error OTHERS while validating: '||sqlerrm);
rollback to terminate_cwk_s1;
end;
--

Terminate Employee - HRMS APIs

--
declare
--
--Common Variables
l_terminate_emp_flag varchar2(1) := 'N';
l_terminate_msg varchar2(600);
l_person_id number := 123456;
l_le_terminate_emp_exception exception;

--- DECLARE variables for HR_EX_EMPLOYEE_WORKER_API.actual_termination_emp
--- IN variables
l_effective_date date;
l_termination_reason per_periods_of_service.leaving_reason%type := 'AGE_REACHED';
l_person_type_id per_person_types.person_type_id%type := 1120;
l_period_of_service_id per_periods_of_service.period_of_service_id%type;
l_actual_termination_date per_periods_of_service.actual_termination_date%type :=trunc(sysdate);
l_last_standard_process_date per_periods_of_service.last_standard_process_date%type := trunc(sysdate+10);
l_object_version_number per_periods_of_service.object_version_number%type;
l_start_date per_periods_of_service.date_start%type;
l_notif_term_date date;

--- OUT variables
l_supervisor_warning boolean := false;
l_event_warning boolean := false;
l_interview_warning boolean := false;
l_review_warning boolean := false;
l_recruiter_warning boolean := false;
l_asg_future_changes_warning boolean := false;
l_entries_changed_warning varchar2(300);
l_pay_proposal_warning boolean := false;
l_dod_warning boolean := false;
l_alu_change_warning varchar2(300);

--- DECLARE variables for HR_EX_EMPLOYEE_WORKER_API.final_process_emp
--- IN variables
l_final_process_date per_periods_of_service.final_process_date%type;

--- OUT variables
l_org_now_no_manager_warning boolean := false;
l_f_asg_future_changes_warning boolean := false;
l_f_entries_changed_warning varchar2(300);
--
begin
--
begin
select pos.period_of_service_id, pos.object_version_number, date_start
into l_period_of_service_id, l_object_version_number, l_start_date
from per_periods_of_service pos
where pos.person_id = l_person_id;
exception
when others then
l_terminate_msg := 'Error while selecting employee details : '||substr(sqlerrm,1,150);
raise l_le_terminate_emp_exception;
end;
--
savepoint terminate_employee_s1;
--
begin
/*
* This API terminates an employee.
* This API converts a person of type Employee >to a person of type
* Ex-Employee. The person's period of service and any employee assignments are ended.
*/
hr_ex_employee_api.actual_termination_emp
(p_validate => false--l_validate
,p_effective_date => trunc(sysdate)
,p_period_of_service_id => l_period_of_service_id
,p_object_version_number => l_object_version_number
,p_actual_termination_date => l_actual_termination_date
,p_last_standard_process_date => l_last_standard_process_date
,p_person_type_id => l_person_type_id
,p_leaving_reason => l_termination_reason
--Out
,p_supervisor_warning => l_supervisor_warning
,p_event_warning => l_event_warning
,p_interview_warning => l_interview_warning
,p_review_warning => l_review_warning
,p_recruiter_warning => l_recruiter_warning
,p_asg_future_changes_warning => l_asg_future_changes_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_pay_proposal_warning => l_pay_proposal_warning
,p_dod_warning => l_dod_warning
,p_alu_change_warning => l_alu_change_warning
);

if l_object_version_number is null then
l_terminate_emp_flag := 'N';
l_terminate_msg := 'Warning validating API: hr_ex_employee_api.actual_termination_emp';
raise l_le_terminate_emp_exception;
end if;

l_terminate_emp_flag := 'Y';
exception
when others then
l_terminate_msg := 'Error validating API: hr_ex_employee_api.actual_termination_emp : '||substr(sqlerrm,1,150);
raise l_le_terminate_emp_exception;
end; -- hr_ex_employee_api.actual_termination_emp

if l_terminate_emp_flag = 'Y' then
begin

if l_start_date > trunc(sysdate) then
l_notif_term_date := l_start_date + 1;
else
l_notif_term_date := trunc(sysdate);
end if;

/*
* This API updates employee termination information.
* The ex-employee must exist in the relevant business group
*/
apps.hr_ex_employee_api.update_term_details_emp
(p_validate => false--l_validate
,p_effective_date => trunc(sysdate)
,p_period_of_service_id => l_period_of_service_id
,p_notified_termination_date => l_notif_term_date
,p_projected_termination_date => l_notif_term_date
--In/Out
,p_object_version_number => l_object_version_number
);
exception
when others then
l_terminate_msg := 'Error validating API: hr_ex_employee_api.update_term_details_emp : '||substr(sqlerrm,1,150);
l_terminate_emp_flag := 'N';
raise l_le_terminate_emp_exception;
end; --hr_ex_employee_api.update_term_details_emp

begin
/*
* This API set the final process date for a terminated employee.
* This API covers the second step in terminating a period of service and all
* current assignments for an employee. It updates the period of service
* details and date-effectively deletes all employee assignments as of the final process date.
*/
apps.hr_ex_employee_api.final_process_emp
(p_validate => false--l_validate
,p_period_of_service_id => l_period_of_service_id
--Out
,p_object_version_number => l_object_version_number
,p_final_process_date => l_final_process_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_asg_future_changes_warning => l_f_asg_future_changes_warning
,p_entries_changed_warning => l_f_entries_changed_warning
);
exception
when others then
l_terminate_msg := 'Error validating API: hr_ex_employee_api.final_process_emp : '||substr(sqlerrm,1,150);
raise l_le_terminate_emp_exception;
end; --hr_ex_employee_api.final_process_emp

end if;
--
commit;
--
exception
when l_le_terminate_emp_exception then
dbms_output.put_line(l_terminate_msg);
rollback to terminate_employee_s1;
when others then
dbms_output.put_line('Terminate Employee. Error OTHERS while validating: '||sqlerrm);
rollback to terminate_employee_s1;
end;
--

Tuesday, September 2, 2014

Assignment Extra Info Create/Update - HRMS APIs

--
declare
--
l_action varchar2(10) := 'CREATE';--'UPDATE'
--
--- DECLARE variables for hr_assignment_extra_info_api
--- IN variables
l_assignment_id number := 123456;
l_info_type per_assignment_extra_info.information_type%type := 'XXAK_COM_EXTRA';
l_aei_info_cat per_assignment_extra_info.aei_information_category%type := 'XXAK_COM_EXTRA';
l_aei_info1 per_assignment_extra_info.aei_information1%type := 'Bonus';
l_emp_max_bonus varchar2(30) := '16';
l_ass_extra_info_id number;
--- OUT variables
l_obj_version_number number;
--
begin
/*
* This API creates an Assignment Extra Information record for a given
* assignment.
* @param p_information_type Identifies the Assignment Extra Information Type.
* Must be active.
*/

if l_action = 'CREATE' then
--
l_ass_extra_info_id := null;
--
hr_assignment_extra_info_api.create_assignment_extra_info
( p_validate => false
, p_assignment_id => l_assignment_id
, p_information_type => l_info_type
, p_aei_information_category => l_aei_info_cat
, p_aei_information1 => l_aei_info1
, p_aei_information2 => l_emp_max_bonus
, p_aei_information3 => to_char(sysdate,'YYYY/MM/DD HH24:MI:SS')
-- Out
, p_assignment_extra_info_id => l_ass_extra_info_id
, p_object_version_number => l_obj_version_number
);

if l_obj_version_number is null then
dbms_output.put_line('Assignment Attribute creation failed '||sqlerrm);
rollback;
else
dbms_output.put_line('Assignment Attribute created');
commit;
end if;

else
--
l_obj_version_number := 3;
l_ass_extra_info_id := 345612;
--
hr_assignment_extra_info_api.update_assignment_extra_info
( p_validate => false
, p_assignment_extra_info_id => l_ass_extra_info_id
-- In / Out
, p_object_version_number => l_obj_version_number
-- In
, p_aei_information_category => l_aei_info_cat
, p_aei_information1 => l_aei_info1
, p_aei_information2 => l_emp_max_bonus
, p_aei_information3 => to_char(sysdate,'YYYY/MM/DD HH24:MI:SS')
);

if l_obj_version_number > 3 then
dbms_output.put_line('Assignment Attribute updated');
commit;
else
dbms_output.put_line('Assignment Attribute updation failed '||sqlerrm);
rollback;
end if;
end if;
--
exception
when others then
dbms_output.put_line('pqp_aat_api API failed with error :'||sqlerrm);
rollback;
end;
--

Assignment Attribute Create/Update - HRMS APIs

--
declare
--
l_action varchar2(10) := 'CREATE';--'UPDATE'
--
--- DECLARE variables for pqp_aat_api
--- IN variables
l_assignment_id number := 123456;
l_business_group_id number := 101;
l_contract_type pqp_assignment_attributes_f.contract_type%type := 'Contractual 37 hours';
--- OUT variables
l_attr_ass_id number;
l_effective_start_date date;
l_effective_end_date date;
l_obj_version_number number;
--
begin
/*
* This API is used to create assignment attributes. These attributes
* include contract type and details of work patterns that are mainly used by
* public sector. Note: even though there are columns for vehicle information,
* these are not used anymore as the new api's pqp_vehicle_repository_api and
* pqp_vehicle_allocation_api are used for vehicle repository and allocations
* respectively.
*/

if l_action = 'CREATE' then
--
l_attr_ass_id := null;
--
pqp_aat_api.create_assignment_attribute
( p_validate => false
, p_effective_date => trunc(sysdate)
, p_business_group_id => l_business_group_id
, p_assignment_id => l_assignment_id
, p_contract_type => l_contract_type
-- Out
, p_effective_start_date => l_effective_start_date
, p_effective_end_date => l_effective_end_date
, p_assignment_attribute_id => l_attr_ass_id
, p_object_version_number => l_obj_version_number
);

if l_obj_version_number is null then
dbms_output.put_line('Assignment Attribute creation failed '||sqlerrm);
rollback;
else
dbms_output.put_line('Assignment Attribute created');
commit;
end if;

else
--
l_obj_version_number := 3;
l_attr_ass_id := 345612;
--
pqp_aat_api.update_assignment_attribute
( p_validate => false
, p_effective_date => trunc(sysdate)
, p_datetrack_mode => 'CORRECTION'
, p_assignment_attribute_id => l_attr_ass_id
, p_business_group_id => l_business_group_id
, p_assignment_id => l_assignment_id
, p_contract_type => l_contract_type
-- Out
, p_effective_start_date => l_effective_start_date
, p_effective_end_date => l_effective_end_date
-- In / Out
, p_object_version_number => l_obj_version_number
);

if l_obj_version_number > 3 then
dbms_output.put_line('Assignment Attribute updated');
commit;
else
dbms_output.put_line('Assignment Attribute updation failed '||sqlerrm);
rollback;
end if;
end if;
--
exception
when others then
dbms_output.put_line('pqp_aat_api API failed with error :'||sqlerrm);
rollback;
end;
--

Cost Allocation Create/Update - HRMS APIs

--
declare
--
l_action varchar2(10) := 'CREATE';--'UPDATE'
--
--- DECLARE variables for PAY_COST_ALLOCATION_API
--- IN variables
l_assignment_id number := 123456;
l_business_group_id number := 101;
l_proportion pay_cost_allocations_f.proportion%type := 1;
l_cost_code varchar2(20) := '14';
l_company varchar2(20) := 'AK';
l_business varchar2(20) := 'GRT';
l_budget_code varchar2(20) := '99999';
l_account varchar2(20) := '0000';
--- OUT variables
l_combination_name varchar2(100);
l_cost_allocation_id number;
l_cost_effective_start_date date;
l_cost_effective_end_date date;
l_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%type;
l_cost_obj_version_number number;
--
begin
/*
* This API creates/updates cost allocations.
* Requires a valid assignment.
*/

if l_action = 'CREATE' then
--
l_cost_allocation_id := null;
--
pay_cost_allocation_api.create_cost_allocation (p_validate => false
,p_effective_date => trunc(sysdate)
,p_assignment_id => l_assignment_id
,p_proportion => l_proportion
,p_business_group_id => l_business_group_id
,p_segment1 => l_company
,p_segment2 => l_business
,p_segment3 => l_cost_code
,p_segment4 => l_budget_code
,p_segment5 => l_account
-- Out
,p_combination_name => l_combination_name
,p_cost_allocation_id => l_cost_allocation_id
,p_effective_start_date => l_cost_effective_start_date
,p_effective_end_date => l_cost_effective_end_date
,p_object_version_number => l_cost_obj_version_number
-- In / Out
,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
);

if l_cost_obj_version_number is null then
dbms_output.put_line('Cost Allocation creation failed '||sqlerrm);
rollback;
else
dbms_output.put_line('Cost Allocation created');
commit;
end if;

else
--
l_cost_obj_version_number := 3;
l_cost_allocation_id := 345612;
--
pay_cost_allocation_api.update_cost_allocation
( p_validate => false
, p_effective_date => trunc(sysdate)
, p_datetrack_update_mode => 'UPDATE'
, p_cost_allocation_id => l_cost_allocation_id
, p_proportion => l_proportion
, p_segment1 => l_company
, p_segment2 => l_business
, p_segment3 => l_cost_code
, p_segment4 => l_budget_code
, p_segment5 => l_account
-- In / Out
, p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
, p_object_version_number => l_cost_obj_version_number
-- Out
, p_effective_start_date => l_cost_effective_start_date
, p_effective_end_date => l_cost_effective_end_date
, p_combination_name => l_combination_name
);

if l_cost_obj_version_number > 3 then
dbms_output.put_line('Cost Allocation updated');
commit;
else
dbms_output.put_line('Cost Allocation updation failed '||sqlerrm);
rollback;
end if;
end if;
--
exception
when others then
dbms_output.put_line('pay_cost_allocation_api API failed with error :'||sqlerrm);
rollback;
end;
--

Monday, September 1, 2014

Salary Proposal Create/Update - HRMS APIs

--
declare
--
l_action varchar2(10) := 'CREATE';--'UPDATE'
--
l_business_group_id number := 101;
l_assignment_id number := 123456;
l_salary_reason varchar2(20) := 'PROM';
l_salary number := 46119;
l_end_date date := hr_general.end_of_time;
l_salary_status varchar2(10) := 'Y';
l_ee_id number;
l_pay_proposal_id per_pay_proposals.pay_proposal_id%type := 678546;

l_ovn_ppp number;
l_inv_next_sal_date_warning boolean;
l_proposed_salary_warning boolean;
l_approved_warning boolean;
l_payroll_warning boolean;
--
begin
/*#
* This API inserts/updates a salary proposal into the per_pay_proposals table.
* If the proposal is an approved one, this procedure will also insert into the
* pay_element_entries table to create a new salary proposal.
* @param p_proposal_reason The proposal reason. Valid values are defined by
* lookup type 'PROPOSAL_REASON'.
*/

if l_action = 'CREATE' then
--
l_pay_proposal_id := null;
--
hr_maintain_proposal_api.insert_salary_proposal
-- Out
( p_pay_proposal_id => l_pay_proposal_id
--
, p_assignment_id => l_assignment_id
, p_business_group_id => l_business_group_id
, p_change_date => trunc(sysdate)
, p_proposal_reason => l_salary_reason
, p_proposed_salary_n => l_salary
, p_date_to => l_end_date
-- Out
, p_object_version_number => l_ovn_ppp
--
, p_multiple_components => 'N'
, p_approved => l_salary_status
, p_validate => false
-- In / Out
, p_element_entry_id => l_ee_id
-- Out
, p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
, p_proposed_salary_warning => l_proposed_salary_warning
, p_approved_warning => l_approved_warning
, p_payroll_warning => l_payroll_warning
);

if l_ovn_ppp is null or (l_inv_next_sal_date_warning = true or l_proposed_salary_warning = true
or l_approved_warning = true or l_payroll_warning = true) then
dbms_output.put_line('Salary creation failed '||sqlerrm);
rollback;
else
dbms_output.put_line('Salary created');
commit;
end if;

else
--
l_ovn_ppp := 3;
--
hr_maintain_proposal_api.update_salary_proposal
( p_pay_proposal_id => l_pay_proposal_id
, p_change_date => trunc(sysdate)
, p_proposal_reason => l_salary_reason
, p_proposed_salary_n => l_salary
, p_date_to => l_end_date
-- In / Out
, p_object_version_number => l_ovn_ppp
--
, p_multiple_components => 'N'
, p_approved => l_salary_status
, p_validate => false
-- Out
, p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
, p_proposed_salary_warning => l_proposed_salary_warning
, p_approved_warning => l_approved_warning
, p_payroll_warning => l_payroll_warning
);

if l_ovn_ppp > 3 then
dbms_output.put_line('Salary updated');
commit;
elsif (l_inv_next_sal_date_warning = true or l_proposed_salary_warning = true
or l_approved_warning = true or l_payroll_warning = true) then
dbms_output.put_line('Salary updatetion failed '||sqlerrm);
rollback;
end if;
end if;
--
exception
when others then
dbms_output.put_line('hr_maintain_proposal_api API failed with error :'||sqlerrm);
rollback;
end;
--