HRMS Interface -Employee Creation and updation

Hi All,
this is basic to sink employee information between two instances....


CREATE OR REPLACE PROCEDURE APPS.XX_HRMS_INTF_PROC
(retcode VARCHAR2
, errbuff VARCHAR2)
AS l_emp_num VARCHAR2 (30);
l_person_id NUMBER;
l_assignment_id NUMBER;
l_per_object_version_number NUMBER;
l_asg_object_version_number NUMBER;
l_per_effective_start_date DATE;
l_per_effective_end_date DATE;
l_full_name VARCHAR2 (240);
l_per_comment_id NUMBER;
l_assignment_sequence NUMBER;
l_assignment_number VARCHAR2 (30);
l_name_combination_warning BOOLEAN;
l_assign_payroll_warning BOOLEAN;
l_orig_hire_warning BOOLEAN;
l_employee_number VARCHAR2 (20);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_comment_id NUMBER;
lv_rec_exixsts VARCHAR2 (1) := 'N';
l_last_std_process_date_out DATE;
l_supervisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warning BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (200);
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_alu_change_warning VARCHAR2 (200);
l_acutal_temination_date_ewgl timestamp;
l_business_group_id number:=101;
l_ser_object_version_number number;
l_last_run_date timestamp ;
l_service_id number;
CURSOR c_emp_cur_hrms(c_last_run_Date date) IS
SELECT * FROM xx_hrms_intf_v@uat_new
where last_update_date>=c_last_run_Date or creation_Date>=c_last_run_Date;
BEGIN
select max(actual_completion_date)
into l_last_run_date from FND_CONCURRENT_REQUESTS fcr, fnd_concurrent_programs fcp where fcr.concurrent_program_id=fcp.concurrent_program_id
and concurrent_program_name='XX_HRMS_INTF'
and fcr.STATUS_CODE='C';
fnd_file.PUT_LINE(fnd_file.log,'last run successful date:'l_last_run_date); --Creating New Employee
FOR emp_rec IN c_emp_cur_hrms(l_last_run_date) LOOP
fnd_file.put_line (fnd_file.LOG, 'HRMS employee NO:' emp_rec.employee_number);
BEGIN
SELECT 'Y' INTO lv_rec_exixsts
FROM per_all_people_f
WHERE employee_number = emp_rec.employee_number
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and business_group_id=l_business_group_id;
EXCEPTION
WHEN OTHERS THEN
lv_rec_exixsts := 'N';
END;
IF lv_rec_exixsts = 'N' THEN
hr_employee_api.create_employee
(p_hire_date => emp_rec.start_date ,
p_business_group_id => l_business_group_id,
p_last_name => emp_rec.last_name ,
p_first_name => emp_rec.first_name ,
p_title => emp_rec.title, p_sex => emp_rec.sex ,
p_employee_number => emp_rec.employee_number ,
p_date_of_birth => emp_rec.date_of_birth ,
p_person_id => l_person_id ,
p_assignment_id => l_assignment_id ,
p_per_object_version_number => l_per_object_version_number ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_full_name => l_full_name ,
p_per_comment_id => l_per_comment_id ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_name_combination_warning => l_name_combination_warning ,
p_assign_payroll_warning => l_assign_payroll_warning ,
p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'Creating New employee:' emp_rec.employee_number); ELSIF lv_rec_exixsts = 'Y' THEN
SELECT papf.person_id ,
papf.object_version_number ,
ppos.actual_termination_date ,
ppos.OBJECT_VERSION_NUMBER ,
ppos.PERIOD_OF_SERVICE_ID INTO l_person_id ,
l_per_object_version_number ,
l_acutal_temination_date_ewgl ,
l_ser_object_version_number ,
l_service_id
FROM per_all_people_f papf, per_periods_of_service ppos
WHERE employee_number = emp_rec.employee_number
AND papf.person_id = ppos.person_id
and papf.business_group_id=l_business_group_id
AND SYSDATE BETWEEN EFFECTIVE_START_dATE AND EFFECTIVE_END_DATE;
l_employee_number := emp_rec.employee_number;
fnd_file.put_line(fnd_file.log,'person_id:'l_person_id);
fnd_file.put_line(fnd_file.log,'Object Version Number:'l_per_object_version_number); fnd_file.put_line(fnd_file.log,'EWGL Termination date:'l_acutal_temination_date_ewgl); fnd_file.put_line(fnd_file.log,'Last Update date:'emp_rec.last_update_date); fnd_file.put_line(fnd_file.log,'HRMS Employee Actual Termination Date:'emp_rec.actual_termination_date); f
nd_file.put_line(fnd_file.log,'Service Object Version:'l_ser_object_version_number);
IF emp_rec.last_update_date>=l_last_run_date
AND l_acutal_temination_date_ewgl IS NULL and emp_rec.actual_termination_date is null THEN
hr_person_api.update_person (p_effective_date => emp_rec.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_employee_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_full_name => l_full_name
,p_last_name => emp_rec.last_name
,p_first_name => emp_rec.first_name
,p_date_of_birth => emp_rec.date_of_birth
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'UPDATING New employee:' emp_rec.employee_number);
END IF;
IF emp_rec.actual_termination_date IS NOT NULL and emp_rec.last_update_date>=l_last_run_date THEN
l_last_std_process_date_out := emp_rec.final_process_date; hr_ex_employee_api.actual_termination_emp
(p_effective_date => emp_rec.effective_start_date
,p_period_of_service_id => l_service_id
,p_object_version_number => l_ser_object_version_number
,p_actual_termination_date => emp_rec.actual_termination_date
,p_person_type_id=>null
,p_last_std_process_date_out => l_last_std_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 ,
p_alu_change_warning => l_alu_change_warning);
fnd_file.put_line (fnd_file.LOG, 'Terminating New employee:' emp_rec.employee_number);
END IF;
IF l_acutal_temination_date_ewgl IS NOT NULL
AND emp_rec.actual_termination_date IS NULL and emp_rec.last_update_date>=l_last_run_date THEN hr_employee_api.re_hire_ex_employee
(p_hire_date => emp_rec.effective_start_date ,
p_person_id => l_person_id ,
p_per_object_version_number => l_per_object_version_number ,
p_rehire_reason => ' ', p_assignment_id => l_assignment_id ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_assign_payroll_warning => l_assign_payroll_warning);
fnd_file.put_line (fnd_file.LOG, 'RE Hiring New employee:' emp_rec.employee_number); END IF; END IF; END LOOP;
commit;
END;

Copying a request Group Content to another request group

Hi All,
Recently i got a requirement to create request and attach all the standard reports of GL to these request groups...it seems a lot tedious task to do manually...so i developed this script to copy the request group content to other request groups..THis is very useful if you have to create multiple requestgroups and attach reports to all,....

declare
cursor z isselect b.CONCURRENT_PROGRAM_NAME PROGRAMNAME,c.APPLICATION_NAME APPNAME from FND_REQUEST_GROUP_UNITS a
,fnd_concurrent_programs b
,fnd_application_tl c
,FND_REQUEST_GROUPS d
where a.request_Group_id=d.request_Group_id and a.REQUEST_UNIT_ID=b.CONCURRENT_PROGRAM_ID
and b.APPLICATION_ID=c.APPLICATION_ID
and d.request_Group_name='GL Concurrent Program Group';
begin
FOR REC IN Z LOOP
FND_PROGRAM.ADD_TO_GROUP(program_short_name =>REC.PROGRAMNAME
,program_application=>REC.APPNAME
,request_group=>'XX_GL_MOCA'
, group_application=>'Custom Application');
END LOOP;
END;

Concurrent Manager-1

Hi All.
We all are using concurrent programs ..but might have never put much thought into concurrent manager details....
We know that there are concurrent managers running in background which will be processing the concurrent programs submitted.
Basically they are three managers
1.Internal Manager
2.Standard Manager
3.Conflict manager
First let us see few scenarios..
1.In a instance when ever we are submitting multiple request only few are running.. why?
2.I have some reports which need to be run only in month end .how should I design this??
3.If two modules are submitting programs..and one is submitting more..provison should be made to make others are also submit program and they are get processed.
One user of GL submitted 100 reports…which takes time.. at the same time user2 need a few of reports also processed…How to setup so that both can run their reports

For developer point of view the most concerned one should be standard manager and Defining New Manager
First look at standard manager options..then i will come to how to handle the above scenarios..


Let see what is cache size..it is the number of concurrent programs it will select and keep in the queue.that means even you submit a program with higer priority it will be queues after the cache sized program only…
Press the workshift button which will say when the concurrent manager will be running…
In this note the number of process, it will be decide how many concurren program will be running at a time..dont increase number substantially high it depends on lot of parameters
Like server size and number of nodes…check with you DBA before changing to very high value…


Press the specialization rules button to see the programs which need to be excluded or included in the concurrent manager queue..by default all the custom concurrent programs will be running under standard manager;….

Concurrent Program LOCKs..

Hi All,

Now a days i am developing a concurrent program...while developing in some scenarios the concurrent program is not completingso i want to terminate,make some corrections and resubmit my program..even i terminate, the lock on my object are not getting releasedso i need to find the locked session and terminate the session...i thought of getting the information of session which i am currently using from oracle applications GUI itself but no information of session is available in front end...then i figured out that oraclesession and oracle_process_id are two columns in fnd_concurrent requests.
query1:select concurrent_program_name,fcr.REQUESTED_START_DATE, oracle_process_id,oracle_Session_id from FND_CONCURRENT_REQUESTS fcr,fnd_concurrent_programs fcp,fnd_logins fl,fnd_user fuwhere fcr.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID and fcr.CONC_LOGIN_ID=fl.login_idand fl.user_id=fu.user_id--and fcr.request_id=441792and fu.user_name='MGANJAM'order by fl.start_time desc
query2select sql_text, sid, SERIAL# from v$session vs,v$sqltext vsql,applsys.fnd_concurrent_requests fcrwhere vs.sql_hash_value=vsql.hash_valueand fcr.ORACLE_SESSION_ID=vs.AUDSIDand fcr.request_id = '442686'
This query will give the the sql it is running and the SID and serial#
Select * from v$locked_objects
check where any lock exists for the session
then use the alter system kill session to kill the session and the lock will be release
Now you will be able to compile the object...

SQL Loader in Oracle Applications(Draft)

SQL Loader in Oracle Applications…

Step1:create a Sqlloader file.(the ctl file)

Step2:Create a executable with Execution method type as SQLLOADER

Step3:move .ctl file to executable application top bin folder
For example for GL it is GL_TOP/bin
Step4.Create a program for the executable with paramertes like file name..

Step6:Attach the concurrent program to a request group and the run the program..

The one point that comes in mind how to acess the concurrent program parameters in sqlloader..it is using %1


LOAD DATA
INFILE '%1'
BADFILE '%1.bad'
DISCARDFILE '%1.dis'
TRUNCATE
INTO TABLE GL_DOW_TRANS
(
VOTE POSITION(01:16) INTEGER EXTERNAL,
AMOUNT POSITION(18:30) INTEGER EXTERNAL,
PERIOD POSITION(35:38) INTEGER EXTERNAL
)

Copying Files using PLSQL

Hi all
Most of the time we have requirement of moving files….we normally use the Shell script to move the files…
It is not mandatory we need to use shell scripts.
There are plsql api’s which can move files from one location to other location..

I think every one is aware of UTL_FILE .even it has some limitations…like the directory strucuture used by it should be in the UTL_FILE_PATH in init.ora …or Directory object should exist for that path…

For copying the file..
Utl_File.Fcopy ( src_location => p_file_location
,src_filename => p_file_name
, dest_location => p_arch_location,
dest_filename => p_arch_file );

For removing the file..:

UTL_FILE.Fremove(p_file_location,lc_datafile_name);

We can use this package when the directories are fixed and only file movement should happen..if you want to create directories at run time
I think shell scripting is a better option…

FND_GLOBAL PACKAGE

Recently i come accross a requirement to capture the requestidof the concurrent with in the concurrent program session...Even i am aware of the FND_GLOBAL package...But it didnt strike'at first go.....so i am writing this article for people who are not even awarethat this package exists..
FND_GLOBAL package gives the most of the enviroment values
THis package should be used in database side only...use FND_PROFILE package to get these values in Forms.
1.FND_GLOBAL.USERID --Returns userid

2.FND_GLOBAL.APPS_INTIALIZEprocedure APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
This is used to set the values userid and responsibilityid for a session
3.FND_GLOBAL.LOGIN_ID -Gives login id
4.FND_GLOBAL.CONC_LOGIN_ID--Not sure how to use this
5.FND_GLOBAL.PROG_APPL_ID--Concurrent program application id
6.FND_GLOBAL.CONC_PROGRAM_ID--Concurrent program id
7.FND_GLOBAL.CONC_REQUEST_ID (Server)This will give the concurrent request id of the program which is calling the plsql package..

ln_Request_id:=FND_GLOBAL.CONC_REQUEST_ID;