Core HR Reports 1 : Pesronal and assignment Details history Report

create or replace PACKAGE BODY XXARJ_PER_ASSG_DET_REP_PKG AS

    PROCEDURE XXARJ_PER_ASN_DET_IND_PROC(errbuf OUT VARCHAR2,
                                        retcode OUT NUMBER,
                                        P_EFFECTIVE_DATE       IN VARCHAR2,
                                        P_ORGANIZATION_ID      IN NUMBER,
                                        P_ROLL_UP              IN VARCHAR2,
                                        p_location_id          IN NUMBER,
                                        p_job_id               IN NUMBER,
                                        p_grade_id             IN NUMBER,
                                        p_user_person_type     IN VARCHAR2,
                                        P_EMPLOYEE_NUMBER      IN VARCHAR2,
                                        p_ass_attribute1       IN varchar2,
                                        p_NATIONALITY          IN VARCHAR2,
                                        p_sex                  IN VARCHAR2,
                                        p_DOJ                  IN DATE,
                                        P_PAN                  IN VARCHAR2,
                                        P_PAN_Applied_for      IN VARCHAR2,
                                        p_manager_number       IN VARCHAR2,
                                        p_Passport             IN VARCHAR2)
    AS
        l_date DATE;
        CURSOR XXARJ_emp_details_cur(l_date VARCHAR2) is
        SELECT papf.person_id person_id
              ,papf.business_group_id business_group_id
              ,papf.employee_number employee_number
              ,papf.attribute1 previous_employee_number
              ,papf.full_name full_name
              ,papf.original_date_of_hire doj
              ,papf.effective_start_date  latest_start_date    -- added on 18th nov 2011
              ,hr_general.decode_lookup('SEX', papf.sex) gender
              ,papf.date_of_birth birth_date
              ,Round((months_between(l_date, 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
              ,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
              ,rtrim(pa.address_line1 ||', '||
               pa.address_line2 ||', '||
               pa.address_line3 ||', '||
               pa.add_information14 ||', '||
               hr_general.decode_lookup('IN_STATES', pa.add_information15) ||', '||
               hr_general.decode_lookup('PER_US_COUNTRY_CODE', pa.country) ||', '||
               pa.postal_code, ', ') address
              ,papf.email_address email
              ,replace(rtrim((SELECT rtrim (xmlagg (xmlelement (e, phone_number || ', ')).extract ('//text()'), ',') phone_numbers
                FROM per_phones
                WHERE parent_id = papf.person_id
                AND phone_type = 'M'
                GROUP BY parent_id), ', '), chr(10)) mobile_number
              ,replace(rtrim((SELECT rtrim (xmlagg (xmlelement (e, phone_number || ', ')).extract ('//text()'), ',') phone_numbers
                FROM per_phones
                WHERE parent_id = papf.person_id
                AND phone_type = 'P'
                GROUP BY parent_id), ', '), chr(10)) emergency_number
              ,ppei.pei_information2 passport_number
              ,ppei.pei_information6 emigration_check_required
              ,TO_CHAR(to_date(substr(ppei.pei_information4,1,10),'YYYY-MM-DD'),'DD-Mon-YYYY') passport_issue_date
              ,TO_CHAR(to_date(substr(ppei.pei_information5,1,10),'YYYY-MM-DD'),'DD-Mon-YYYY') passport_expiry_date
              ,ppei.pei_information3 issue_place
              ,hr_general.decode_lookup('PER_US_COUNTRY_CODE',ppei.pei_information7) passport_issuing_country
              ,(select 'Licence Number: ' || pac.segment1 ||
                      ' Licence Type: ' || pac.segment2 ||
                      ' Issue Date: ' || TO_CHAR(to_date(substr(pac.segment3,1,10),'YYYY-MM-DD'),'DD-Mon-YYYY') ||
                      ' Licence Expiry Date: ' || TO_CHAR(to_date(substr(pac.segment4,1,10),'YYYY-MM-DD'),'DD-Mon-YYYY') ||
                      ' Place Of Issue:?' || pac.segment5 ||
                      ' Issuing Country:?' || hr_general.decode_lookup('PER_US_COUNTRY_CODE',pac.segment6)
                  from  fnd_id_flex_structures fifs
                        ,per_person_analyses ppa
                        ,per_analysis_criteria pac
                  where       fifs.id_flex_structure_code = 'XXARJ_DRIVING_LICENSE'
                        AND   ppa.person_id                  = papf.person_id
                        AND   ppa.id_flex_num                = fifs.id_flex_num
                        AND   ppa.business_group_id          = papf.business_group_id
                        AND   pac.analysis_criteria_id       = ppa.analysis_criteria_id
                        AND   pac.id_flex_num                = ppa.id_flex_num
                        AND   fifs.id_flex_num               = pac.id_flex_num
                        AND   ppa.date_from = (select max(ppa1.date_from) from per_person_analyses ppa1 where ppa1.person_id = papf.person_id)
                        AND   ROWNUM <= 1
              ) driving_licence
              ,(select 'Work Station: ' || pac.segment1 ||
                      ' Extension Number: ' || pac.segment2 ||
                      ' Floor: ' || pac.segment3 ||
                      ' Building Number: ' || pac.segment4
                  from  fnd_id_flex_structures fifs
                        ,per_person_analyses ppa
                        ,per_analysis_criteria pac
                  where       fifs.id_flex_structure_code = 'XXARJ_WORK_STATION'
                        AND   ppa.person_id                  = papf.person_id
                        AND   ppa.id_flex_num                = fifs.id_flex_num
                        AND   ppa.business_group_id          = papf.business_group_id
                        AND   pac.analysis_criteria_id       = ppa.analysis_criteria_id
                        AND   pac.id_flex_num                = ppa.id_flex_num
                        AND   fifs.id_flex_num               = pac.id_flex_num
                        AND   ppa.date_from = (select max(ppa1.date_from) from per_person_analyses ppa1 where ppa1.person_id = papf.person_id)
                        AND   ROWNUM <= 1
              ) work_station_details
              ,(select 'BGC Conducted: ' || pac.segment1 ||
                      ' BGC Status: ' || pac.segment2 ||
                      ' BGC Conducted On: ' || pac.segment3
                  from  fnd_id_flex_structures fifs
                        ,per_person_analyses ppa
                        ,per_analysis_criteria pac
                  where       fifs.id_flex_structure_code = 'XXARJ_BACKGROUND_CHECK'
                        AND   ppa.person_id                  = papf.person_id
                        AND   ppa.id_flex_num                = fifs.id_flex_num
                        AND   ppa.business_group_id          = papf.business_group_id
                        AND   pac.analysis_criteria_id       = ppa.analysis_criteria_id
                        AND   pac.id_flex_num                = ppa.id_flex_num
                        AND   fifs.id_flex_num               = pac.id_flex_num
                        AND   ppa.date_from = (select max(ppa1.date_from) from per_person_analyses ppa1 where ppa1.person_id = papf.person_id)
                        AND   ROWNUM <= 1
              ) bgc_details
              ,nvl(XXARJ_hr_general_pkg.get_organization_name(paaf.organization_id, 'Entity')
                  ,XXARJ_hr_general_pkg.get_organization_name(paaf.organization_id, 'Business Group')) entity
              ,hr_general.decode_lookup('ORG_TYPE',haou.type) organization_type
              ,hr_general.decode_organization(paaf.organization_id) organization_name
              ,hr_general.decode_location(paaf.location_id) location_name
              ,hr_general.decode_job(paaf.job_id) job_name
              ,hr_general.decode_grade(paaf.grade_id) grade_name
              ,ppt.user_person_type person_type
              ,initcap(paaf.employee_category) employee_category
              ,papf_sup.full_name manager_name
              ,papf_sup.employee_number manager_number
              ,flv.meaning employee_role
              ,papf_admin.full_name admin_name
              ,papf_admin.employee_number admin_number
        FROM   per_all_people_f papf
              ,per_all_assignments_f paaf
              ,per_addresses pa
              ,per_people_extra_info ppei
              ,hr_all_organization_units haou
              ,per_person_type_usages_f pptuf
              ,per_person_types ppt
              ,per_all_people_f papf_sup
              ,fnd_lookup_values flv
              ,per_all_people_f papf_admin
        WHERE papf.person_id = paaf.person_id
        AND   papf.current_employee_flag = 'Y'
        AND   paaf.primary_flag = 'Y'
        AND   paaf.assignment_type = 'E'
        AND   paaf.assignment_status_type_id in (1,3096)
        --AND   papf.business_group_id = 81
        AND   pa.person_id(+) = papf.person_id
        AND   pa.primary_flag(+) = 'Y'
        AND   pa.style(+) = 'IN'
        AND   l_date BETWEEN pa.date_from(+) AND nvl(pa.date_to(+), to_date('31-DEC-4712'))
        AND   l_date between papf.effective_start_date and papf.effective_end_date
        AND   l_date between paaf.effective_start_date and paaf.effective_end_date
        AND   (papf.employee_number LIKE 'P%' OR papf.employee_number LIKE 'C%')
        AND   ppei.person_id(+) = papf.person_id
        AND   ppei.information_type(+) = 'IN_PASSPORT_DETAILS'
        AND   paaf.organization_id = haou.organization_id
        AND   papf.person_id = pptuf.person_id
        AND   ppt.person_type_id = pptuf.person_type_id
        AND   l_date between pptuf.effective_start_date and pptuf.effective_end_date
        AND   ppt.system_person_type = 'EMP'
        AND   ppt.active_flag = 'Y'
        AND   paaf.supervisor_id = papf_sup.person_id(+)
        AND   l_date between papf_sup.effective_start_date(+) and papf_sup.effective_end_date(+)
        AND   paaf.ass_attribute1 = flv.lookup_code(+)
        AND   flv.lookup_type(+) = 'XXARJ_ROLE'
        AND   paaf.ASS_ATTRIBUTE3 = papf_admin.person_id(+)
        AND   l_date between papf_admin.effective_start_date(+) and papf_admin.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 = XXHCM_HR_HOST.get_hierarchy_id()
                                        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 ((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   ((paaf.organization_id IN (SELECT child_org_id
                                         FROM XXARJ_org_structure
                                         WHERE P_ROLL_UP = 'Y'
                                         AND parent_org_id = P_ORGANIZATION_ID))
              OR (PAAF.ORGANIZATION_ID=P_ORGANIZATION_ID))
        AND   nvl(paaf.location_id,-1) = nvl(nvl(p_location_id, paaf.location_id),-1)
        AND   nvl(paaf.job_id,-1) = nvl(nvl(p_job_id, paaf.job_id),-1)
        AND   nvl(paaf.grade_id,-1) = nvl(nvl(p_grade_id, paaf.grade_id),-1)
        AND   pptuf.person_type_id = nvl(p_user_person_type, pptuf.person_type_id)
        AND   papf.person_id = nvl(P_EMPLOYEE_NUMBER, papf.person_id)
        AND   nvl(paaf.ass_attribute1,-1) = nvl(p_ass_attribute1,nvl(paaf.ass_attribute1,-1))
        AND   nvl(papf.nationality,-1) = nvl(p_NATIONALITY,nvl(papf.nationality,-1))
        AND   papf.sex = nvl(p_sex,papf.sex)
        AND   nvl(papf.original_date_of_hire,to_date('01-Jan-1951')) = nvl(nvl(p_DOJ,papf.original_date_of_hire),to_date('01-Jan-1951'))
        AND   nvl(papf.per_information4,-1) = nvl(nvl(P_PAN, papf.per_information4),-1)
        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(paaf.supervisor_id,-1) = nvl(nvl(p_manager_number, paaf.supervisor_id),-1)
        AND   nvl2(ppei.pei_information2, 'Y', 'N') = nvl(p_Passport, nvl2(ppei.pei_information2, 'Y', 'N'));
        --order by papf.person_id;
        --and papf.employee_number = 'P1002030';

        cursor highest_qualification(p_person_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_v pq
               ,per_qualification_types pqt
               ,per_establishment_attendances  pea
               ,per_establishments pe
          where pq.person_id                                          = p_person_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 = p_person_id)
              OR(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.attended_end_date IS NULL))
          AND ROWNUM <= 1;

        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) Prev_Experience
            ,(SELECT  ROUND(MONTHS_BETWEEN(l_date,Min(ppe.start_date)) / 12,1)
              FROM  per_previous_employers ppe
              WHERE ppe.person_id                                     = p_person_id
              )  Total_Experience
            ,(SELECT ROUND(MONTHS_BETWEEN(l_date,original_date_of_hire )/12,1)
              FROM per_all_people_f
              WHERE person_id = p_person_id
              AND l_date BETWEEN effective_start_date AND effective_end_date) EXP_IN_PLG
          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
          AND (nvl(ppe.start_date,to_date('01-Jan-0001','DD-Mon-YYYY')))  IN (SELECT MAX(nvl(ppe.start_date,to_date('01-Jan-0001','DD-Mon-YYYY')))
                                                                              FROM per_previous_employers ppe
                                                                              WHERE ppe.person_id = p_person_id)
          AND ROWNUM <= 1;

        cursor language_prof(p_person_id number, p_business_group_id number) is
          select  pac.segment1
                ,pac.segment2
                ,pac.segment3
                ,pac.segment4
          from  fnd_id_flex_structures fifs
                ,per_person_analyses ppa
                ,per_analysis_criteria pac
          where       fifs.id_flex_structure_code    = 'XXARJ_WORK_STATION'
                AND   ppa.person_id                  = p_person_id
                AND   ppa.id_flex_num                = fifs.id_flex_num
                AND   ppa.business_group_id          = p_business_group_id
                AND   pac.analysis_criteria_id       = ppa.analysis_criteria_id
                AND   pac.id_flex_num                = ppa.id_flex_num
                AND   fifs.id_flex_num               = pac.id_flex_num;

        i           number:=1;
        --L_EMP       VARCHAR2(50);
        L_EXP_TOT   VARCHAR2(50);
        L_EXP_PLG   VARCHAR2(50);
        l_e_ph_no   VARCHAR2(50);
        l_mb_no     VARCHAR2(50);
        l_speak varchar2(1000):='Speak :';
        l_write varchar2(1000):='Write :';
        l_read varchar2(1000):='Read :';
        l_result  VARCHAR2(10000) :='';
        L_LATEST_START_DATE DATE;
        L_ENTITY VARCHAR2(10000);
    BEGIN
        l_date := fnd_date.canonical_to_date(P_EFFECTIVE_DATE);
        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>');

        FOR REEC_EMPLOYEE_DETAILS IN XXARJ_emp_details_cur(l_date)
        LOOP
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<G_PER_ASSG_DETAILS>');
           --fnd_file.put_line(fnd_file.LOG,   '1st:=> ' || REEC_EMPLOYEE_DETAILS.employee_number);
         /*  BEGIN
           SELECT MAX(EFFECTIVE_START_DATE) INTO L_LATEST_START_DATE FROM PER_ALL_PEOPLE_F
           WHERE EMPLOYEE_NUMBER =REEC_EMPLOYEE_DETAILS.employee_number
        
           EXCEPTION WHEN OTHERS THEN
           L_LATEST_START_DATE := REEC_EMPLOYEE_DETAILS.latest_start_date;
           END;*/ -- THIS IS NOT DONE BECAUSE WE NEED TO FETCH AS PER EFFECTIVE DATE PARAMETER
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Employee_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.employee_number || ']]></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,   '<latest_start_date><![CDATA[' || REEC_EMPLOYEE_DETAILS.latest_start_date|| ']]></latest_start_date>');--added on 18th nov
           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>');
        FOR REEC_EMPLOYEE_QUALIFICATION IN highest_qualification(REEC_EMPLOYEE_DETAILS.person_id)
        LOOP
           ---Highest Qualification
           --fnd_file.put_line(fnd_file.LOG,   '2nd');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Highest_Qualification><![CDATA[' || REEC_EMPLOYEE_QUALIFICATION.Highest_Qualification || ']]></Highest_Qualification>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Type_Specialisation><![CDATA[' || REEC_EMPLOYEE_QUALIFICATION.Type_Specialisation || ']]></Type_Specialisation>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Grade><![CDATA[' || REEC_EMPLOYEE_QUALIFICATION.Grade || ']]></Grade>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<University><![CDATA[' || REEC_EMPLOYEE_QUALIFICATION.University || ']]></University>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Full_Time_Part_Time><![CDATA[' || REEC_EMPLOYEE_QUALIFICATION.Full_Time_Part_Time || ']]></Full_Time_Part_Time>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<year_of_completion><![CDATA[' || REEC_EMPLOYEE_QUALIFICATION.year_of_completion || ']]></year_of_completion>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<School_College ><![CDATA[' || REEC_EMPLOYEE_QUALIFICATION.School_College || ']]></School_College >');
        END LOOP;
           ---------------------------------------------------------------------------------------------------------------------------------
        FOR REEC_EMPLOYER_DETAILS IN previous_employment(REEC_EMPLOYEE_DETAILS.person_id, REEC_EMPLOYEE_DETAILS.business_group_id)
        LOOP
           ----Previous Employment
           --fnd_file.put_line(fnd_file.LOG,   '3rd');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Employer><![CDATA[' || REEC_EMPLOYER_DETAILS.Employer || ']]></Employer>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Emp_Address><![CDATA[' || REEC_EMPLOYER_DETAILS.Emp_Address || ']]></Emp_Address>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Country><![CDATA[' || REEC_EMPLOYER_DETAILS.Country || ']]></Country>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Type><![CDATA[' || REEC_EMPLOYER_DETAILS.Type || ']]></Type>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Sub_Type><![CDATA[' || REEC_EMPLOYER_DETAILS.Sub_Type || ']]></Sub_Type>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Start_Date><![CDATA[' || REEC_EMPLOYER_DETAILS.Start_Date || ']]></Start_Date>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<End_Date><![CDATA[' || REEC_EMPLOYER_DETAILS.End_Date || ']]></End_Date>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Last_Designation><![CDATA[' || REEC_EMPLOYER_DETAILS.Last_Designation || ']]></Last_Designation>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Total_Experience><![CDATA[' || REEC_EMPLOYER_DETAILS.Total_Experience || ']]></Total_Experience>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Total_Exp_PLG><![CDATA[' || REEC_EMPLOYER_DETAILS.EXP_IN_PLG || ']]></Total_Exp_PLG>');
        END LOOP;
       ----------------------------------------------------------------------------------------------------------------------------------
           ---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.emigration_check_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,   '<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 || ']]></DRIVING_LICENCE_DETAILS>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<WORK_STATION_DETAILS><![CDATA[' || REEC_EMPLOYEE_DETAILS.work_station_details || ']]></WORK_STATION_DETAILS>');
        l_speak :='Speak :';
        l_write :='Write :';
        l_read  :='Read :';
        l_result :='';
        for i in language_prof(REEC_EMPLOYEE_DETAILS.person_id, REEC_EMPLOYEE_DETAILS.business_group_id)
        loop
        --fnd_file.put_line(fnd_file.LOG,   '4th');
            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;
            fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Language_Details><![CDATA[' || l_result || ']]></Language_Details>');
        end loop;
           --fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Language_Details><![CDATA['           || REEC_EMPLOYEE_DETAILS.Language_Details                                 || ']]></Language_Details>');
           ---------------------
           IF REEC_EMPLOYEE_DETAILS.organization_type ='Entity' THEN
           L_ENTITY := REEC_EMPLOYEE_DETAILS.organization_name;
           ELSE
           L_ENTITY := REEC_EMPLOYEE_DETAILS.entity;
           END IF;
          
          
           -------------------------
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Entity><![CDATA[' || L_ENTITY || ']]></Entity>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Organization_Type><![CDATA[' || REEC_EMPLOYEE_DETAILS.organization_type || ']]></Organization_Type>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Organization><![CDATA[' || REEC_EMPLOYEE_DETAILS.organization_name || ']]></Organization>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Location><![CDATA[' || REEC_EMPLOYEE_DETAILS.location_name || ']]></Location>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Job><![CDATA[' || REEC_EMPLOYEE_DETAILS.job_name || ']]></Job>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Assignment_Grade><![CDATA[' || REEC_EMPLOYEE_DETAILS.grade_name || ']]></Assignment_Grade>');
           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_Category><![CDATA[' || REEC_EMPLOYEE_DETAILS.employee_category || ']]></Employee_Category>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Manager><![CDATA[' || REEC_EMPLOYEE_DETAILS.manager_name || ']]></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,   '<Employee_Role><![CDATA[' || REEC_EMPLOYEE_DETAILS.employee_role || ']]></Employee_Role>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Admin_Manager><![CDATA[' || REEC_EMPLOYEE_DETAILS.admin_name || ']]></Admin_Manager>');
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Admin_Manager_Number><![CDATA[' || REEC_EMPLOYEE_DETAILS.admin_number || ']]></Admin_Manager_Number>');
           --fnd_file.put_line(fnd_file.LOG,   '5th:=> ' || REEC_EMPLOYEE_DETAILS.employee_number);
           fnd_file.PUT_LINE(fnd_file.OUTPUT,   '</G_PER_ASSG_DETAILS>');
        END LOOP;
        fnd_file.PUT_LINE(fnd_file.OUTPUT,   '</PERSON_ASSIGNMENT_DETAIL>');
    END XXARJ_PER_ASN_DET_IND_PROC;
END XXARJ_PER_ASSG_DET_REP_PKG;