create or replace PACKAGE BODY XXTEST_PER_ASSIGN_DETAILS_PKG AS
--
/* Title : Core HR - Employee Personal Details Report
* Program Name : XXTEST_PER_ASSIGN_DETAILS_PKG.pkb
* Created by :
* Creation Date :
* Called By :
* Dependency : None
* Frequency : None
* Related documents :
* Tables/views accessed:
*
*
* Change History Log
*=============================================================================================================+
* Date |Name |Ticket / Bug |Version |Remarks
*==============================================================================================================
***************************************************************************************************************
*/
---
--- LANGUAGE DETAILS
---
FUNCTION get_lan_det_info(p_person_id IN NUMBER
,p_employee_number IN VARCHAR2
,p_business_group_id IN NUMBER)
RETURN VARCHAR2
IS
cursor c1 is SELECT DISTINCT pac.segment1
,pac.segment2
,pac.segment3
,pac.segment4
from per_analysis_criteria pac,
per_person_analyses ppa,
fnd_id_flex_structures fifs,
per_all_people_f papf
where ppa.person_id(+) =p_person_id
AND pac.analysis_criteria_id(+) = ppa.analysis_criteria_id
AND pac.id_flex_num = fifs.id_flex_num(+)
AND nvl(fifs.id_flex_structure_code,'XXTEST_LANGUAGE_PROFICIENCY') = 'XXTEST_LANGUAGE_PROFICIENCY'
AND papf.employee_number=p_employee_number
AND papf.business_group_id=p_business_group_id;
l_speak varchar2(1000):='Speak :';
l_write varchar2(1000):='Write :';
l_read varchar2(1000):='Read :';
l_result VARCHAR2(10000) :='';
begin
for i in c1
loop
if i.segment2 <> 'No' then
l_speak:=l_speak||' '||i.segment1;
end if;
--
if i.segment3 <> 'No' then
l_read:=l_read||' '||i.segment1;
end if;
--
if i.segment4 <> 'No' then
l_write:=l_write||' '||i.segment1;
end if;
l_result:=l_speak||' :: '||l_read||' :: '||l_write;
end loop;
RETURN l_result;
Exception WHEN OTHERS THEN
RETURN NULL;
END;
-----
--Total Experience
-----
FUNCTION XXHCM_TOTAL_EXP(
P_Person_id IN NUMBER ,
P_DATE IN VARCHAR2
)
RETURN VARCHAR2 AS
l_pre_yr NUMBER :=0;
l_pre_mn NUMBER :=0;
l_curr_exp_mon NUMBER :=0;
l_pre_day NUMBER :=0;
L_DATE VARCHAR2(20);
l_tot_mon NUMBER :=0;
l_day_mon NUMBER :=0;
l_mon_yr NUMBER :=0;
L_MONTHS NUMBER :=0;
l_exp varchar2(100);
l_exp_yr NUMBER:=0;
BEGIN
L_DATE:=TO_CHAR(FND_CONC_DATE.STRING_TO_DATE(P_DATE),'DD-Mon-RRRR');
select nvl(sum(ppe.period_years),0),
nvl(sum(ppe.period_months),0),
nvl(sum(ppe.period_days),0)
INTO l_pre_yr,
l_pre_mn,
l_pre_day
from per_previous_employers ppe
where ppe.person_id = P_Person_id;
dbms_output.PUT_LINE('Prev Years' ||' '||l_pre_yr);
dbms_output.PUT_LINE('Prev Months' ||' '||l_pre_mn);
dbms_output.PUT_LINE('Prev Days' ||' '||l_pre_day);
SELECT ROUND(months_between(TO_DATE(L_DATE), original_date_of_hire))
INTO l_curr_exp_mon
FROM per_people_f
WHERE PERSON_ID=P_Person_id
AND TO_DATE(L_DATE) BETWEEN effective_start_date AND effective_end_date;
dbms_output.PUT_LINE('PLG EXP'||' '||l_curr_exp_mon);
IF NVL(l_pre_day,0) >=30 THEN
l_day_mon := TRUNC(l_pre_day/30);
-- select mod(sum(ppe.period_days),30) INTO l_pre_day
-- from per_previous_employers ppe
-- where ppe.person_id = P_Person_id;
END IF;
dbms_output.PUT_LINE('Day Mon'||' '||l_day_mon||l_curr_exp_mon);
dbms_output.PUT_LINE('1'||' '||l_day_mon);
dbms_output.PUT_LINE('2'||' '||l_curr_exp_mon);
dbms_output.PUT_LINE('3'||' '||l_pre_mn);
l_tot_mon :=l_day_mon + l_curr_exp_mon + NVL(l_pre_mn,0);
dbms_output.PUT_LINE('Total Month'||' '||l_tot_mon);
IF l_tot_mon >=12 THEN
l_mon_yr :=TRUNC(l_tot_mon/12);
SELECT MOD(l_tot_mon,12)INTO l_tot_mon FROM DUAL;
dbms_output.PUT_LINE('MOD Months'||' '||L_MONTHS);
END IF;
dbms_output.PUT_LINE('Months converted to years'||' '||l_mon_yr);
dbms_output.PUT_LINE('Months not converted to years'||' '||L_MONTHS);
l_exp_yr:=NVL(l_pre_yr,0) + l_mon_yr;
l_exp:=TO_CHAR(l_exp_yr)||'.'||TO_CHAR(l_tot_mon);
RETURN l_exp;
-- RETURN(TO_CHAR(L_YEAR)||'.'||TO_CHAR(L_MONTHS));
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ' || SQLERRM);
dbms_output.PUT_LINE('eRROR');
END XXHCM_TOTAL_EXP;
/*
---
--Function XXHCM_TOTAL_EXP is used to fetch the (Actaul Prev Exp +Current Exp)
---
FUNCTION XXHCM_TOTAL_EXP(
P_Person_id IN NUMBER ,
P_DATE IN VARCHAR2
)
RETURN VARCHAR2 AS
L_YEAR NUMBER :=0;
L_MONTHS NUMBER :=0;
L_MONTHS1 NUMBER :=0;
L_DAY NUMBER :=0;
L_DATE VARCHAR2(20);
BEGIN
L_DATE:=TO_CHAR(FND_CONC_DATE.STRING_TO_DATE(P_DATE),'DD-Mon-RRRR');
select sum(ppe.period_years) INTO L_YEAR
from per_previous_employers ppe where ppe.person_id = P_Person_id;
-- fnd_file.PUT_LINE(fnd_file.log, 'YEARS ' || L_YEAR );
select sum(ppe.period_months) INTO L_MONTHS
from per_previous_employers ppe where ppe.person_id = P_Person_id;
-- fnd_file.PUT_LINE(fnd_file.log, 'MONTHS ' || L_MONTHS );
select sum(ppe.period_days) INTO L_DAY
from per_previous_employers ppe where ppe.person_id = P_Person_id;
-- fnd_file.PUT_LINE(fnd_file.log, 'DAYS ' || L_DAY );
SELECT ROUND(months_between(TO_DATE(L_DATE), original_date_of_hire)) INTO L_MONTHS1
FROM per_people_f WHERE PERSON_ID=P_Person_id
AND TO_DATE(L_DATE) BETWEEN effective_start_date AND effective_end_date;
--fnd_file.PUT_LINE(fnd_file.log, 'PRESENT ' || L_MONTHS1 );
L_MONTHS :=NVL(L_MONTHS,0)+nvl(L_MONTHS1,0);
IF L_MONTHS >=12 THEN
L_YEAR := NVL(L_YEAR,0) + TRUNC(L_MONTHS/12);
SELECT MOD(L_MONTHS,12)INTO L_MONTHS FROM DUAL;
END IF;
IF nvl(L_DAY,0) >=30 THEN
L_MONTHS := NVL(L_MONTHS,0) + TRUNC(L_DAY/30);
select mod(sum(ppe.period_days),30) INTO L_DAY
from per_previous_employers ppe
where ppe.person_id = P_Person_id;
END IF;
-- fnd_file.PUT_LINE(fnd_file.log, 'RETURN ' || TO_CHAR(L_YEAR)||'.'||TO_CHAR(L_MONTHS) );
RETURN(TO_CHAR(nvl(L_YEAR,0))||'.'||TO_CHAR(L_MONTHS));
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END XXHCM_TOTAL_EXP; */
----
--FUNCTION TO CALCULATE CURRENT EXP
---
FUNCTION XXHCM_EXP_CURRENT
( P_PERSON_ID IN NUMBER
, P_DATE IN VARCHAR2
)
RETURN VARCHAR2 AS
L_YEAR NUMBER:=0;
L_MONTHS NUMBER :=0;
L_DATE VARCHAR2(50);
BEGIN
L_DATE:=TO_CHAR(FND_CONC_DATE.STRING_TO_DATE(P_DATE),'DD-Mon-RRRR');
SELECT ROUND(months_between(TO_DATE(L_DATE), original_date_of_hire)) INTO L_MONTHS
FROM per_people_f WHERE PERSON_ID=P_Person_id
AND TO_DATE(L_DATE) BETWEEN effective_start_date AND effective_end_date;
IF L_MONTHS >=12 THEN
L_YEAR := nvl(L_YEAR,0) + TRUNC(L_MONTHS/12);
SELECT MOD(L_MONTHS,12)INTO L_MONTHS FROM DUAL;
END IF;
RETURN(TO_CHAR(L_YEAR)||'.'||TO_CHAR(L_MONTHS));
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END XXHCM_EXP_CURRENT;
-- --------------------------------------
-- PROCEDURE XXTEST_PER_ASN_DET_IND_PROC
-- --------------------------------------
PROCEDURE XXTEST_PER_ASN_DET_IND_PROC(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
P_EFFECTIVE_START_DATE IN VARCHAR2,
P_ORGANIZATION_ID NUMBER,
P_ROLL_UP VARCHAR2,
p_location_id NUMBER,
p_job_id NUMBER,
p_grade_id NUMBER,
p_user_person_type VARCHAR2,
P_EMPLOYEE_NUMBER VARCHAR2,
p_ass_attribute1 varchar2,
p_NATIONALITY VARCHAR2,
p_sex VARCHAR2,
p_DOJ DATE,
P_PAN VARCHAR2,
P_PAN_Applied_for VARCHAR2,
p_manager_number VARCHAR2,
p_Passport VARCHAR2) AS
l_Passport varchar2(20);
CURSOR EMPLOYEE_DETAILS IS
SELECT distinct papf.effective_start_date Effect_Start_Date
,papf.employee_number Employee_Number
,papf.person_id PERSON_ID
,papf.attribute1 Previous_Employee_Number
,papf.full_name Full_Name
,papf.original_date_of_hire DOJ
,hr_general.decode_lookup('SEX',papf.sex) Gender
,papf.date_of_birth Birth_Date
,Round((months_between(TRUNC(sysdate), papf.date_of_birth) / 12),1) Age
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',papf.country_of_birth) Country_of_Birth
,hr_general.decode_lookup('MAR_STATUS',papf.Marital_Status) Marital_Status
,hr_general.decode_lookup('NATIONALITY',papf.NATIONALITY) Nationality
,hr_general.decode_lookup('IN_RESIDENTIAL_STATUS',papf.per_information7) Residential_Status
,hr_general.decode_lookup('REGISTERED_DISABLED',papf.registered_disabled_flag) Registered_Disabled
,papf.blood_type Blood_Type
,papf.per_information14 PAN_Reference_Number
,papf.per_information4 PAN
-- ,XXTEST_personal_details_pkg.f_XXTEST_have_pan(papf.person_id) "Have PAN"
,hr_general.decode_lookup('YES_NO',papf.per_information5) PAN_Applied_For
,papf.per_information8 Provident_Fund_Number
,papf.per_information9 ESI_Number
,papf.per_information10 Superannuation_Number
,papf.per_information11 Group_Insurance_Number
,papf.per_information12 Gratutity_Number
,papf.per_information13 Pension_Fund_Number
,XXTEST_personal_details_pkg.f_XXTEST_addresses(papf.person_id) Address
,papf.email_address Email
,XXTEST_personal_details_pkg.f_XXTEST_mobile_number(papf.person_id) Mobile_Number
,XXTEST_personal_details_pkg.f_XXTEST_emergency_number(papf.person_id) Emergency_Number
,pq.name Highest_Qualification
,pq.attribute1 Type_Specialisation
,pq.grade_attained Grade
,pq.establishment University
,DECODE(pea.full_time,'Y' , 'Full Time' , 'N' , 'Part Time' , ' ') Full_Time_Part_Time
,pq.end_date year_of_completion
,pe.name School_College
,ppe.employer_name Employer
,ppe.employer_address Emp_Address
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',ppe.employer_country) Country
,hr_general.decode_lookup('PREV_EMP_TYPE',ppe.employer_type) Type
,hr_general.decode_lookup('PREV_EMP_SUBTYPE',ppe.employer_subtype) Sub_Type
,ppe.start_date Start_Date
,ppe.end_date End_Date
,ppj.job_name Last_Designation
,ROUND(MONTHS_BETWEEN(ppe.end_date,ppe.start_date) /12,1) Total_Experience
,ROUND(months_between(sysdate, papf.original_date_of_hire)/12,1) Total_Exp_PLG
,XXTEST_personal_details_pkg.f_XXTEST_have_passport_IN(papf.person_id) Have_Passport
,ppei.pei_information2 Passport_Number
,TO_CHAR(TO_DATE(TO_DATE(ppei.pei_information4 ,'rrrr/mm/dd hh24:mi:ss'),'DD-Mon-RRRR'),'DD-Mon-YYYY') Passport_Issue_Date
,TO_CHAR(TO_DATE(TO_DATE(ppei.pei_information5 ,'rrrr/mm/dd hh24:mi:ss'),'DD-Mon-RRRR'),'DD-Mon-YYYY') Passport_Expiry_Date
,DECODE(ppei.pei_information6,'Y' , 'Yes' , 'N' , 'No' , ppei.pei_information6) ECNR_Required
,ppei.pei_information3 Issue_Place
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',ppei.pei_information7) Passport_Issuing_Country
,XXTEST_personal_details_pkg.f_XXTEST_driving_details(papf.person_id,papf.employee_number,papf.business_group_id) DRIVING_LICENCE_DETAILS
,XXTEST_personal_details_pkg.f_XXTEST_work_stn(papf.person_id,papf.employee_number,papf.business_group_id) WORK_STATION_DETAILS
,get_lan_det_info(papf.person_id,papf.employee_number,papf.business_group_id) Language_Details
,NVL(XXTEST_HR_GENERAL_PKG.get_organization_name(paaf.organization_id,'Entity') ,XXTEST_HR_GENERAL_PKG.get_organization_name(paaf.organization_id,'Business Group')) Entity
,haou.name Organization
,haou.organization_type Organization_Type
,hla.location_code Location
,pj.name Job
,pg.name Assignment_Grade
,hr_general.decode_lookup('EMPLOYEE_CATG',paaf.employee_category) Employee_Category
,papf2.full_name Manager
,papf2.employee_number Manager_Number
,XXTEST_personal_details_pkg.f_XXTEST_bgc(papf.person_id,papf.employee_number,papf.business_group_id) BGC_Details
,ppt.user_person_type person_type
,hr_general.decode_lookup('XXTEST_ROLE',paaf.ass_attribute1) Employee_Role
,XXTEST_personal_details_pkg.f_XXTEST_full_name(paaf.ASS_ATTRIBUTE3) Admin_Manager
,XXTEST_personal_details_pkg.f_XXTEST_emp_number(paaf.ASS_ATTRIBUTE3) Admin_Manager_Number
FROM per_all_people_f papf
,per_qualifications_v pq
-- ,per_qualification_types pqt
,per_establishment_attendances pea
,per_previous_employers ppe
,per_previous_jobs ppj
,per_all_assignments_f paaf
,hr_locations_all hla
,per_establishments pe
,per_jobs pj
,per_grades pg
,per_all_people_f papf2
,hr_organization_units_v haou
,per_person_type_usages_f pptuf
,per_person_types ppt
,per_people_extra_info ppei
WHERE paaf.person_id = papf.person_id
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type = 'E'
AND papf.current_employee_flag = 'Y'
AND pj.job_id(+) = paaf.job_id
AND pg.grade_id(+) = paaf.grade_id
AND haou.organization_id = paaf.organization_id
AND (paaf.location_id = hla.location_id(+)
AND nvl(hla.business_group_id,papf.business_group_id) = papf.business_group_id)
AND papf.person_id = pptuf.person_id
AND pptuf.person_type_id = ppt.person_type_id
AND ppt.system_person_type IN('EMP')
AND ppt.user_person_type IN ('Contract','Permanent')
AND papf2.person_id(+) = paaf.supervisor_id
AND ppei.information_type(+) = 'IN_PASSPORT_DETAILS'
AND ppei.person_id(+) = papf.person_id
AND ppe.person_id(+) = papf.person_id
AND ppe.business_group_id(+) = papf.business_group_id
AND ppj.previous_employer_id(+) = ppe.previous_employer_id
AND PAPF.PERSON_ID = PQ.PERSON_ID(+)
AND pq.attendance_id = pea.attendance_id(+)
AND PQ.PERSON_ID = PEA.PERSON_ID(+)
AND PEA.ESTABLISHMENT_ID = PE.ESTABLISHMENT_ID(+)
/*AND papf.person_id = pq.person_id(+)
AND papf.business_group_id = pq.business_group_id(+)
AND pq.person_id = pea.person_id(+)
AND pq.business_group_id = pea.business_group_id(+)
AND pq.attendance_id = pea.attendance_id(+)
AND pea.establishment_id = pe.establishment_id(+)
-- AND pq.qualification_type_id = pqt.qualification_type_id(+)*/
AND (nvl(pq.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN (SELECT MAX(nvl(pq1.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_qualifications_v pq1
WHERE pq1.person_id = papf.person_id
AND pq1.business_group_id = papf.business_group_id)
OR(pq.person_id IS NULL AND pq.end_date IS NULL))
AND (nvl(ppe.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN (SELECT MAX(nvl(ppe1.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_previous_employers ppe1
WHERE ppe1.person_id = papf.person_id
AND ppe1.business_group_id = papf.business_group_id)
OR(ppe.person_id IS NULL AND ppe.end_date IS NULL))
AND (nvl(pea.attended_end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN (SELECT MAX(nvl(pea1.attended_end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_establishment_attendances pea1
WHERE pea1.person_id = pq.person_id
AND pea1.business_group_id = pq.business_group_id)
OR(pea.person_id IS NULL AND pea.attended_end_date IS NULL))
AND TRUNC(SYSDATE) between papf.effective_start_date and papf.effective_end_date
AND FND_CONC_DATE.STRING_TO_DATE(P_EFFECTIVE_START_DATE) between paaf.effective_start_date and paaf.effective_end_date
AND TRUNC(SYSDATE) between papf2.effective_start_date(+) and papf2.effective_end_date(+)
AND TRUNC(SYSDATE) between PPTUF.effective_start_date and PPTUF.effective_end_date
AND ((PAAF.ORGANIZATION_ID IN (SELECT OSE.ORGANIZATION_ID_CHILD
FROM PER_ORG_STRUCTURE_ELEMENTS_V OSE
WHERE P_ROLL_UP = 'Y'
AND ORG_STRUCTURE_VERSION_ID = 61
START WITH OSE.ORGANIZATION_ID_PARENT = P_ORGANIZATION_ID
CONNECT BY OSE.ORGANIZATION_ID_PARENT = PRIOR OSE.ORGANIZATION_ID_CHILD ))
OR (PAAF.ORGANIZATION_ID =P_ORGANIZATION_ID)
)
AND nvl(paaf.location_id,-1)=nvl(p_location_id,nvl(paaf.location_id,-1))
AND nvl(paaf.job_id,-1)=nvl(p_job_id,nvl(paaf.job_id,-1))
AND nvl(paaf.grade_id,-1)=nvl(p_grade_id,nvl(paaf.grade_id,-1))
AND (ppt.user_person_type =nvl(p_user_person_type,ppt.user_person_type )
AND nvl(p_user_person_type,ppt.user_person_type ) IN ('Contract','Permanent'))
AND nvl(PAPF.EMPLOYEE_NUMBER,-1)=NVL(P_EMPLOYEE_NUMBER,nvl(PAPF.EMPLOYEE_NUMBER,-1))
AND nvl(paaf.ass_attribute1,-1)=NVL(p_ass_attribute1,nvl(paaf.ass_attribute1,-1))
--and paaf.assignment_status_type_id =1
AND nvl(papf.NATIONALITY,-1)=nvl(p_NATIONALITY,nvl(papf.NATIONALITY,-1))
and nvl(upper(hr_general.decode_lookup('SEX',papf.sex)),-1)=nvl(p_sex,nvl(Upper(hr_general.decode_lookup('SEX',papf.sex)),-1))
AND papf.original_date_of_hire=nvl(p_DOJ,papf.original_date_of_hire)
AND XXTEST_personal_details_pkg.f_XXTEST_have_pan(papf.person_id)=nvl(P_PAN,XXTEST_personal_details_pkg.f_XXTEST_have_pan(papf.person_id))
and nvl(hr_general.decode_lookup('YES_NO',papf.per_information5),-1)=nvl(nvl(P_PAN_Applied_for,hr_general.decode_lookup('YES_NO',papf.per_information5)),-1)
AND nvl(papf2.employee_number,-1)=nvl(p_manager_number,nvl(papf2.employee_number,-1))
and upper(XXTEST_personal_details_pkg.f_XXTEST_have_passport_IN(papf.person_id))=nvl(l_Passport,upper(XXTEST_personal_details_pkg.f_XXTEST_have_passport_IN(papf.person_id)))
AND ( PAPF.EMPLOYEE_NUMBER LIKE 'P%'
OR PAPF.EMPLOYEE_NUMBER LIKE 'C%' )
order by papf.employee_number;
-------------------------------------------------------------------------------------------------------------------------
cursor highest_qualification(p_person_id number,p_business_group_id number) is
select
pqt.name Highest_Qualification
,pq.attribute1 Type_Specialisation
,pq.grade_attained Grade
,pe.name University
,DECODE(pea.full_time,'Y' , 'Full Time' , 'N' , 'Part Time' , pea.full_time) Full_Time_Part_Time
,pq.end_date year_of_completion
,pe.name "School/College" --------------
from per_qualifications pq
,per_qualification_types pqt
,per_establishment_attendances pea
,per_establishments pe
where
pq.person_id = p_person_id --13329
AND pq.business_group_id = nvl(p_business_group_id,pq.business_group_id)
AND pq.person_id = pea.person_id(+)
AND pq.business_group_id = pea.business_group_id(+)
AND pq.attendance_id = pea.attendance_id(+)
AND pea.establishment_id = pe.establishment_id(+)
AND pq.qualification_type_id = pqt.qualification_type_id(+)
AND (nvl(pq.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN (SELECT MAX(nvl(pq1.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_qualifications pq1
WHERE pq1.person_id = p_person_id
AND pq1.business_group_id = nvl(p_business_group_id,pq1.business_group_id)
)
OR(pq.person_id IS NULL AND pq.end_date IS NULL))
AND (nvl(pea.attended_end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN ( SELECT MAX(nvl(pea1.attended_end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_establishment_attendances pea1
WHERE pea1.person_id = pq.person_id
AND pea1.business_group_id = pq.business_group_id
)
OR(pea.person_id IS NULL AND pea.attended_end_date IS NULL));
---------------------------------------------------------------------------------------------------------------------------
cursor previous_employment(p_person_id number,p_business_group_id number) is
select ppe.employer_name Employer
,ppe.employer_address Emp_Address
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',ppe.employer_country) Country
,hr_general.decode_lookup('PREV_EMP_TYPE',ppe.employer_type) Type
,hr_general.decode_lookup('PREV_EMP_SUBTYPE',ppe.employer_subtype) Sub_Type
,ppe.start_date Start_Date
,ppe.end_date End_Date
,ppj.job_name Last_Designation
,ROUND(MONTHS_BETWEEN(ppe.end_date,ppe.start_date) / 12,1) Total_Experience
from per_previous_employers ppe
,per_previous_jobs ppj
where ppe.person_id = p_person_id
AND ppe.business_group_id = nvl(p_business_group_id,ppe.business_group_id)
AND ppj.previous_employer_id(+) = ppe.previous_employer_id ;
-------------------------------------------------------------------------------------------------------------------------
cursor passport_details(p_person_id number) is
select ppei.pei_information2 Passport_Number
,TO_CHAR(TO_DATE(TO_DATE(ppei.pei_information4 ,'rrrr/mm/dd hh24:mi:ss'),'DD-Mon-RRRR'),'DD-Mon-YYYY') Passport_Issue_Date
,TO_CHAR(TO_DATE(TO_DATE(ppei.pei_information5 ,'rrrr/mm/dd hh24:mi:ss'),'DD-Mon-RRRR'),'DD-Mon-YYYY') Passport_Expiry_Date
,DECODE(ppei.pei_information6,'Y' , 'Yes' , 'N' , 'No' , ppei.pei_information6) ECNR_Required
,ppei.pei_information3 Issue_Place
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',ppei.pei_information7) Passport_Issuing_Country
from per_people_extra_info ppei
where ppei.information_type(+) = 'IN_PASSPORT_DETAILS'
AND ppei.person_id = p_person_id ;
---------------------------------------------------------------------------------------------------------------------------
i number:=1;
L_EMP VARCHAR2(50);
L_EXP_TOT VARCHAR2(50);
L_EXP_PLG VARCHAR2(50);
l_date VARCHAR2(50);
BEGIN
l_date:=to_char(FND_CONC_DATE.STRING_TO_DATE(P_EFFECTIVE_START_DATE),'DD-Mon-YYYY');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<?xml version="1.0" ?>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<PERSON_ASSIGNMENT_DETAIL>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<P_DATE><![CDATA[' || l_date || ']]></P_DATE>');
fnd_file.PUT_LINE(fnd_file.log, p_Passport);
if p_Passport='N' then
l_Passport:=upper('No');
elsif p_Passport='Y' then
l_Passport:=upper('Yes');
end if;
FOR REEC_EMPLOYEE_DETAILS IN EMPLOYEE_DETAILS
LOOP
L_EXP_TOT := '';
L_EXP_PLG := '' ;
L_EXP_TOT := XXHCM_TOTAL_EXP(REEC_EMPLOYEE_DETAILS.PERSON_ID,l_date);
L_EXP_PLG := XXHCM_EXP_CURRENT(REEC_EMPLOYEE_DETAILS.PERSON_ID,l_date);
DBMS_OUTPUT.PUT_LINE(REEC_EMPLOYEE_DETAILS.Effect_Start_Date||' '||' '||REEC_EMPLOYEE_DETAILS.Employee_Number||' '||REEC_EMPLOYEE_DETAILS.Full_Name||' '||REEC_EMPLOYEE_DETAILS.DOJ);
fnd_file.PUT_LINE(fnd_file.log, 'Processing for '|| REEC_EMPLOYEE_DETAILS.Employee_Number);
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<G_PER_ASSG_DETAILS>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Effect_Start_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Effect_Start_Date || ']]></Effect_Start_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Employee_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Employee_Number || ']]></Employee_Number>');
L_EMP := NULL;
L_EMP := REEC_EMPLOYEE_DETAILS.Employee_Number;
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Previous_Employee_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Previous_Employee_Number || ']]></Previous_Employee_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Full_Name><![CDATA[' || REEC_EMPLOYEE_DETAILS.Full_Name || ']]></Full_Name>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<DOJ><![CDATA[' || REEC_EMPLOYEE_DETAILS.DOJ || ']]></DOJ>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Gender><![CDATA[' || REEC_EMPLOYEE_DETAILS.Gender || ']]></Gender>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Birth_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Birth_Date || ']]></Birth_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Age><![CDATA[' || REEC_EMPLOYEE_DETAILS.Age || ']]></Age>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Country_of_Birth><![CDATA[' || REEC_EMPLOYEE_DETAILS.Country_of_Birth || ']]></Country_of_Birth>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Marital_Status><![CDATA[' || REEC_EMPLOYEE_DETAILS.Marital_Status || ']]></Marital_Status>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Nationality><![CDATA[' || REEC_EMPLOYEE_DETAILS.Nationality || ']]></Nationality>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Residential_Status><![CDATA[' || REEC_EMPLOYEE_DETAILS.Residential_Status || ']]></Residential_Status>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Registered_Disabled><![CDATA[' || REEC_EMPLOYEE_DETAILS.Registered_Disabled || ']]></Registered_Disabled>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Blood_Type><![CDATA[' || REEC_EMPLOYEE_DETAILS.Blood_Type || ']]></Blood_Type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<PAN_Reference_Number><![CDATA['|| REEC_EMPLOYEE_DETAILS.PAN_Reference_Number || ']]></PAN_Reference_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<PAN><![CDATA[' || REEC_EMPLOYEE_DETAILS.PAN || ']]></PAN>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<PAN_Applied_For><![CDATA[' || REEC_EMPLOYEE_DETAILS.PAN_Applied_For || ']]></PAN_Applied_For>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Provident_Fund_Number><![CDATA['|| REEC_EMPLOYEE_DETAILS.Provident_Fund_Number || ']]></Provident_Fund_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<ESI_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.ESI_Number || ']]></ESI_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Superannuation_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Superannuation_Number || ']]></Superannuation_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Group_Insurance_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Group_Insurance_Number || ']]></Group_Insurance_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Gratutity_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Gratutity_Number || ']]></Gratutity_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Pension_Fund_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Pension_Fund_Number || ']]></Pension_Fund_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Address><![CDATA[' || REEC_EMPLOYEE_DETAILS.Address || ']]></Address>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Email><![CDATA[' || REEC_EMPLOYEE_DETAILS.Email || ']]></Email>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Mobile_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Mobile_Number || ']]></Mobile_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Emergency_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Emergency_Number || ']]></Emergency_Number>');
---Highest Qualification
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Highest_Qualification><![CDATA[' || REEC_EMPLOYEE_DETAILS.Highest_Qualification || ']]></Highest_Qualification>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Type_Specialisation><![CDATA[' || REEC_EMPLOYEE_DETAILS.Type_Specialisation || ']]></Type_Specialisation>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Grade><![CDATA[' || REEC_EMPLOYEE_DETAILS.Grade || ']]></Grade>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<University><![CDATA[' || REEC_EMPLOYEE_DETAILS.University || ']]></University>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Full_Time_Part_Time><![CDATA[' || REEC_EMPLOYEE_DETAILS.Full_Time_Part_Time || ']]></Full_Time_Part_Time>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<year_of_completion><![CDATA[' || REEC_EMPLOYEE_DETAILS.year_of_completion || ']]></year_of_completion>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<School_College ><![CDATA[' || REEC_EMPLOYEE_DETAILS.School_College || ']]></School_College >');
---------------------------------------------------------------------------------------------------------------------------------
----Previous Employment
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Employer><![CDATA[' || REEC_EMPLOYEE_DETAILS.Employer || ']]></Employer>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Emp_Address><![CDATA[' || REEC_EMPLOYEE_DETAILS.Emp_Address || ']]></Emp_Address>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Country><![CDATA[' || REEC_EMPLOYEE_DETAILS.Country || ']]></Country>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Type><![CDATA[' || REEC_EMPLOYEE_DETAILS.Type || ']]></Type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Sub_Type><![CDATA[' || REEC_EMPLOYEE_DETAILS.Sub_Type || ']]></Sub_Type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Start_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Start_Date || ']]></Start_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<End_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.End_Date || ']]></End_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Last_Designation><![CDATA[' || REEC_EMPLOYEE_DETAILS.Last_Designation || ']]></Last_Designation>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Total_Experience><![CDATA[' || L_EXP_TOT || ']]></Total_Experience>');
----------------------------------------------------------------------------------------------------------------------------------
---Passport Details
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Passport_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Passport_Number || ']]></Passport_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<ECNR_Required><![CDATA[' || REEC_EMPLOYEE_DETAILS.ECNR_Required || ']]></ECNR_Required>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Passport_Issue_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Passport_Issue_Date || ']]></Passport_Issue_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Passport_Expiry_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Passport_Expiry_Date || ']]></Passport_Expiry_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Issue_Place><![CDATA[' || REEC_EMPLOYEE_DETAILS.Issue_Place || ']]></Issue_Place>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Passport_Issuing_Country><![CDATA[' || REEC_EMPLOYEE_DETAILS.Passport_Issuing_Country || ']]></Passport_Issuing_Country>');
------------------------------------------------------------------------------------------------------------------------------------
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Total_Exp_PLG><![CDATA[' || L_EXP_PLG || ']]></Total_Exp_PLG>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Have_Passport><![CDATA[' || REEC_EMPLOYEE_DETAILS.Have_Passport || ']]></Have_Passport>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<DRIVING_LICENCE_DETAILS><![CDATA[' || REEC_EMPLOYEE_DETAILS.DRIVING_LICENCE_DETAILS || ']]></DRIVING_LICENCE_DETAILS>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<WORK_STATION_DETAILS><![CDATA[' || REEC_EMPLOYEE_DETAILS.WORK_STATION_DETAILS || ']]></WORK_STATION_DETAILS>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Language_Details><![CDATA[' || REEC_EMPLOYEE_DETAILS.Language_Details || ']]></Language_Details>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Entity><![CDATA[' || REEC_EMPLOYEE_DETAILS.Entity || ']]></Entity>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Organization><![CDATA[' || REEC_EMPLOYEE_DETAILS.Organization || ']]></Organization>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Organization_Type><![CDATA[' || REEC_EMPLOYEE_DETAILS.Organization_Type || ']]></Organization_Type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Location><![CDATA[' || REEC_EMPLOYEE_DETAILS.Location || ']]></Location>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Job><![CDATA[' || REEC_EMPLOYEE_DETAILS.Job || ']]></Job>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Assignment_Grade><![CDATA[' || REEC_EMPLOYEE_DETAILS.Assignment_Grade || ']]></Assignment_Grade>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Employee_Category><![CDATA[' || REEC_EMPLOYEE_DETAILS.Employee_Category || ']]></Employee_Category>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Manager><![CDATA[' || REEC_EMPLOYEE_DETAILS.Manager || ']]></Manager>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Manager_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Manager_Number || ']]></Manager_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<BGC_Details><![CDATA[' || REEC_EMPLOYEE_DETAILS.BGC_Details || ']]></BGC_Details>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<person_type><![CDATA[' || REEC_EMPLOYEE_DETAILS.person_type || ']]></person_type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Employee_Role><![CDATA[' || REEC_EMPLOYEE_DETAILS.Employee_Role || ']]></Employee_Role>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Admin_Manager><![CDATA[' || REEC_EMPLOYEE_DETAILS.Admin_Manager || ']]></Admin_Manager>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Admin_Manager_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Admin_Manager_Number || ']]></Admin_Manager_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</G_PER_ASSG_DETAILS>');
i:=i+1;
-- fnd_file.PUT_LINE(fnd_file.OUTPUT, '</G_PER_ASSG_DETAILS>');
DBMS_OUTPUT.PUT_LINE('-----------------------------------');
END LOOP;
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</PERSON_ASSIGNMENT_DETAIL>');
DBMS_OUTPUT.PUT_LINE(i);
EXCEPTION WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.log, 'FAILED FOR ' || L_EMP );
fnd_file.PUT_LINE(fnd_file.log, SUBSTR(SQLERRM,1,200));
END XXTEST_PER_ASN_DET_IND_PROC;
END XXTEST_PER_ASSIGN_DETAILS_PKG;
--
/* Title : Core HR - Employee Personal Details Report
* Program Name : XXTEST_PER_ASSIGN_DETAILS_PKG.pkb
* Created by :
* Creation Date :
* Called By :
* Dependency : None
* Frequency : None
* Related documents :
* Tables/views accessed:
*
*
* Change History Log
*=============================================================================================================+
* Date |Name |Ticket / Bug |Version |Remarks
*==============================================================================================================
***************************************************************************************************************
*/
---
--- LANGUAGE DETAILS
---
FUNCTION get_lan_det_info(p_person_id IN NUMBER
,p_employee_number IN VARCHAR2
,p_business_group_id IN NUMBER)
RETURN VARCHAR2
IS
cursor c1 is SELECT DISTINCT pac.segment1
,pac.segment2
,pac.segment3
,pac.segment4
from per_analysis_criteria pac,
per_person_analyses ppa,
fnd_id_flex_structures fifs,
per_all_people_f papf
where ppa.person_id(+) =p_person_id
AND pac.analysis_criteria_id(+) = ppa.analysis_criteria_id
AND pac.id_flex_num = fifs.id_flex_num(+)
AND nvl(fifs.id_flex_structure_code,'XXTEST_LANGUAGE_PROFICIENCY') = 'XXTEST_LANGUAGE_PROFICIENCY'
AND papf.employee_number=p_employee_number
AND papf.business_group_id=p_business_group_id;
l_speak varchar2(1000):='Speak :';
l_write varchar2(1000):='Write :';
l_read varchar2(1000):='Read :';
l_result VARCHAR2(10000) :='';
begin
for i in c1
loop
if i.segment2 <> 'No' then
l_speak:=l_speak||' '||i.segment1;
end if;
--
if i.segment3 <> 'No' then
l_read:=l_read||' '||i.segment1;
end if;
--
if i.segment4 <> 'No' then
l_write:=l_write||' '||i.segment1;
end if;
l_result:=l_speak||' :: '||l_read||' :: '||l_write;
end loop;
RETURN l_result;
Exception WHEN OTHERS THEN
RETURN NULL;
END;
-----
--Total Experience
-----
FUNCTION XXHCM_TOTAL_EXP(
P_Person_id IN NUMBER ,
P_DATE IN VARCHAR2
)
RETURN VARCHAR2 AS
l_pre_yr NUMBER :=0;
l_pre_mn NUMBER :=0;
l_curr_exp_mon NUMBER :=0;
l_pre_day NUMBER :=0;
L_DATE VARCHAR2(20);
l_tot_mon NUMBER :=0;
l_day_mon NUMBER :=0;
l_mon_yr NUMBER :=0;
L_MONTHS NUMBER :=0;
l_exp varchar2(100);
l_exp_yr NUMBER:=0;
BEGIN
L_DATE:=TO_CHAR(FND_CONC_DATE.STRING_TO_DATE(P_DATE),'DD-Mon-RRRR');
select nvl(sum(ppe.period_years),0),
nvl(sum(ppe.period_months),0),
nvl(sum(ppe.period_days),0)
INTO l_pre_yr,
l_pre_mn,
l_pre_day
from per_previous_employers ppe
where ppe.person_id = P_Person_id;
dbms_output.PUT_LINE('Prev Years' ||' '||l_pre_yr);
dbms_output.PUT_LINE('Prev Months' ||' '||l_pre_mn);
dbms_output.PUT_LINE('Prev Days' ||' '||l_pre_day);
SELECT ROUND(months_between(TO_DATE(L_DATE), original_date_of_hire))
INTO l_curr_exp_mon
FROM per_people_f
WHERE PERSON_ID=P_Person_id
AND TO_DATE(L_DATE) BETWEEN effective_start_date AND effective_end_date;
dbms_output.PUT_LINE('PLG EXP'||' '||l_curr_exp_mon);
IF NVL(l_pre_day,0) >=30 THEN
l_day_mon := TRUNC(l_pre_day/30);
-- select mod(sum(ppe.period_days),30) INTO l_pre_day
-- from per_previous_employers ppe
-- where ppe.person_id = P_Person_id;
END IF;
dbms_output.PUT_LINE('Day Mon'||' '||l_day_mon||l_curr_exp_mon);
dbms_output.PUT_LINE('1'||' '||l_day_mon);
dbms_output.PUT_LINE('2'||' '||l_curr_exp_mon);
dbms_output.PUT_LINE('3'||' '||l_pre_mn);
l_tot_mon :=l_day_mon + l_curr_exp_mon + NVL(l_pre_mn,0);
dbms_output.PUT_LINE('Total Month'||' '||l_tot_mon);
IF l_tot_mon >=12 THEN
l_mon_yr :=TRUNC(l_tot_mon/12);
SELECT MOD(l_tot_mon,12)INTO l_tot_mon FROM DUAL;
dbms_output.PUT_LINE('MOD Months'||' '||L_MONTHS);
END IF;
dbms_output.PUT_LINE('Months converted to years'||' '||l_mon_yr);
dbms_output.PUT_LINE('Months not converted to years'||' '||L_MONTHS);
l_exp_yr:=NVL(l_pre_yr,0) + l_mon_yr;
l_exp:=TO_CHAR(l_exp_yr)||'.'||TO_CHAR(l_tot_mon);
RETURN l_exp;
-- RETURN(TO_CHAR(L_YEAR)||'.'||TO_CHAR(L_MONTHS));
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ' || SQLERRM);
dbms_output.PUT_LINE('eRROR');
END XXHCM_TOTAL_EXP;
/*
---
--Function XXHCM_TOTAL_EXP is used to fetch the (Actaul Prev Exp +Current Exp)
---
FUNCTION XXHCM_TOTAL_EXP(
P_Person_id IN NUMBER ,
P_DATE IN VARCHAR2
)
RETURN VARCHAR2 AS
L_YEAR NUMBER :=0;
L_MONTHS NUMBER :=0;
L_MONTHS1 NUMBER :=0;
L_DAY NUMBER :=0;
L_DATE VARCHAR2(20);
BEGIN
L_DATE:=TO_CHAR(FND_CONC_DATE.STRING_TO_DATE(P_DATE),'DD-Mon-RRRR');
select sum(ppe.period_years) INTO L_YEAR
from per_previous_employers ppe where ppe.person_id = P_Person_id;
-- fnd_file.PUT_LINE(fnd_file.log, 'YEARS ' || L_YEAR );
select sum(ppe.period_months) INTO L_MONTHS
from per_previous_employers ppe where ppe.person_id = P_Person_id;
-- fnd_file.PUT_LINE(fnd_file.log, 'MONTHS ' || L_MONTHS );
select sum(ppe.period_days) INTO L_DAY
from per_previous_employers ppe where ppe.person_id = P_Person_id;
-- fnd_file.PUT_LINE(fnd_file.log, 'DAYS ' || L_DAY );
SELECT ROUND(months_between(TO_DATE(L_DATE), original_date_of_hire)) INTO L_MONTHS1
FROM per_people_f WHERE PERSON_ID=P_Person_id
AND TO_DATE(L_DATE) BETWEEN effective_start_date AND effective_end_date;
--fnd_file.PUT_LINE(fnd_file.log, 'PRESENT ' || L_MONTHS1 );
L_MONTHS :=NVL(L_MONTHS,0)+nvl(L_MONTHS1,0);
IF L_MONTHS >=12 THEN
L_YEAR := NVL(L_YEAR,0) + TRUNC(L_MONTHS/12);
SELECT MOD(L_MONTHS,12)INTO L_MONTHS FROM DUAL;
END IF;
IF nvl(L_DAY,0) >=30 THEN
L_MONTHS := NVL(L_MONTHS,0) + TRUNC(L_DAY/30);
select mod(sum(ppe.period_days),30) INTO L_DAY
from per_previous_employers ppe
where ppe.person_id = P_Person_id;
END IF;
-- fnd_file.PUT_LINE(fnd_file.log, 'RETURN ' || TO_CHAR(L_YEAR)||'.'||TO_CHAR(L_MONTHS) );
RETURN(TO_CHAR(nvl(L_YEAR,0))||'.'||TO_CHAR(L_MONTHS));
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END XXHCM_TOTAL_EXP; */
----
--FUNCTION TO CALCULATE CURRENT EXP
---
FUNCTION XXHCM_EXP_CURRENT
( P_PERSON_ID IN NUMBER
, P_DATE IN VARCHAR2
)
RETURN VARCHAR2 AS
L_YEAR NUMBER:=0;
L_MONTHS NUMBER :=0;
L_DATE VARCHAR2(50);
BEGIN
L_DATE:=TO_CHAR(FND_CONC_DATE.STRING_TO_DATE(P_DATE),'DD-Mon-RRRR');
SELECT ROUND(months_between(TO_DATE(L_DATE), original_date_of_hire)) INTO L_MONTHS
FROM per_people_f WHERE PERSON_ID=P_Person_id
AND TO_DATE(L_DATE) BETWEEN effective_start_date AND effective_end_date;
IF L_MONTHS >=12 THEN
L_YEAR := nvl(L_YEAR,0) + TRUNC(L_MONTHS/12);
SELECT MOD(L_MONTHS,12)INTO L_MONTHS FROM DUAL;
END IF;
RETURN(TO_CHAR(L_YEAR)||'.'||TO_CHAR(L_MONTHS));
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END XXHCM_EXP_CURRENT;
-- --------------------------------------
-- PROCEDURE XXTEST_PER_ASN_DET_IND_PROC
-- --------------------------------------
PROCEDURE XXTEST_PER_ASN_DET_IND_PROC(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
P_EFFECTIVE_START_DATE IN VARCHAR2,
P_ORGANIZATION_ID NUMBER,
P_ROLL_UP VARCHAR2,
p_location_id NUMBER,
p_job_id NUMBER,
p_grade_id NUMBER,
p_user_person_type VARCHAR2,
P_EMPLOYEE_NUMBER VARCHAR2,
p_ass_attribute1 varchar2,
p_NATIONALITY VARCHAR2,
p_sex VARCHAR2,
p_DOJ DATE,
P_PAN VARCHAR2,
P_PAN_Applied_for VARCHAR2,
p_manager_number VARCHAR2,
p_Passport VARCHAR2) AS
l_Passport varchar2(20);
CURSOR EMPLOYEE_DETAILS IS
SELECT distinct papf.effective_start_date Effect_Start_Date
,papf.employee_number Employee_Number
,papf.person_id PERSON_ID
,papf.attribute1 Previous_Employee_Number
,papf.full_name Full_Name
,papf.original_date_of_hire DOJ
,hr_general.decode_lookup('SEX',papf.sex) Gender
,papf.date_of_birth Birth_Date
,Round((months_between(TRUNC(sysdate), papf.date_of_birth) / 12),1) Age
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',papf.country_of_birth) Country_of_Birth
,hr_general.decode_lookup('MAR_STATUS',papf.Marital_Status) Marital_Status
,hr_general.decode_lookup('NATIONALITY',papf.NATIONALITY) Nationality
,hr_general.decode_lookup('IN_RESIDENTIAL_STATUS',papf.per_information7) Residential_Status
,hr_general.decode_lookup('REGISTERED_DISABLED',papf.registered_disabled_flag) Registered_Disabled
,papf.blood_type Blood_Type
,papf.per_information14 PAN_Reference_Number
,papf.per_information4 PAN
-- ,XXTEST_personal_details_pkg.f_XXTEST_have_pan(papf.person_id) "Have PAN"
,hr_general.decode_lookup('YES_NO',papf.per_information5) PAN_Applied_For
,papf.per_information8 Provident_Fund_Number
,papf.per_information9 ESI_Number
,papf.per_information10 Superannuation_Number
,papf.per_information11 Group_Insurance_Number
,papf.per_information12 Gratutity_Number
,papf.per_information13 Pension_Fund_Number
,XXTEST_personal_details_pkg.f_XXTEST_addresses(papf.person_id) Address
,papf.email_address Email
,XXTEST_personal_details_pkg.f_XXTEST_mobile_number(papf.person_id) Mobile_Number
,XXTEST_personal_details_pkg.f_XXTEST_emergency_number(papf.person_id) Emergency_Number
,pq.name Highest_Qualification
,pq.attribute1 Type_Specialisation
,pq.grade_attained Grade
,pq.establishment University
,DECODE(pea.full_time,'Y' , 'Full Time' , 'N' , 'Part Time' , ' ') Full_Time_Part_Time
,pq.end_date year_of_completion
,pe.name School_College
,ppe.employer_name Employer
,ppe.employer_address Emp_Address
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',ppe.employer_country) Country
,hr_general.decode_lookup('PREV_EMP_TYPE',ppe.employer_type) Type
,hr_general.decode_lookup('PREV_EMP_SUBTYPE',ppe.employer_subtype) Sub_Type
,ppe.start_date Start_Date
,ppe.end_date End_Date
,ppj.job_name Last_Designation
,ROUND(MONTHS_BETWEEN(ppe.end_date,ppe.start_date) /12,1) Total_Experience
,ROUND(months_between(sysdate, papf.original_date_of_hire)/12,1) Total_Exp_PLG
,XXTEST_personal_details_pkg.f_XXTEST_have_passport_IN(papf.person_id) Have_Passport
,ppei.pei_information2 Passport_Number
,TO_CHAR(TO_DATE(TO_DATE(ppei.pei_information4 ,'rrrr/mm/dd hh24:mi:ss'),'DD-Mon-RRRR'),'DD-Mon-YYYY') Passport_Issue_Date
,TO_CHAR(TO_DATE(TO_DATE(ppei.pei_information5 ,'rrrr/mm/dd hh24:mi:ss'),'DD-Mon-RRRR'),'DD-Mon-YYYY') Passport_Expiry_Date
,DECODE(ppei.pei_information6,'Y' , 'Yes' , 'N' , 'No' , ppei.pei_information6) ECNR_Required
,ppei.pei_information3 Issue_Place
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',ppei.pei_information7) Passport_Issuing_Country
,XXTEST_personal_details_pkg.f_XXTEST_driving_details(papf.person_id,papf.employee_number,papf.business_group_id) DRIVING_LICENCE_DETAILS
,XXTEST_personal_details_pkg.f_XXTEST_work_stn(papf.person_id,papf.employee_number,papf.business_group_id) WORK_STATION_DETAILS
,get_lan_det_info(papf.person_id,papf.employee_number,papf.business_group_id) Language_Details
,NVL(XXTEST_HR_GENERAL_PKG.get_organization_name(paaf.organization_id,'Entity') ,XXTEST_HR_GENERAL_PKG.get_organization_name(paaf.organization_id,'Business Group')) Entity
,haou.name Organization
,haou.organization_type Organization_Type
,hla.location_code Location
,pj.name Job
,pg.name Assignment_Grade
,hr_general.decode_lookup('EMPLOYEE_CATG',paaf.employee_category) Employee_Category
,papf2.full_name Manager
,papf2.employee_number Manager_Number
,XXTEST_personal_details_pkg.f_XXTEST_bgc(papf.person_id,papf.employee_number,papf.business_group_id) BGC_Details
,ppt.user_person_type person_type
,hr_general.decode_lookup('XXTEST_ROLE',paaf.ass_attribute1) Employee_Role
,XXTEST_personal_details_pkg.f_XXTEST_full_name(paaf.ASS_ATTRIBUTE3) Admin_Manager
,XXTEST_personal_details_pkg.f_XXTEST_emp_number(paaf.ASS_ATTRIBUTE3) Admin_Manager_Number
FROM per_all_people_f papf
,per_qualifications_v pq
-- ,per_qualification_types pqt
,per_establishment_attendances pea
,per_previous_employers ppe
,per_previous_jobs ppj
,per_all_assignments_f paaf
,hr_locations_all hla
,per_establishments pe
,per_jobs pj
,per_grades pg
,per_all_people_f papf2
,hr_organization_units_v haou
,per_person_type_usages_f pptuf
,per_person_types ppt
,per_people_extra_info ppei
WHERE paaf.person_id = papf.person_id
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type = 'E'
AND papf.current_employee_flag = 'Y'
AND pj.job_id(+) = paaf.job_id
AND pg.grade_id(+) = paaf.grade_id
AND haou.organization_id = paaf.organization_id
AND (paaf.location_id = hla.location_id(+)
AND nvl(hla.business_group_id,papf.business_group_id) = papf.business_group_id)
AND papf.person_id = pptuf.person_id
AND pptuf.person_type_id = ppt.person_type_id
AND ppt.system_person_type IN('EMP')
AND ppt.user_person_type IN ('Contract','Permanent')
AND papf2.person_id(+) = paaf.supervisor_id
AND ppei.information_type(+) = 'IN_PASSPORT_DETAILS'
AND ppei.person_id(+) = papf.person_id
AND ppe.person_id(+) = papf.person_id
AND ppe.business_group_id(+) = papf.business_group_id
AND ppj.previous_employer_id(+) = ppe.previous_employer_id
AND PAPF.PERSON_ID = PQ.PERSON_ID(+)
AND pq.attendance_id = pea.attendance_id(+)
AND PQ.PERSON_ID = PEA.PERSON_ID(+)
AND PEA.ESTABLISHMENT_ID = PE.ESTABLISHMENT_ID(+)
/*AND papf.person_id = pq.person_id(+)
AND papf.business_group_id = pq.business_group_id(+)
AND pq.person_id = pea.person_id(+)
AND pq.business_group_id = pea.business_group_id(+)
AND pq.attendance_id = pea.attendance_id(+)
AND pea.establishment_id = pe.establishment_id(+)
-- AND pq.qualification_type_id = pqt.qualification_type_id(+)*/
AND (nvl(pq.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN (SELECT MAX(nvl(pq1.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_qualifications_v pq1
WHERE pq1.person_id = papf.person_id
AND pq1.business_group_id = papf.business_group_id)
OR(pq.person_id IS NULL AND pq.end_date IS NULL))
AND (nvl(ppe.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN (SELECT MAX(nvl(ppe1.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_previous_employers ppe1
WHERE ppe1.person_id = papf.person_id
AND ppe1.business_group_id = papf.business_group_id)
OR(ppe.person_id IS NULL AND ppe.end_date IS NULL))
AND (nvl(pea.attended_end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN (SELECT MAX(nvl(pea1.attended_end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_establishment_attendances pea1
WHERE pea1.person_id = pq.person_id
AND pea1.business_group_id = pq.business_group_id)
OR(pea.person_id IS NULL AND pea.attended_end_date IS NULL))
AND TRUNC(SYSDATE) between papf.effective_start_date and papf.effective_end_date
AND FND_CONC_DATE.STRING_TO_DATE(P_EFFECTIVE_START_DATE) between paaf.effective_start_date and paaf.effective_end_date
AND TRUNC(SYSDATE) between papf2.effective_start_date(+) and papf2.effective_end_date(+)
AND TRUNC(SYSDATE) between PPTUF.effective_start_date and PPTUF.effective_end_date
AND ((PAAF.ORGANIZATION_ID IN (SELECT OSE.ORGANIZATION_ID_CHILD
FROM PER_ORG_STRUCTURE_ELEMENTS_V OSE
WHERE P_ROLL_UP = 'Y'
AND ORG_STRUCTURE_VERSION_ID = 61
START WITH OSE.ORGANIZATION_ID_PARENT = P_ORGANIZATION_ID
CONNECT BY OSE.ORGANIZATION_ID_PARENT = PRIOR OSE.ORGANIZATION_ID_CHILD ))
OR (PAAF.ORGANIZATION_ID =P_ORGANIZATION_ID)
)
AND nvl(paaf.location_id,-1)=nvl(p_location_id,nvl(paaf.location_id,-1))
AND nvl(paaf.job_id,-1)=nvl(p_job_id,nvl(paaf.job_id,-1))
AND nvl(paaf.grade_id,-1)=nvl(p_grade_id,nvl(paaf.grade_id,-1))
AND (ppt.user_person_type =nvl(p_user_person_type,ppt.user_person_type )
AND nvl(p_user_person_type,ppt.user_person_type ) IN ('Contract','Permanent'))
AND nvl(PAPF.EMPLOYEE_NUMBER,-1)=NVL(P_EMPLOYEE_NUMBER,nvl(PAPF.EMPLOYEE_NUMBER,-1))
AND nvl(paaf.ass_attribute1,-1)=NVL(p_ass_attribute1,nvl(paaf.ass_attribute1,-1))
--and paaf.assignment_status_type_id =1
AND nvl(papf.NATIONALITY,-1)=nvl(p_NATIONALITY,nvl(papf.NATIONALITY,-1))
and nvl(upper(hr_general.decode_lookup('SEX',papf.sex)),-1)=nvl(p_sex,nvl(Upper(hr_general.decode_lookup('SEX',papf.sex)),-1))
AND papf.original_date_of_hire=nvl(p_DOJ,papf.original_date_of_hire)
AND XXTEST_personal_details_pkg.f_XXTEST_have_pan(papf.person_id)=nvl(P_PAN,XXTEST_personal_details_pkg.f_XXTEST_have_pan(papf.person_id))
and nvl(hr_general.decode_lookup('YES_NO',papf.per_information5),-1)=nvl(nvl(P_PAN_Applied_for,hr_general.decode_lookup('YES_NO',papf.per_information5)),-1)
AND nvl(papf2.employee_number,-1)=nvl(p_manager_number,nvl(papf2.employee_number,-1))
and upper(XXTEST_personal_details_pkg.f_XXTEST_have_passport_IN(papf.person_id))=nvl(l_Passport,upper(XXTEST_personal_details_pkg.f_XXTEST_have_passport_IN(papf.person_id)))
AND ( PAPF.EMPLOYEE_NUMBER LIKE 'P%'
OR PAPF.EMPLOYEE_NUMBER LIKE 'C%' )
order by papf.employee_number;
-------------------------------------------------------------------------------------------------------------------------
cursor highest_qualification(p_person_id number,p_business_group_id number) is
select
pqt.name Highest_Qualification
,pq.attribute1 Type_Specialisation
,pq.grade_attained Grade
,pe.name University
,DECODE(pea.full_time,'Y' , 'Full Time' , 'N' , 'Part Time' , pea.full_time) Full_Time_Part_Time
,pq.end_date year_of_completion
,pe.name "School/College" --------------
from per_qualifications pq
,per_qualification_types pqt
,per_establishment_attendances pea
,per_establishments pe
where
pq.person_id = p_person_id --13329
AND pq.business_group_id = nvl(p_business_group_id,pq.business_group_id)
AND pq.person_id = pea.person_id(+)
AND pq.business_group_id = pea.business_group_id(+)
AND pq.attendance_id = pea.attendance_id(+)
AND pea.establishment_id = pe.establishment_id(+)
AND pq.qualification_type_id = pqt.qualification_type_id(+)
AND (nvl(pq.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN (SELECT MAX(nvl(pq1.end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_qualifications pq1
WHERE pq1.person_id = p_person_id
AND pq1.business_group_id = nvl(p_business_group_id,pq1.business_group_id)
)
OR(pq.person_id IS NULL AND pq.end_date IS NULL))
AND (nvl(pea.attended_end_date,to_date('01-Jan-0001','DD-Mon-YYYY')) IN ( SELECT MAX(nvl(pea1.attended_end_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
FROM per_establishment_attendances pea1
WHERE pea1.person_id = pq.person_id
AND pea1.business_group_id = pq.business_group_id
)
OR(pea.person_id IS NULL AND pea.attended_end_date IS NULL));
---------------------------------------------------------------------------------------------------------------------------
cursor previous_employment(p_person_id number,p_business_group_id number) is
select ppe.employer_name Employer
,ppe.employer_address Emp_Address
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',ppe.employer_country) Country
,hr_general.decode_lookup('PREV_EMP_TYPE',ppe.employer_type) Type
,hr_general.decode_lookup('PREV_EMP_SUBTYPE',ppe.employer_subtype) Sub_Type
,ppe.start_date Start_Date
,ppe.end_date End_Date
,ppj.job_name Last_Designation
,ROUND(MONTHS_BETWEEN(ppe.end_date,ppe.start_date) / 12,1) Total_Experience
from per_previous_employers ppe
,per_previous_jobs ppj
where ppe.person_id = p_person_id
AND ppe.business_group_id = nvl(p_business_group_id,ppe.business_group_id)
AND ppj.previous_employer_id(+) = ppe.previous_employer_id ;
-------------------------------------------------------------------------------------------------------------------------
cursor passport_details(p_person_id number) is
select ppei.pei_information2 Passport_Number
,TO_CHAR(TO_DATE(TO_DATE(ppei.pei_information4 ,'rrrr/mm/dd hh24:mi:ss'),'DD-Mon-RRRR'),'DD-Mon-YYYY') Passport_Issue_Date
,TO_CHAR(TO_DATE(TO_DATE(ppei.pei_information5 ,'rrrr/mm/dd hh24:mi:ss'),'DD-Mon-RRRR'),'DD-Mon-YYYY') Passport_Expiry_Date
,DECODE(ppei.pei_information6,'Y' , 'Yes' , 'N' , 'No' , ppei.pei_information6) ECNR_Required
,ppei.pei_information3 Issue_Place
,hr_general.decode_lookup('PER_US_COUNTRY_CODE',ppei.pei_information7) Passport_Issuing_Country
from per_people_extra_info ppei
where ppei.information_type(+) = 'IN_PASSPORT_DETAILS'
AND ppei.person_id = p_person_id ;
---------------------------------------------------------------------------------------------------------------------------
i number:=1;
L_EMP VARCHAR2(50);
L_EXP_TOT VARCHAR2(50);
L_EXP_PLG VARCHAR2(50);
l_date VARCHAR2(50);
BEGIN
l_date:=to_char(FND_CONC_DATE.STRING_TO_DATE(P_EFFECTIVE_START_DATE),'DD-Mon-YYYY');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<?xml version="1.0" ?>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<PERSON_ASSIGNMENT_DETAIL>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<P_DATE><![CDATA[' || l_date || ']]></P_DATE>');
fnd_file.PUT_LINE(fnd_file.log, p_Passport);
if p_Passport='N' then
l_Passport:=upper('No');
elsif p_Passport='Y' then
l_Passport:=upper('Yes');
end if;
FOR REEC_EMPLOYEE_DETAILS IN EMPLOYEE_DETAILS
LOOP
L_EXP_TOT := '';
L_EXP_PLG := '' ;
L_EXP_TOT := XXHCM_TOTAL_EXP(REEC_EMPLOYEE_DETAILS.PERSON_ID,l_date);
L_EXP_PLG := XXHCM_EXP_CURRENT(REEC_EMPLOYEE_DETAILS.PERSON_ID,l_date);
DBMS_OUTPUT.PUT_LINE(REEC_EMPLOYEE_DETAILS.Effect_Start_Date||' '||' '||REEC_EMPLOYEE_DETAILS.Employee_Number||' '||REEC_EMPLOYEE_DETAILS.Full_Name||' '||REEC_EMPLOYEE_DETAILS.DOJ);
fnd_file.PUT_LINE(fnd_file.log, 'Processing for '|| REEC_EMPLOYEE_DETAILS.Employee_Number);
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<G_PER_ASSG_DETAILS>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Effect_Start_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Effect_Start_Date || ']]></Effect_Start_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Employee_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Employee_Number || ']]></Employee_Number>');
L_EMP := NULL;
L_EMP := REEC_EMPLOYEE_DETAILS.Employee_Number;
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Previous_Employee_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Previous_Employee_Number || ']]></Previous_Employee_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Full_Name><![CDATA[' || REEC_EMPLOYEE_DETAILS.Full_Name || ']]></Full_Name>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<DOJ><![CDATA[' || REEC_EMPLOYEE_DETAILS.DOJ || ']]></DOJ>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Gender><![CDATA[' || REEC_EMPLOYEE_DETAILS.Gender || ']]></Gender>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Birth_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Birth_Date || ']]></Birth_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Age><![CDATA[' || REEC_EMPLOYEE_DETAILS.Age || ']]></Age>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Country_of_Birth><![CDATA[' || REEC_EMPLOYEE_DETAILS.Country_of_Birth || ']]></Country_of_Birth>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Marital_Status><![CDATA[' || REEC_EMPLOYEE_DETAILS.Marital_Status || ']]></Marital_Status>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Nationality><![CDATA[' || REEC_EMPLOYEE_DETAILS.Nationality || ']]></Nationality>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Residential_Status><![CDATA[' || REEC_EMPLOYEE_DETAILS.Residential_Status || ']]></Residential_Status>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Registered_Disabled><![CDATA[' || REEC_EMPLOYEE_DETAILS.Registered_Disabled || ']]></Registered_Disabled>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Blood_Type><![CDATA[' || REEC_EMPLOYEE_DETAILS.Blood_Type || ']]></Blood_Type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<PAN_Reference_Number><![CDATA['|| REEC_EMPLOYEE_DETAILS.PAN_Reference_Number || ']]></PAN_Reference_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<PAN><![CDATA[' || REEC_EMPLOYEE_DETAILS.PAN || ']]></PAN>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<PAN_Applied_For><![CDATA[' || REEC_EMPLOYEE_DETAILS.PAN_Applied_For || ']]></PAN_Applied_For>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Provident_Fund_Number><![CDATA['|| REEC_EMPLOYEE_DETAILS.Provident_Fund_Number || ']]></Provident_Fund_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<ESI_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.ESI_Number || ']]></ESI_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Superannuation_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Superannuation_Number || ']]></Superannuation_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Group_Insurance_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Group_Insurance_Number || ']]></Group_Insurance_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Gratutity_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Gratutity_Number || ']]></Gratutity_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Pension_Fund_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Pension_Fund_Number || ']]></Pension_Fund_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Address><![CDATA[' || REEC_EMPLOYEE_DETAILS.Address || ']]></Address>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Email><![CDATA[' || REEC_EMPLOYEE_DETAILS.Email || ']]></Email>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Mobile_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Mobile_Number || ']]></Mobile_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Emergency_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Emergency_Number || ']]></Emergency_Number>');
---Highest Qualification
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Highest_Qualification><![CDATA[' || REEC_EMPLOYEE_DETAILS.Highest_Qualification || ']]></Highest_Qualification>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Type_Specialisation><![CDATA[' || REEC_EMPLOYEE_DETAILS.Type_Specialisation || ']]></Type_Specialisation>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Grade><![CDATA[' || REEC_EMPLOYEE_DETAILS.Grade || ']]></Grade>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<University><![CDATA[' || REEC_EMPLOYEE_DETAILS.University || ']]></University>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Full_Time_Part_Time><![CDATA[' || REEC_EMPLOYEE_DETAILS.Full_Time_Part_Time || ']]></Full_Time_Part_Time>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<year_of_completion><![CDATA[' || REEC_EMPLOYEE_DETAILS.year_of_completion || ']]></year_of_completion>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<School_College ><![CDATA[' || REEC_EMPLOYEE_DETAILS.School_College || ']]></School_College >');
---------------------------------------------------------------------------------------------------------------------------------
----Previous Employment
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Employer><![CDATA[' || REEC_EMPLOYEE_DETAILS.Employer || ']]></Employer>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Emp_Address><![CDATA[' || REEC_EMPLOYEE_DETAILS.Emp_Address || ']]></Emp_Address>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Country><![CDATA[' || REEC_EMPLOYEE_DETAILS.Country || ']]></Country>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Type><![CDATA[' || REEC_EMPLOYEE_DETAILS.Type || ']]></Type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Sub_Type><![CDATA[' || REEC_EMPLOYEE_DETAILS.Sub_Type || ']]></Sub_Type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Start_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Start_Date || ']]></Start_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<End_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.End_Date || ']]></End_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Last_Designation><![CDATA[' || REEC_EMPLOYEE_DETAILS.Last_Designation || ']]></Last_Designation>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Total_Experience><![CDATA[' || L_EXP_TOT || ']]></Total_Experience>');
----------------------------------------------------------------------------------------------------------------------------------
---Passport Details
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Passport_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Passport_Number || ']]></Passport_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<ECNR_Required><![CDATA[' || REEC_EMPLOYEE_DETAILS.ECNR_Required || ']]></ECNR_Required>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Passport_Issue_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Passport_Issue_Date || ']]></Passport_Issue_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Passport_Expiry_Date><![CDATA[' || REEC_EMPLOYEE_DETAILS.Passport_Expiry_Date || ']]></Passport_Expiry_Date>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Issue_Place><![CDATA[' || REEC_EMPLOYEE_DETAILS.Issue_Place || ']]></Issue_Place>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Passport_Issuing_Country><![CDATA[' || REEC_EMPLOYEE_DETAILS.Passport_Issuing_Country || ']]></Passport_Issuing_Country>');
------------------------------------------------------------------------------------------------------------------------------------
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Total_Exp_PLG><![CDATA[' || L_EXP_PLG || ']]></Total_Exp_PLG>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Have_Passport><![CDATA[' || REEC_EMPLOYEE_DETAILS.Have_Passport || ']]></Have_Passport>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<DRIVING_LICENCE_DETAILS><![CDATA[' || REEC_EMPLOYEE_DETAILS.DRIVING_LICENCE_DETAILS || ']]></DRIVING_LICENCE_DETAILS>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<WORK_STATION_DETAILS><![CDATA[' || REEC_EMPLOYEE_DETAILS.WORK_STATION_DETAILS || ']]></WORK_STATION_DETAILS>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Language_Details><![CDATA[' || REEC_EMPLOYEE_DETAILS.Language_Details || ']]></Language_Details>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Entity><![CDATA[' || REEC_EMPLOYEE_DETAILS.Entity || ']]></Entity>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Organization><![CDATA[' || REEC_EMPLOYEE_DETAILS.Organization || ']]></Organization>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Organization_Type><![CDATA[' || REEC_EMPLOYEE_DETAILS.Organization_Type || ']]></Organization_Type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Location><![CDATA[' || REEC_EMPLOYEE_DETAILS.Location || ']]></Location>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Job><![CDATA[' || REEC_EMPLOYEE_DETAILS.Job || ']]></Job>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Assignment_Grade><![CDATA[' || REEC_EMPLOYEE_DETAILS.Assignment_Grade || ']]></Assignment_Grade>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Employee_Category><![CDATA[' || REEC_EMPLOYEE_DETAILS.Employee_Category || ']]></Employee_Category>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Manager><![CDATA[' || REEC_EMPLOYEE_DETAILS.Manager || ']]></Manager>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Manager_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Manager_Number || ']]></Manager_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<BGC_Details><![CDATA[' || REEC_EMPLOYEE_DETAILS.BGC_Details || ']]></BGC_Details>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<person_type><![CDATA[' || REEC_EMPLOYEE_DETAILS.person_type || ']]></person_type>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Employee_Role><![CDATA[' || REEC_EMPLOYEE_DETAILS.Employee_Role || ']]></Employee_Role>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Admin_Manager><![CDATA[' || REEC_EMPLOYEE_DETAILS.Admin_Manager || ']]></Admin_Manager>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Admin_Manager_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.Admin_Manager_Number || ']]></Admin_Manager_Number>');
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</G_PER_ASSG_DETAILS>');
i:=i+1;
-- fnd_file.PUT_LINE(fnd_file.OUTPUT, '</G_PER_ASSG_DETAILS>');
DBMS_OUTPUT.PUT_LINE('-----------------------------------');
END LOOP;
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</PERSON_ASSIGNMENT_DETAIL>');
DBMS_OUTPUT.PUT_LINE(i);
EXCEPTION WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.log, 'FAILED FOR ' || L_EMP );
fnd_file.PUT_LINE(fnd_file.log, SUBSTR(SQLERRM,1,200));
END XXTEST_PER_ASN_DET_IND_PROC;
END XXTEST_PER_ASSIGN_DETAILS_PKG;
No comments:
Post a Comment