Employees who have a certain Element Entries with no values restrictions

select distinct
--asg.BUSINESS_GROUP_ID,
--pee.CREATOR_TYPE, pee.ENTRY_TYPE,
/*asg.ASSIGNMENT_ID,
pee.ELEMENT_ENTRY_ID,
pet.ELEMENT_TYPE_ID,*/
asg.ASSIGNMENT_NUMBER EMP_NO,
--,pep.TITLE||' '||pep.FIRST_NAME||' '||pep.LAST_NAME Name
--,substr(pg.GROUP_NAME,instr(pg.GROUP_NAME,'.')+1,instr(pg.GROUP_NAME,'.',1,2) - instr(pg.GROUP_NAME,'.')-1) Housing_Status
--,grd.NAME
pet.ELEMENT_NAME
-- --,pet.PROCESSING_PRIORITY
-- --,pee.SOURCE_ASG_ACTION_ID
-- --,pee.SOURCE_ID
 ,to_char(pee.EFFECTIVE_START_DATE,'dd/mm/yyyy')  Effective_Date
--,to_char(pee.EFFECTIVE_END_DATE,'dd/mm/yyyy')  END
--,pee.LAST_UPDATE_DATE Last_Update
--,pee.CREATED_BY
--Count (1)
from pay_element_types_f pet
,pay_element_entries_f pee
,per_all_Assignments_f Asg
,PER_ASSIGNMENT_STATUS_TYPES past
,pay_element_links_f pel
,pay_people_groups pg
,HR_GRADE_LOV_V GRD
,hr_organization_units org
--,per_all_people_f pep
--,xx.xx_emp_num rta
where 1=1
 and asg.business_group_id  in (4508)
and past.ASSIGNMENT_STATUS_TYPE_ID = asg.ASSIGNMENT_STATUS_TYPE_ID
and asg.GRADE_ID = grd.GRADE_ID
--and grd.NAME not like '%DSF%'
/*and  last_day(to_date('1-1-2006','dd-mm-yyyy')) between pep.EFFECTIVE_START_DATE and pep.EFFECTIVE_END_DATE
and pep.BUSINESS_GROUP_ID = asg.BUSINESS_GROUP_ID
and pep.PERSON_ID = asg.PERSON_ID*/
--and upper(past.USER_STATUS)   like Upper('Suspend Assignment')
and pet.business_group_id = asg.business_group_id
--and Upper(pet.ELEMENT_NAME) like  upper('%AUTH LV WITHOUT PAY TAKEN%')
--and not Upper(pet.ELEMENT_NAME) like  upper('annual_leave_provision')
and pet.element_type_id = pel.element_type_id
and pel.BUSINESS_GROUP_ID = asg.BUSINESS_GROUP_ID
--and pet.CLASSIFICATION_ID in (96)    --   96  Earning      110  Deduction
and pel.element_link_id = pee.element_link_id
and pee.Assignment_id =  asg.Assignment_id
and asg.ASSIGNMENT_NUMBER in ('81')
--and pep.EMPLOYEE_NUMBER = rta.EMP_NUM
and pg.PEOPLE_GROUP_ID = asg.PEOPLE_GROUP_ID
--and org.NAME like 'Finance Department'
and asg.ORGANIZATION_ID = org.ORGANIZATION_ID
--and  upper(pg.GROUP_NAME) like upper('%%EXPAT%')
and last_day(to_date('30-9-2006','dd-mm-yyyy')) between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
--and pee.LAST_UPDATE_DATE >= to_date('25-4-2006','dd-mm-yyyy')
and last_day(to_date('30-10-2006','dd-mm-yyyy')) between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
--and pee.EFFECTIVE_START_DATE between to_date('1-10-2006','dd-mm-yyyy') and last_day(to_date('1-10-2006','dd-mm-yyyy'))
and asg.PAYROLL_ID is not null
--and pee.CREATOR_TYPE = 'H'
--and pet.CREATION_DATE >= to_date('1-1-2006','dd-mm-yyyy')
--and pet.PROCESSING_TYPE = 'R'

order by effective_date desc

group by
pee.ELEMENT_ENTRY_ID,
pet.ELEMENT_TYPE_ID,
asg.ASSIGNMENT_NUMBER
,pet.ELEMENT_NAME
,pet.PROCESSING_PRIORITY
--,pee.SOURCE_ASG_ACTION_ID
--,pee.SOURCE_ID
,to_char(pee.EFFECTIVE_START_DATE,'dd/mm/yyyy')
--order by assignment_number

EMPLOYEE_And_Employee_Address_Interface_Procedure

This summary is not available. Please click here to view the post.

Designation of Employee Query In Oracle

SELECT
papf.person_id
,papf.employee_number
,papf.first_name
,papf.full_name
,papf.sex
,papf.date_of_birth
,papf.effective_start_date papf_start_date_active
,papf.effective_end_date papf_end_date_active
,mgr.full_name manager
,mgr.employee_number mgr_emp_no
,mgr.person_id mgr_prson_id
,paaf.assignment_number
,paaf.effective_start_date assingment_start_date
,pj.job_id
,pj.NAME
,usr.user_id
,usr.user_name
,usr.last_logon_date
,jrs.salesrep_id
,jrs.resource_id
,jrs.salesrep_number
,jrs.name salesrep_name
,jrs.org_id sales_org_id
,jrs.start_date_active rep_start_date_active
,jrs.end_date_active rep_end_date_active

FROM
per_all_people_f papf
,per_all_people_f mgr
,per_all_assignments_f paaf
,per_jobs pj
,fnd_user usr
,jtf_rs_salesreps jrs

WHERE 1 = 1
AND papf.person_id = paaf.person_id (+)
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND NVL (papf.effective_end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date (+) AND NVL (paaf.effective_end_date (+), TRUNC (SYSDATE))
AND paaf.job_id = pj.job_id (+)
AND paaf.business_group_id = pj.business_group_id (+)
AND papf.party_id = usr.person_party_id (+)
AND TRUNC (SYSDATE) BETWEEN usr.start_date (+) AND NVL (usr.end_date (+) , TRUNC (SYSDATE))
AND paaf.supervisor_id = mgr.person_id (+)
AND TRUNC (SYSDATE) BETWEEN mgr.effective_start_date (+) AND NVL (mgr.effective_end_date (+), TRUNC (SYSDATE))
AND paaf.assignment_type (+) = 'E'
AND papf.person_id = jrs.person_id (+)
AND papf.full_name LIKE '%Behan, Linda%'
--AND papf.employee_number = '30880'
--AND papf.person_id = 36557
--AND mgr.employee_number = '141968'
--AND usr.user_name = 'AAOFFSHORE'
--AND usr.user_ID = 1234
--AND jrs.salesrep_id = 100009572
--AND jrs.resource_id = 100010609
--AND jrs.salesrep_number = 'XXSR100'
--AND jrs.name like 'Bashaar%'

Basic Payroll Tables and their links

SELECT * FROM PAY_ELEMENT_TYPES_F(PK: ELEMENT_TYPE_ID)

SELECT * FROM PAY_ELEMENT_LINKS_F(PK: ELEMENT_LINK_ID)
                                  (To link PAY_ELEMENT_TYPES_F:ELEMENT_TYPE_ID)

SELECT * FROM PAY_ELEMENT_ENTRIES_F (PK: ELEMENT_ENTRY_ID)
                                    (To link PER_ALL_ASSIGNMENTS_F :ASSIGNMENT_ID,
                                     PAY_ELEMENT_LINKS_F :ELEMENT_LINK_ID,
                                     PAY_ELEMENT_TYPES_F :ELEMENT_TYPE_ID)
                                                                 
SELECT * FROM PAY_ELEMENT_ENTRY_VALUES_F (PK:ELEMENT_ENTRY_VALUE_ID)
                                         (To link PAY_ELEMENT_ENTRIES_F : ELEMENT_ENTRY_ID,
                                          PAY_RUN_RESULT_VALUES : INPUT_VALUE_ID,
                                          PAY_COSTS : INPUT_VALUE_ID)                                                                

select * from PER_ALL_PEOPLE_F (PK:PERSON_ID)

select * from PER_ALL_ASSIGNMENTS_F (PK:ASSIGNMENT_ID)
                                    (To link PER_ALL_PEOPLE_F:PERSON_ID,
                                     PER_JOBS:JOB_ID,
                                     PER_GRADES:GRADE_ID,
                                     PER_POSITIONS : POSITION_ID,
                                     PER_BUSINESS_GROUPS:BUSINESS_GROUP_ID,
                                     PER_ASSIGNMENT_STATUS_TYPES:ASSIGNMENT_STATUS_TYPE_ID,
                                     HR_LOCATIONS :LOCATION_ID,
                                     PAY_PAYROLLS_F : PAYROLL_ID)
                                                           
SELECT * FROM PER_PERSON_TYPE_USAGES_F (PK: PERSON_TYPE_USAGE_ID)
                                        (To link  PER_ALL_PEOPLE_F: PERSON_ID,
                                         PER_PERSON_TYPES:PERSON_TYPE_ID)

SELECT * FROM PER_PERSON_TYPES  (PK:PERSON_TYPE_ID)
                                 (To link  PER_PERSON_TYPE_USAGES_F : PERSON_TYPE_ID)






Below are the basic main tables between HR and Payroll

1) Pay_all_payrolls_f-> Payroll_id  linked with PER_ALL_ASSIGNEMNTS_F.Payroll_id
2)Pay_personal_payment_methods_F->Assignment_id linked with PER_ALL_ASSIGNEMNTS_F.Assignment_id
3)PAY_ORG_PAYMENT_METHODS_F->Payment_type_id linked with  pay_payment_types.Payment_type_id
4) PER_ALL_ASSINMENTS_F->Assignment_id linked withPAY_ELEMENT_ENTRIES_F.Assignment_id
5)PAY_ELEMENT_TYPES_F->ELEMENT_LINK_ID linked with  PER_ELEMENT_LINKS_F.ELEMENT_LINK_ID
6)PAY_ELEMENT_TYPES_F-> element_type_id linked withpay_input_values_f.element_type_id
7)HR_ALL_POSITIONS_F->position_id  linked with PAY_ELEMENT_TYPES_F.Position_id

Oracle HRMS Scripts(Queries) - Employee Supervisor

SELECT asg.organization_id,
    emp.employee_number,
    emp.full_name emp_name,
    pos.NAME POSITION,
    sup.employee_number sup_emp_no,
    sup.full_name sup_name,
    sup_pos.NAME sup_pos,
    sppt.user_person_type,
    emp.effective_start_date emp_esd,
    emp.effective_end_date emp_eed,
    asg.effective_start_date asg_esd,
    asg.effective_end_date asg_eed,
    sup.effective_start_date sup_esd,
    sup.effective_end_date sup_eed,
    sup_asg.effective_start_date sup_asg_esd,
    sup_asg.effective_end_date sup_asg_eed,
    sptu.effective_start_date sptu_esd,
    sptu.effective_end_date sptu_eed    
  FROM per_all_people_f emp,
       per_all_assignments_f asg,
       per_all_positions pos,
       per_all_people_f sup,
       per_all_assignments_f sup_asg,
       per_all_positions sup_pos,
       per_person_type_usages_f sptu,
       per_person_types sppt
 WHERE asg.person_id = emp.person_id
   AND pos.position_id(+) = asg.position_id
   AND sup.person_id(+) = asg.supervisor_id
   AND sup_asg.person_id(+) = sup.person_id
   AND sup_pos.position_id(+) = sup_asg.position_id
   AND sptu.person_id = emp.person_id
   AND sppt.person_type_id = sptu.person_type_id
   and emp.person_type_id = 1120


Best Blogger TemplatesBest Blogger Tips
Posted by Raju Ch at 12:45 AM 0 comments
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest

Labels: HRMS, SCRIPTS


Oracle HRMS Scripts(Queries) - Employee Elements
SELECT entry.element_entry_id, entry.assignment_id,
          entry.effective_start_date, entry.effective_end_date,
          entry.cost_allocation_keyflex_id, entry.updating_action_id,
          entry.element_link_id, entry.original_entry_id, entry.creator_type,
          entry.entry_type, DECODE (entry.entry_type, 'D', 'Y', 'N'),
          DECODE (entry.entry_type, 'S', 'Y', 'N'), entry.comment_id,
          entry.creator_id, entry.reason,
          hr_general.decode_lookup ('ELE_ENTRY_REASON', entry.reason),
          entry.target_entry_id, entry.attribute_category, entry.attribute1,
          entry.attribute2, entry.attribute3, entry.attribute4,
          entry.attribute5, entry.attribute6, entry.attribute7,
          entry.attribute8, entry.attribute9, entry.attribute10,
          entry.attribute11, entry.attribute12, entry.attribute13,
          entry.attribute14, entry.attribute15, entry.attribute16,
          entry.attribute17, entry.attribute18, entry.attribute19,
          entry.attribute20, ELEMENT.element_type_id, elementtl.element_name,
          SUBSTR (ELEMENT.processing_type, 1, 1),
          hr_general.decode_lookup ('PROCESSING_TYPE',
                                    ELEMENT.processing_type),
          ELEMENT.processing_priority,
          SUBSTR (ELEMENT.process_in_run_flag, 1, 1),
          SUBSTR (ELEMENT.closed_for_entry_flag, 1, 1),
          SUBSTR (ELEMENT.additional_entry_allowed_flag, 1, 1),
          SUBSTR (ELEMENT.multiple_entries_allowed_flag, 1, 1),
          ELEMENT.input_currency_code,
          SUBSTR (pay_paywsmee_pkg.overridden (LINK.element_link_id,
                                               entry.assignment_id,
                                               sesh.effective_date
                                              ),
                  1,
                  1
                 ),
          SUBSTR (pay_paywsmee_pkg.adjusted (LINK.element_link_id,
                                             entry.assignment_id,
                                             sesh.effective_date
                                            ),
                  1,
                  1
                 ),
          SUBSTR (pay_paywsmee_pkg.processed (entry.element_entry_id,
                                              entry.original_entry_id,
                                              ELEMENT.processing_type,
                                              entry.entry_type,
                                              sesh.effective_date
                                             ),
                  1,
                  1
                 ),
          DECODE (NVL (entry_proc.source_asg_action_id, -1),
                  -1, 'N',
                  'Y'
                 ) retroactive,
          ELEMENT.classification_id, SUBSTR (LINK.costable_type, 1, 1),
          entry.subpriority, benefit.contributions_used, entry.creation_date,
          entry.created_by, entry.last_update_login, entry.last_updated_by,
          entry.last_update_date, entry.date_earned,
          pay_paywsmee_pkg.get_original_date_earned
                                 (entry.element_entry_id)
                                                         original_date_earned,
          entry.personal_payment_method_id,
          SUBSTR
             (pay_paywsmee_pkg.personal_payment_method
                                            (entry.personal_payment_method_id,
                                             entry.assignment_id,
                                             sesh.effective_date
                                            ),
              1,
              255
             ),
          ELEMENT.third_party_pay_only_flag, entry.ROWID row_id,
          entry.entry_information_category, entry.entry_information1,
          entry.entry_information2, entry.entry_information3,
          entry.entry_information4, entry.entry_information5,
          entry.entry_information6, entry.entry_information7,
          entry.entry_information8, entry.entry_information9,
          entry.entry_information10, entry.entry_information11,
          entry.entry_information12, entry.entry_information13,
          entry.entry_information14, entry.entry_information15,
          entry.entry_information16, entry.entry_information17,
          entry.entry_information18, entry.entry_information19,
          entry.entry_information20, entry.entry_information21,
          entry.entry_information22, entry.entry_information23,
          entry.entry_information24, entry.entry_information25,
          entry.entry_information26, entry.entry_information27,
          entry.entry_information28, entry.entry_information29,
          entry.entry_information30, -999999999999999
     FROM pay_element_types_f_tl elementtl,
          pay_element_types_f ELEMENT,
          ben_benefit_classifications benefit,
          pay_element_entries_f entry,
          pay_element_links_f LINK,
          pay_entry_process_details entry_proc,
          fnd_sessions sesh                                  /* TABLE JOINS */
    WHERE ELEMENT.element_type_id = elementtl.element_type_id
      AND elementtl.LANGUAGE = 'AR'
      AND ELEMENT.element_type_id = LINK.element_type_id
      AND ELEMENT.benefit_classification_id = benefit.benefit_classification_id(+)
      AND entry.element_link_id = LINK.element_link_id
      AND entry.element_entry_id =
                         entry_proc.element_entry_id(+)
                            /* ONLY DISPLAY ENTRIES FOR CERTAIN SUB-SYSTEMS */
      AND entry.creator_type IN
             ('H',                                                   /* MIX */
              'P',                                               /* BACKPAY */
              'SP',                                      /* SALARY PROPOSAL */
              'F',                                                 /* OTHER */
              'M',                               /* STATUTORY MATERNITY PAY */
              'S',                                    /* STATUTORY SICK PAY */
              'A',                                               /* ABSENCE */
              'D',                                           /* ADVANCE PAY */
              'DF',                                     /* ADVANCE PAY FORM */
              'R',                                    /* RETROPAY BY ACTION */
              'EE',                                  /* RETROPAY/ELEMENT EE */
              'RR',                                  /* RETROPAY/ELEMENT RR */
              'AD',                                /* ADVANCEPAY/ELEMENT AD */
              'AE',                                /* ADVANCEPAY/ELEMENT AE */
              'PR',                                  /* RETROPAY ELEMENT PR */
              'NR',                                  /* RETROPAY/ELEMENT NR */
              'FL'                                            /* FLSA ENTRY */
             )                     /* ONLY DISPLAY ENTRIES OF CERTAIN TYPES */
      AND entry.entry_type IN
             ('E', /* NORMAL ENTRY */ 'S', /* OVERRIDE */ 'D' /* ADDITIONAL */)
                       /* ONLY DISPLAY ENTRIES CURRENT AS OF EFFECTIVE DATE */
      AND USERENV ('sessionid') = sesh.session_id
      AND sesh.effective_date BETWEEN ELEMENT.effective_start_date AND ELEMENT.effective_end_date
      AND sesh.effective_date BETWEEN LINK.effective_start_date AND LINK.effective_end_date
      AND sesh.effective_date BETWEEN entry.effective_start_date AND entry.effective_end_date
   UNION ALL
   SELECT DISTINCT entry.element_entry_id, entry.assignment_id,
                   entry.effective_start_date, entry.effective_end_date,
                   entry.cost_allocation_keyflex_id, entry.updating_action_id,
                   entry.element_link_id, entry.original_entry_id,
                   entry.creator_type, entry.entry_type,
                   DECODE (entry.entry_type, 'D', 'Y', 'N'),
                   DECODE (entry.entry_type, 'S', 'Y', 'N'), entry.comment_id,
                   entry.creator_id, entry.reason,
                   hr_general.decode_lookup ('ELE_ENTRY_REASON', entry.reason),
                   entry.target_entry_id, entry.attribute_category,
                   entry.attribute1, entry.attribute2, entry.attribute3,
                   entry.attribute4, entry.attribute5, entry.attribute6,
                   entry.attribute7, entry.attribute8, entry.attribute9,
                   entry.attribute10, entry.attribute11, entry.attribute12,
                   entry.attribute13, entry.attribute14, entry.attribute15,
                   entry.attribute16, entry.attribute17, entry.attribute18,
                   entry.attribute19, entry.attribute20,
                   ELEMENT.element_type_id, elementtl.element_name,
                   SUBSTR (ELEMENT.processing_type, 1, 1),
                   hr_general.decode_lookup ('PROCESSING_TYPE',
                                             ELEMENT.processing_type
                                            ),
                   ELEMENT.processing_priority,
                   SUBSTR (ELEMENT.process_in_run_flag, 1, 1),
                   SUBSTR (ELEMENT.closed_for_entry_flag, 1, 1),
                   SUBSTR (ELEMENT.additional_entry_allowed_flag, 1, 1),
                   SUBSTR (ELEMENT.multiple_entries_allowed_flag, 1, 1),
                   ELEMENT.input_currency_code,
                   SUBSTR (pay_paywsmee_pkg.overridden (LINK.element_link_id,
                                                        entry.assignment_id,
                                                        sesh.effective_date
                                                       ),
                           1,
                           1
                          ),
                   SUBSTR (pay_paywsmee_pkg.adjusted (LINK.element_link_id,
                                                      entry.assignment_id,
                                                      sesh.effective_date
                                                     ),
                           1,
                           1
                          ),
                   SUBSTR
                        (pay_paywsmee_pkg.processed (entry.element_entry_id,
                                                     entry.original_entry_id,
                                                     ELEMENT.processing_type,
                                                     entry.entry_type,
                                                     sesh.effective_date
                                                    ),
                         1,
                         1
                        ),
                   DECODE (NVL (entry_proc.source_asg_action_id, -1),
                           -1, 'N',
                           'Y'
                          ) retroactive,
                   ELEMENT.classification_id,
                   SUBSTR (LINK.costable_type, 1, 1), entry.subpriority,
                   benefit.contributions_used, entry.creation_date,
                   entry.created_by, entry.last_update_login,
                   entry.last_updated_by, entry.last_update_date,
                   entry.date_earned,
                   pay_paywsmee_pkg.get_original_date_earned
                                 (entry.element_entry_id)
                                                         original_date_earned,
                   entry.personal_payment_method_id,
                   SUBSTR
                      (pay_paywsmee_pkg.personal_payment_method
                                            (entry.personal_payment_method_id,
                                             entry.assignment_id,
                                             sesh.effective_date
                                            ),
                       1,
                       255
                      ),
                   ELEMENT.third_party_pay_only_flag, entry.ROWID row_id,
                   entry.entry_information_category, entry.entry_information1,
                   entry.entry_information2, entry.entry_information3,
                   entry.entry_information4, entry.entry_information5,
                   entry.entry_information6, entry.entry_information7,
                   entry.entry_information8, entry.entry_information9,
                   entry.entry_information10, entry.entry_information11,
                   entry.entry_information12, entry.entry_information13,
                   entry.entry_information14, entry.entry_information15,
                   entry.entry_information16, entry.entry_information17,
                   entry.entry_information18, entry.entry_information19,
                   entry.entry_information20, entry.entry_information21,
                   entry.entry_information22, entry.entry_information23,
                   entry.entry_information24, entry.entry_information25,
                   entry.entry_information26, entry.entry_information27,
                   entry.entry_information28, entry.entry_information29,
                   entry.entry_information30, assact.assignment_action_id
              FROM pay_element_types_f_tl elementtl,
                   pay_element_types_f ELEMENT,
                   ben_benefit_classifications benefit,
                   pay_element_entries_f entry,
                   pay_element_links_f LINK,
                   pay_entry_process_details entry_proc,
                   pay_assignment_actions assact,
                   pay_payroll_actions payact,
                   fnd_sessions sesh                         /* TABLE JOINS */
             WHERE ELEMENT.element_type_id = elementtl.element_type_id
               AND elementtl.LANGUAGE = USERENV ('LANG')
               AND ELEMENT.element_type_id = LINK.element_type_id
               AND ELEMENT.benefit_classification_id = benefit.benefit_classification_id(+)
               AND entry.element_link_id = LINK.element_link_id
               AND entry.element_entry_id =
                         entry_proc.element_entry_id(+)
                            /* ONLY DISPLAY ENTRIES FOR CERTAIN SUB-SYSTEMS */
               AND entry.creator_type IN
                      ('H',                                          /* MIX */
                       'P',                                      /* BACKPAY */
                       'SP',                             /* SALARY PROPOSAL */
                       'F',                                        /* OTHER */
                       'M',                      /* STATUTORY MATERNITY PAY */
                       'S',                           /* STATUTORY SICK PAY */
                       'A',                                      /* ABSENCE */
                       'D',                                  /* ADVANCE PAY */
                       'DF',                            /* ADVANCE PAY FORM */
                       'R',                           /* RETROPAY BY ACTION */
                       'EE',                         /* RETROPAY/ELEMENT EE */
                       'RR',                         /* RETROPAY/ELEMENT RR */
                       'AD',                       /* ADVANCEPAY/ELEMENT AD */
                       'AE',                       /* ADVANCEPAY/ELEMENT AE */
                       'PR',                         /* RETROPAY ELEMENT PR */
                       'NR',                         /* RETROPAY/ELEMENT NR */
                       'FL'                                   /* FLSA ENTRY */
                      )            /* ONLY DISPLAY ENTRIES OF CERTAIN TYPES */
               AND entry.entry_type IN
                      ('E', /* NORMAL ENTRY */ 'S', /* OVERRIDE */ 'D' /* ADDITIONAL */)
                       /* ONLY DISPLAY ENTRIES CURRENT AS OF EFFECTIVE DATE */
               AND USERENV ('sessionid') = sesh.session_id
               AND sesh.effective_date BETWEEN ELEMENT.effective_start_date
                                           AND ELEMENT.effective_end_date
               AND sesh.effective_date BETWEEN LINK.effective_start_date
                                           AND LINK.effective_end_date
               AND entry.assignment_id = assact.assignment_id
               AND assact.payroll_action_id = payact.payroll_action_id
               AND payact.date_earned BETWEEN ELEMENT.effective_start_date
                                          AND ELEMENT.effective_end_date
               AND payact.date_earned BETWEEN LINK.effective_start_date
                                          AND LINK.effective_end_date
               AND entry.effective_start_date <= payact.date_earned
               AND entry.effective_end_date >=
                      DECODE
                         (ELEMENT.proration_group_id,
                          NULL, payact.date_earned,
                          pay_interpreter_pkg.prorate_start_date
                                                 (assact.assignment_action_id,
                                                  ELEMENT.proration_group_id
                                                 )

                         )

Oracle HRMS Query - Absence

SELECT   /*+ rule*/
            xx.person_id, xx.assignment_id, flv.meaning absence_category,
            c.NAME absence_type, flv1.meaning absence_reason,
            ABS.date_notification, ABS.date_projected_start,
            ABS.date_projected_end, ABS.date_start, ABS.date_end,
            ABS.absence_days, ABS.attribute1, ABS.attribute2, ABS.attribute3,
            ABS.attribute4, ABS.attribute5, ABS.attribute6, ABS.attribute7,
            ABS.attribute8, ABS.attribute9, ABS.attribute10, ABS.attribute11,
            ABS.attribute12, ABS.attribute13, ABS.attribute14,
            ABS.attribute15, ABS.attribute16, ABS.attribute17,
            ABS.attribute18, ABS.attribute19, ABS.attribute20,
         
            --, LOCATION,
            b.admission_code, b.admission_date, b.amendment_date,
            b.amendment_reason, b.concatenated_segments, b.contact_grade,
            b.contact_type, b.CONTEXT, b.discharge_date, b.disease_name,
            b.hospital_name, b.leave_amended, b.leave_salary_paid,
            b.physician_approved_accident, b.physician_name,
            b.resumption_date
       FROM per_absence_attendances ABS,
            per_absence_attendance_types c,
            per_abs_attendance_reasons d,
            per_absence_attendances_dfv b,
            fnd_lookup_values flv1,
            fnd_lookup_values flv,
            per_all_assignments_f xx                      --xxhr_zainiq_mv  xx
      WHERE ABS.person_id = xx.person_id
        AND ABS.ROWID = b.row_id
        AND ABS.absence_attendance_type_id = c.absence_attendance_type_id(+)
        AND ABS.abs_attendance_reason_id = d.abs_attendance_reason_id(+)
        AND d.NAME = flv1.lookup_code(+)
        AND flv1.lookup_type(+) = 'ABSENCE_REASON'
        AND flv.lookup_code(+) = c.absence_category
        AND flv.lookup_type(+) = 'ABSENCE_CATEGORY'
   ORDER BY ABS.person_id, absence_type, date_start DESC

Oracle HRMS Scripts(Queries) - Employee Relation

SELECT
rel.PERSON_ID Person_id ,
rel.CONTACT_PERSON_ID CONTACT_PERSON_ID,
per.full_name,
per.date_of_birth ,
hr_general.decode_lookup ('SEX', per.sex) gender ,
(SELECT
    look_up.meaning
 FROM hr_lookups look_up
 WHERE look_up.lookup_type = 'CONTACT'
    AND look_up.enabled_flag = 'Y'
    and look_up.lookup_code =  rel.CONTACT_TYPE
)  Relation
FROM
    per_contact_relationships rel,
    per_people_f per
WHERE
    rel.CONTACT_PERSON_ID = per.person_id and
    per.BUSINESS_GROUP_ID=hr_general.GET_BUSINESS_GROUP_ID

Oracle HRMS Scripts(Queries) - Contacts

SELECT /*+ rule */
  xx.employee_number empno,
  xx.full_name employee_full_name,
  per.effective_start_date,
  per.last_name contact_last_name,
  per.first_name contact_first_name,
  per.full_name contact_full_name,
  InitCap(per.title) Title,
  per.pre_name_adjunct  prefix,
  per.suffix,
  per.middle_names,
  hr_general.decode_lookup ('SEX', per.sex) gender,
  ppt.USER_PERSON_TYPE Person_type,
  per.national_identifier national_identifier,
  per.date_of_birth,
  per.town_of_birth,
  hr_general.decode_lookup ('MAR_STATUS',per.marital_status) marital_status,
  per.region_of_birth,
  hr_general.decode_lookup ('NATIONALITY', per.nationality) nationality,
  per.country_of_birth,
  per.registered_disabled_flag,
  per.email_address,
  per.honors,
  per.known_as preffered_name,
  per.previous_last_name,
  per.correspondence_language,
  --
  --
  per.attribute1 religion,
  per.attribute2 place_of_birth,
  per.attribute3 no_of_wife,
  per.attribute4 hajj_leave_taken,
  per.attribute5 citizenship,
  per.attribute6 marriage_leave_taken,
  per.attribute7 marriage_date,
  per.attribute8 xemployee_number,
  --
       pcr.date_start Relationship_start_date ,
       lkp.meaning Relationship_type ,
       --koc_get.emp(pcr.PERSON_ID) Relation_ship_from_employee,
       pcr.CONTACT_PERSON_ID Related_to_employee,
       --
       pcr.primary_contact_flag Primary_contact,
       pcr.third_party_pay_flag Payment_Recipient,
       pcr.rltd_per_rsds_w_dsgntr_flag Shared_Residence,
       pcr.personal_flag Personal_Relationship,
       pcr.beneficiary_flag Beneficiary,
       pcr.dependent_flag Dependent,
       pcr.sequence_number Sequence_Number,
       pcr.bondholder_flag Create_Mirror,
       pcr.contact_type    Mirror_relaion_type,
       --
       pcr.cont_attribute1 Disabled,
       pcr.cont_attribute2 isZain_Employee,
       pcr.cont_attribute5 Dependancy_ceased_date
--       pcr.cont_attribute3 Dependancy_restarted_date,
--       pcr.cont_attribute6 Name_of_school,
--       pcr.cont_attribute7 School_grade,
--       pcr.cont_attribute8 School_class,
--       pcr.cont_attribute9 School_year,
--       pcr.cont_attribute10 EAP_CAP,
--       pcr.cont_attribute20 EAP_CAP_reciepient_number,
--       pcr.cont_attribute11 School_fees_paid_by_emp,
--       pcr.cont_attribute12 School_fees_paid_currency,
--       pcr.cont_attribute13 Claimed_date,
--       pcr.cont_attribute14 Residnecy_expire_date,
--       pcr.cont_attribute15 Resident_in_kuwait,
--       pcr.cont_attribute16 Diceased_date,
--       pcr.cont_attribute17 Nationaliy_group,
--     pcr.cont_attribute18 Medical_squence_Number,
--     pcr.cont_attribute19 Dependent_number
FROM
       per_all_people_f per ,
       per_periods_of_service pps,
       per_person_types ppt,
       per_contact_relationships pcr,
       hr_lookups lkp,
       per_people_f  xx
WHERE
  pcr.person_id = xx.person_id AND
  TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))) BETWEEN per.effective_start_date AND  per.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN xx.effective_start_date AND  xx.effective_end_date
  AND per.person_id = pps.PERSON_ID(+)
  AND per.person_type_Id = ppt.person_type_id(+)
  AND  lkp.lookup_type(+) = 'CONTACT'
  AND  lkp.lookup_code(+) = pcr.contact_type
  AND  pcr.CONTACT_PERSON_ID  = per.person_id
ORDER BY pcr.contact_person_id
/

Oracle HRMS Scripts (Queries) - Entry Value

SELECT   entry.assignment_id,
         asg.assignment_number,
         TYPE.element_name,
         CASE
            WHEN pec.classification_name = 'Earnings'
            THEN
               'Earnings'
            WHEN pec.classification_name = 'Supplemental Earnings'
            THEN
               'Supplemental Earnings'
            --
         WHEN INSTR (pec.classification_name, 'Deductions') >= 1
            THEN
               'Deductions'
            WHEN INSTR (pec.classification_name, 'Information') >= 1
            THEN
               'Information'
            ELSE
               pec.classification_name
         END
            classification,
         CASE
            WHEN INSTR (pec.classification_name, 'Earning') >= 1 THEN '1'
            WHEN INSTR (pec.classification_name, 'Deductions') >= 1 THEN '2'
            WHEN INSTR (pec.classification_name, 'Information') >= 1 THEN '3'
            ELSE pec.classification_name
         END
            class_sort_order,
         --
         TYPE.processing_type,
         DECODE (TYPE.processing_type, 'R', 'Recurring', 'Non Recurring')
            processing_type_meaning,
         TYPE.post_termination_rule,
         DECODE (TYPE.post_termination_rule,
                 'L',
                 'Last Standard Process',
                 'F',
                 'Final Close',
                 'A',
                 'Acutal Termination')
            termination_rule_meaning,
         TYPE.input_currency_code,
         TYPE.output_currency_code,
         inpval.uom,
         DECODE (inpval.uom,
                 'M',
                 'Money',
                 'N',
                 'Number',
                 'D',
                 'Date',
                 'ND',
                 'Day',
                 'C',
                 'Character')
            unit_of_measure,
         NVL (
            DECODE (
               inpval.uom,
               'M',
               fnd_number.canonical_to_number (VALUE.screen_entry_value),
               'N',
               fnd_number.canonical_to_number (VALUE.screen_entry_value)
            ),
            0
         )
            e_value_num,
         NVL (VALUE.screen_entry_value, '0') e_value,
         inpval.NAME value_name,
         VALUE.element_entry_id,
         inpval.effective_start_date eft_st_date_inpval,
         inpval.effective_end_date eft_ed_date_inpval,
         LINK.effective_start_date eft_st_date_link,
         LINK.effective_end_date eft_end_date_link,
         TYPE.effective_start_date eft_st_date_type,
         TYPE.effective_end_date eft_end_date_type,
         entry.effective_start_date eft_st_date_entry,
         entry.effective_end_date eft_end_date_entry,
         asg.effective_start_date eft_st_date_asg,
         asg.effective_end_date eft_end_date_asg,
         TYPE.element_type_id element_type_id
  FROM   pay_element_types_f TYPE,
         pay_element_links_f LINK,
         pay_element_entries_f entry,
         pay_element_entry_values_f VALUE,
         pay_input_values_f inpval,
         pay_element_classifications pec,
         APPS.PER_ASSIGNMENTS_F2 asg
 WHERE       TYPE.element_type_id = LINK.element_type_id
         AND entry.element_link_id = LINK.element_link_id
         --AND ENTRY.ENTRY_TYPE IN ('A', 'R')
         --AND VALUE.element_entry_id(+) = entry.element_entry_id
         AND VALUE.element_entry_id = entry.element_entry_id
         AND entry.effective_start_date BETWEEN TYPE.effective_start_date
                                            AND  TYPE.effective_end_date
         AND entry.effective_start_date BETWEEN link.effective_start_date
                                            AND  link.effective_end_date
         AND entry.effective_start_date BETWEEN inpval.effective_start_date
                                            AND  inpval.effective_end_date
         AND entry.effective_start_date BETWEEN VALUE.effective_start_date
                                            AND  VALUE.effective_end_date
         AND entry.effective_start_date BETWEEN asg.effective_start_date
                                            AND  asg.effective_end_date
         --AND VALUE.effective_start_date(+) = entry.effective_start_date
         --AND VALUE.effective_end_date(+) = entry.effective_end_date
         --AND inpval.input_value_id(+) = VALUE.input_value_id
         AND inpval.input_value_id = VALUE.input_value_id
         AND pec.classification_id = TYPE.classification_id
         AND asg.assignment_id = entry.assignment_id
         AND (UPPER (inpval.NAME) NOT LIKE '%FUTU%'
              OR inpval.NAME IS NOT NULL)
         AND assignment_number NOT LIKE 'XX%'

Oracle HRMS Query - Position

Select distinct
    PD.segment1 POSITION_NUMBER ,
    pd.segment2 POSITION_NAME ,
    pd.segment3 POSITION_GRADE ,
    (select name from hr_all_organization_units where ORGANIZATION_ID = pd.segment4) org ,
    pd.segment4 org_id,
    pd.segment5 POSITION_CODE,
    po.name position,
    j.name job ,
    po.position_id
From
    per_positions po ,
    per_jobs j ,
    hr_all_organization_units org  ,
    PER_POSITION_DEFINITIONS PD
where 1=1
AND po.BUSINESS_GROUP_ID = 81
and pd.POSITION_DEFINITION_ID = po.POSITION_DEFINITION_ID
and j.job_id   = po.job_id
and org.ORGANIZATION_ID = po.ORGANIZATION_ID
and po.position_id= 17624

Low Level Employee Supervisor Relationships

SELECT
    DISTINCT
      LPAD(' ',5*(LEVEL-1)) || PPF.FULL_NAME,
      PERA.SUPERVISOR_ID,
      PERA.PERSON_ID,
      PER_JOBS.NAME JobName,
      LEVEL,
      SYS_CONNECT_BY_PATH(pera.person_id, '/') Path
FROM PER_ASSIGNMENTS_F PERA,PER_JOBS,PER_ALL_PEOPLE_F PPF
WHERE PER_JOBS.JOB_ID = PERA.JOB_ID
    AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
    AND PPF.PERSON_ID = PERA.PERSON_ID
    AND EXISTS
      (SELECT '1'
        FROM PER_PEOPLE_F PERF, PER_ASSIGNMENTS_F PERA1
        WHERE TRUNC(SYSDATE) BETWEEN PERF.EFFECTIVE_START_DATE AND PERF.EFFECTIVE_END_DATE
        AND PERF.PERSON_ID  = PERA.SUPERVISOR_ID
        AND PERA1.PERSON_ID = PERF.PERSON_ID
        AND TRUNC(SYSDATE) BETWEEN PERA1.EFFECTIVE_START_DATE AND PERA1.EFFECTIVE_END_DATE
        AND PERA1.PRIMARY_FLAG    = 'Y'
        AND PERA1.ASSIGNMENT_TYPE = 'E'
        AND EXISTS
          (SELECT '1' FROM PER_PERSON_TYPES PPT WHERE PPT.SYSTEM_PERSON_TYPE IN ('EMP','EMP_APL') AND PPT.PERSON_TYPE_ID = PERF.PERSON_TYPE_ID)
      )
      START WITH PERA.PERSON_ID = 1623
--      (SELECT employee_id FROM fnd_user WHERE user_name = '&UserName' ) -- ** Replace DCROCKETT with your username
    AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE
    AND PERA.PRIMARY_FLAG                     = 'Y'
    AND PERA.ASSIGNMENT_TYPE                  = 'E'
  CONNECT BY NOCYCLE PRIOR PERA.PERSON_ID = PERA.SUPERVISOR_ID
    AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE
    AND PERA.PRIMARY_FLAG    = 'Y'
    AND PERA.ASSIGNMENT_TYPE = 'E'
      --AND LEVEL <= 4
ORDER BY PATH

HRMS-Employee Person Conversion

create or replace PACKAGE xx_hr_person_conv_pkg
AS
 PROCEDURE xx_hr_person_conv_proc
  (
    xc_errbuf OUT VARCHAR2,
    xc_retcode OUT VARCHAR2,
    pc_country IN VARCHAR2);
END xx_hr_person_conv_pkg;
/
create or replace PACKAGE BODY xx_hr_person_conv_pkg
AS
PROCEDURE xx_hr_person_conv_proc
  (
    xc_errbuf  OUT VARCHAR2,
    xc_retcode OUT VARCHAR2,
    pc_country IN VARCHAR2)
              IS
  -- +================================================================+
  -- | Name            : xx_hr_person_conv_proc
  -- | Type            : Procedure
  -- | Description     : Procedure to create address details for the employee
  -- | Parameters  IN  : p_country
  -- | Returns     OUT : p_err_buf
  -- |                   p_retcode
  -- +================================================================+
  lc_err_flag                  VARCHAR2(1); --if any is validation is errored then lc_err_flag ='E' else lc_err_flag='N'
  lc_err_mesg                  VARCHAR2(4000);
  ln_tot_cnt                   NUMBER DEFAULT 0;
  ln_tot_suc                   NUMBER DEFAULT 0;
  ln_tot_fal                   NUMBER DEFAULT 0;
  lc_title                     VARCHAR2(10);
  lc_middle_names              VARCHAR2(20);
  lc_hire_date                 DATE;
  lc_nationality               VARCHAR2(25);
  lc_country_of_birth          VARCHAR2(50);
  lc_i_9_status_tax               VARCHAR2(25);
  ld_i_9_expiration            DATE;
  ln_person_id                 NUMBER;
  ln_assignment_id             NUMBER;
  lc_status                    VARCHAR2(30);
  ln_year                      NUMBER;
  lc_work_schedule             VARCHAR2(30);
  ln_per_object_version_number NUMBER;
  ln_asg_object_version_number NUMBER;
  ld_per_effective_start_date  DATE;
  ld_per_effective_end_date    DATE;
  lc_full_name                 VARCHAR2 (50);
  ln_per_comment_id            NUMBER;
  ln_count_emp                 NUMBER;
  ln_assignment_sequence       NUMBER;
  ln_assignment_number         VARCHAR2(50);
  lb_name_combination_warning  BOOLEAN;
  lb_assign_payroll_warning    BOOLEAN;
  lb_orig_hire_warning         BOOLEAN;
  ln_pdp_object_version_number NUMBER;
  ln_comment_id                NUMBER;
  lc_gender                    VARCHAR2(10);
  lc_person_type_id            NUMBER;
  ln_business_group_id         NUMBER;
  lc_system_person_type        VARCHAR2(100);
  lc_registered_disabled       VARCHAR2(50);
  lc_email_address             VARCHAR2(50);
  lc_date_of_birth             VARCHAR2(50);
  lc_f_date_of_birth           VARCHAR2(50);
  ld_ethnic_origin             NUMBER;
  lc_ethnicity_disclosed       VARCHAR2(10);
  lc_prefix                    VARCHAR2(50);
  lc_suffix                    VARCHAR2(50);
  lc_ssn                       VARCHAR2(50);
  CURSOR cur_xx_person-- select columns which needs to be import.
  IS
     SELECT XHECS.rowid
          , XHECS.*
     FROM   xx.xx_hr_emp_create_stg XHECS
     WHERE  NVL(process_flag,'N') <> 'Y'
     AND    country_code  = pc_country
     AND    employee_number  IS NOT NULL;
  CURSOR cur_xx_person_err
  IS
     SELECT *
     FROM xx.xx_hr_emp_create_stg
     WHERE process_flag ='E'
     AND country_code   = pc_country;
BEGIN
  FOR xx_person_cur_rec IN cur_xx_person
  LOOP
    ln_tot_cnt  := ln_tot_cnt + 1;
    lc_err_mesg := NULL;
    lc_err_flag := NULL;
    --*****************************VALIDATIONS*******************************
    -- ------------------------------------------------------------
    --         Deriving  Business_Group_id
    -- ------------------------------------------------------------
    IF (xx_person_cur_rec.Business_Group_Name IS NULL) THEN
      lc_err_flag                                 := 'E';
      lc_err_mesg                                 := lc_err_mesg||'Business Group Name should not be NULL';
    ELSE
      BEGIN
         SELECT business_group_id
         INTO   ln_business_group_id
         FROM   per_business_groups
         WHERE  name = xx_person_cur_rec.business_group_name;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'No business_group_id for Business_Group_id' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'Error fetching Business_Group_id' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      END;
    END IF;
    FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Business Group Id is : ' || ln_business_group_id);
    ------------------------------------------------------------
    --         Validating  Employee Number
    -- ------------------------------------------------------------
    BEGIN
       SELECT COUNT(1)
       INTO  ln_count_emp
       FROM  per_all_people_f
       WHERE employee_number=xx_person_cur_rec.employee_number
       AND   business_group_id  = ln_business_group_id;

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee_number is  : ' || xx_person_cur_rec.employee_number);
      FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee ln_count_emp is  : ' || ln_count_emp);
      IF ln_count_emp >=1 THEN
        lc_err_flag   := 'E';
        lc_err_mesg   := lc_err_mesg||xx_person_cur_rec.employee_number||'Employee Number allready exists in the system';
      END IF;
    END;
    ------------------------------------------------------------
    --         Validating Last Name of the Employee
    -- ------------------------------------------------------------
    BEGIN
      IF (xx_person_cur_rec.last_name IS NULL) THEN
        lc_err_flag                       := 'E';
        lc_err_mesg                       := lc_err_mesg||'Last Name should not be NULL';
      END IF;
    END;
    --   FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee last_name is : ' || xx_person_cur_rec.last_name);
    -- ------------------------------------------------------------
    --         Validating Gender Lookup code
    -- ------------------------------------------------------------
    IF (xx_person_cur_rec.sex IS NULL) THEN
      lc_err_flag                 := 'E';
      lc_err_mesg                 := lc_err_mesg||'Gender Cannot be  NULL';
    ELSE
      BEGIN
        SELECT DISTINCT lookup_code
        INTO   lc_gender
        FROM   fnd_lookup_values
        WHERE  upper(meaning) = upper(trim(REPLACE(REPLACE(REPLACE(xx_person_cur_rec.sex,CHR(10)), CHR(13)), CHR(9))))
        AND    lookup_type        = 'SEX'
        AND    enabled_flag       = 'Y';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'No Lookup Code found for the Gender' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'Error fetching Gender' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      END;
    END IF;
    -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Gender is : ' || lc_gender);
    ------------------------------------------------------------
    --         Validating Hire Date
    -- ------------------------------------------------------------
    BEGIN
      IF (xx_person_cur_rec.hire_date IS NULL) THEN
        lc_err_flag                       := 'E';
        lc_err_mesg                       := lc_err_mesg||'Hire Date Cannot be NULL';
      ELSE
         SELECT xx_person_cur_rec.hire_date
         INTO   lc_hire_date
         FROM   DUAL;
      END IF;
    END;
    --   FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Hire Date Is : ' || lc_hire_date);
    -- --------------------------------------------------------
    --         Validating Marital Status Lookup code
    -- ------------------------------------------------------------
    IF xx_person_cur_rec.marital_status IS NULL THEN
      lc_status                             := NULL;
      FND_FILE.PUT_LINE(FND_FILE.LOG,'Marital status is null');
    ELSE
      BEGIN
         SELECT DISTINCT lookup_code
         INTO   lc_status
         FROM   fnd_lookup_values
         WHERE  meaning  = xx_person_cur_rec.marital_status
         AND    lookup_type  = 'MARITAL_STATUS'
         AND    enabled_flag = 'Y';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'No Lookup Code Found for the MARITAL STATUS' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'No lookup code found');
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'Error fetching MARITAL_STATUS' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      END;
    END IF;
    -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Marital status Is : ' || lc_status);
    -- --------------------------------------------------------
    --         Validating country of birth  Lookup code
    -- ------------------------------------------------------------
    IF xx_person_cur_rec.country_of_birth IS NULL THEN
      lc_country_of_birth                     := NULL;
    ELSE
      BEGIN
        SELECT DISTINCT territory_code
        INTO   lc_country_of_birth
        FROM   fnd_territories_tl
        WHERE  upper(territory_short_name) = upper(xx_person_cur_rec.country_of_birth);
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'No territory_code found for country of birth' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'Error fetching country of birth' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      END;
    END IF;
    --   FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee country_of_birth: ' || lc_country_of_birth);
    --------------------------------------------------------
    --         Validating Nationality  Lookup code
    -- ------------------------------------------------------------
    IF xx_person_cur_rec.nationality IS NULL THEN
      lc_nationality                     := NULL;
    ELSE
      BEGIN
        SELECT DISTINCT lookup_code
        INTO   lc_nationality
        FROM   hr_lookups
        WHERE  meaning  = xx_person_cur_rec.nationality
        AND    lookup_type  = 'NATIONALITY'
        AND    enabled_flag = 'Y';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'No Data found for the Nationality' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := 'Error fetching Nationality' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      END;
    END IF;
    FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee nationality is : ' || lc_nationality);
    --------------------------------------------------------
    --         Validating TITLE  Lookup code
    -- ------------------------------------------------------------
    IF xx_person_cur_rec.title IS NULL THEN
      lc_title                     := NULL;
    ELSE
      BEGIN
        SELECT DISTINCT lookup_code
        INTO   lc_title
        FROM   hr_lookups
        WHERE  meaning  = xx_person_cur_rec.Title
        AND    lookup_type  = 'TITLE'
        AND    enabled_flag = 'Y';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'No Data Found For the Title' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'Error fetching Title' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      END;
    END IF;
    --    FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee title is : ' || lc_title);
    --------------------------------------------------------
    --         Validating Registerd Disabled
    -- ------------------------------------------------------------
    IF xx_person_cur_rec.registered_disabled IS NULL THEN
      lc_registered_disabled                     := NULL;
    ELSE
      BEGIN
        SELECT DISTINCT lookup_code
        INTO   lc_registered_disabled
        FROM   hr_lookups
        WHERE  meaning  = xx_person_cur_rec.registered_disabled
        AND    lookup_type  = 'REGISTERED_DISABLED'
        AND    enabled_flag = 'Y';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'No Data Found For the Registerd Disabled' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'Error fetching lookup code for Title Registerd Disabled' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      END;
    END IF;
    --  FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee REGISTERED_DISABLED is : ' || lc_registered_disabled);
    --------------------------------------------------------
    --         Validating XX Email Address
    -- ------------------------------------------------------------
    BEGIN
      IF xx_person_cur_rec.email_address IS NULL THEN
        lc_email_address                     := NULL;
      ELSE
        lc_email_address := xx_person_cur_rec.email_address;
      END IF;
    END;
    --  FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Email Address is : ' || lc_email_address);
    --------------------------------------------------------
    --         Validating Date Of Birth
    -- ------------------------------------------------------------
    BEGIN
      IF xx_person_cur_rec.date_of_birth IS NULL THEN
        lc_f_date_of_birth                     := NULL;
      ELSE
        --lc_date_of_birth := xx_person_cur_rec.date_of_birth;
         SELECT TO_CHAR(TO_DATE(xx_person_cur_rec.date_of_birth,'DD-MON-RRRR'),'DD-MON-YYYY')
         INTO   lc_date_of_birth
         FROM   DUAL;

        FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee date_of_birth is : ' || lc_date_of_birth);
         SELECT TO_CHAR(TO_DATE(xx_person_cur_rec.date_of_birth,'DD-MON-RRRR'),'YY')
         INTO   ln_year
         FROM   DUAL ;

        FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Full Year is : ' || ln_year);
        IF ln_year < 50 THEN
           SELECT TO_CHAR(TO_DATE(lc_date_of_birth,'DD-MON-RRRR'),'DD-MON-')
            ||'19'
            || ln_year
           INTO lc_f_date_of_birth
           FROM DUAL;

          FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Converted Date : ' || lc_f_date_of_birth);
        ELSE
          lc_f_date_of_birth:= lc_date_of_birth;
          FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Full Date Of Birth : ' || lc_f_date_of_birth);
        END IF;
      END IF;
    END;
    --------------------------------------------------------
    --         Validating Ethnic Of Origin For US
    -- ------------------------------------------------------------
    IF xx_person_cur_rec.Business_Group_Name = 'XXUS' AND xx_person_cur_rec.ethinic_origin IS NOT NULL THEN
      BEGIN
        SELECT DISTINCT lookup_code
        INTO   ld_ethnic_origin
        FROM   hr_lookups
        WHERE  upper(meaning)= upper(xx_person_cur_rec.ethinic_origin)
        AND    lookup_type       = 'US_ETHNIC_GROUP'
        AND    enabled_flag      = 'Y';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'No Data Found For the ethnic origin ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'Error fetching lookup code for ethnic origin ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      END;
    ELSIF xx_person_cur_rec.ethinic_origin IS NULL THEN
      ld_ethnic_origin                         := NULL;
    END IF;
    --  FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee ethnic_origin is : ' || ld_ethnic_origin);
    ---------------------------------------------------------------
    --         Validating Ethnicity Disclosed
    -- ------------------------------------------------------------
    IF xx_person_cur_rec.ethnicity_disclosed IS NULL THEN
      lc_ethnicity_disclosed                     := NULL;
    ELSE
      BEGIN
         SELECT DECODE(xx_person_cur_rec.ethnicity_disclosed ,'Yes','Y','No','N')
         INTO   lc_ethnicity_disclosed
         FROM   DUAL;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'No data Found For the ethnicity disclosed' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'Error fetching lookup code for ethnicity disclosed ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
      END;
    END IF;
    --   FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee ethnicity_disclosed is : ' || lc_ethnicity_disclosed);
    --------------------------------------------------------
    --         Validating I9 Status For US
    -- ------------------------------------------------------------
      BEGIN
        IF xx_person_cur_rec.business_group_name = 'XXUS' AND xx_person_cur_rec.i_9_status IS NOT NULL THEN
          BEGIN
            SELECT DISTINCT lookup_code
            INTO   lc_i_9_status_tax
            FROM   hr_lookups
            WHERE  upper(meaning)= upper(xx_person_cur_rec.i_9_status )
            AND    lookup_type       = 'PER_US_I9_STATE'
            AND    enabled_flag      = 'Y';
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
            lc_err_flag := 'E';
            lc_err_mesg := lc_err_mesg||'No Look Up Found For the I-9 Status ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
          WHEN OTHERS THEN
            lc_err_flag := 'E';
            lc_err_mesg := lc_err_mesg||'Error fetching lookup code For the I-9 Status ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
          END;
        END IF;

        IF xx_person_cur_rec.business_group_name = 'XXAU'  AND xx_person_cur_rec.payroll_state_tax  IS NOT NULL THEN
          BEGIN
            SELECT DISTINCT lookup_code
            INTO   lc_i_9_status_tax
            FROM   fnd_common_lookups
            WHERE  upper(lookup_code)= upper(xx_person_cur_rec.payroll_state_tax )
            AND    lookup_type       = 'AU_STATE'
            AND    enabled_flag      = 'Y';
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
            lc_err_flag := 'E';
            lc_err_mesg := lc_err_mesg||'No Look Up Found For payroll_state_tax in AU_STATE Lookup ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
          WHEN OTHERS THEN
            lc_err_flag := 'E';
            lc_err_mesg := lc_err_mesg||'Error fetching lookup code For payroll_state_tax in AU_STATE Lookup ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
          END;
        END IF;

    END;

    --    FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee lc_i_9_status is : ' || lc_i_9_status);
    -----------------------------------------------------------
    --         Validating I9 Expiration
    -- ------------------------------------------------------------
    BEGIN
      IF xx_person_cur_rec.i_9_expiration IS NULL THEN
        ld_i_9_expiration                     := NULL;
      ELSE
        ld_i_9_expiration := xx_person_cur_rec.i_9_expiration;
      END IF;
    END;
    BEGIN
      IF xx_person_cur_rec.prefix IS NULL THEN
        lc_prefix                     := NULL;
      ELSE
        lc_prefix := xx_person_cur_rec.prefix;
      END IF;
    END;
    --  FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee prefix is : ' || lc_prefix);
    --------------------------------------------------------
    --         Validating Suffix
    -- ------------------------------------------------------------
    BEGIN
      IF xx_person_cur_rec.suffix IS NULL THEN
        lc_suffix                     := NULL;
      ELSE
        lc_suffix := xx_person_cur_rec.suffix;
      END IF;
    END;
    --   FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee suffix is : ' || lc_suffix);
    --------------------------------------------------------
    --         Validating Middle Name
    -- ------------------------------------------------------------
    BEGIN
      IF xx_person_cur_rec.middle_name IS NULL THEN
        lc_middle_names                    := NULL;
      ELSE
        lc_middle_names := xx_person_cur_rec.middle_name;
      END IF;
    END;
    --    FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee middle_name is : ' || lc_middle_names);
    --------------------------------------------------------
    --         Validating SSN Number
    -- ------------------------------------------------------------
    BEGIN
      IF xx_person_cur_rec.ssn                   IS NULL THEN
        lc_ssn                                       := NULL;
        ELSIF xx_person_cur_rec.business_group_name = 'XXCA' THEN
        BEGIN
                   SELECT SUBSTR(xx_person_cur_rec.ssn,1,3)
                    ||' '
                    || SUBSTR(xx_person_cur_rec.ssn,4,3)
                    ||' '
                    || SUBSTR(xx_person_cur_rec.ssn,7)ssn
                   INTO lc_ssn
                   FROM DUAL ;
                EXCEPTION
                WHEN OTHERS THEN
                  lc_err_flag := 'E';
                  lc_err_mesg := lc_err_mesg||'Exception fetching ssn code For Canada ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
        END;

      ELSE
        lc_ssn := xx_person_cur_rec.ssn;
      END IF;
    END;
    FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee lc_ssn is : ' || lc_ssn);

   --------------------------------------------------------
   --         Validating Work Schedule
   -- ------------------------------------------------------------
    BEGIN

            IF xx_person_cur_rec.work_schedule IS NULL THEN
               lc_work_schedule := NULL;
            ELSE
               BEGIN
                 SELECT DISTINCT lookup_code
                 INTO   lc_work_schedule
                 FROM   fnd_common_lookups
                 WHERE  upper(meaning)= upper(xx_person_cur_rec.work_schedule)
                 AND    lookup_type       = 'WORK_SCHEDULE'
                 AND    enabled_flag      = 'Y';
              EXCEPTION
              WHEN NO_DATA_FOUND THEN
                lc_err_flag := 'E';
                lc_err_mesg := lc_err_mesg||'No Look Up Found For the WORK SCHEDULE ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
              WHEN OTHERS THEN
                lc_err_flag := 'E';
                lc_err_mesg := lc_err_mesg||'Error fetching lookup code For the WORK SCHEDULE ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
              END;
        END IF;
    END;
    BEGIN
      SELECT DISTINCT person_type_id
             ,system_person_type
      INTO   lc_person_type_id,
             lc_system_person_type
      FROM   per_person_types
      WHERE  user_person_type =xx_person_cur_rec.person_type_name
      AND    system_person_type   IN ('EMP','CWK')
      AND    business_group_id    =ln_business_group_id;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      lc_err_flag := 'E';
      lc_err_mesg := lc_err_mesg||'No data Found For This Person Type ' ||xx_person_cur_rec.person_type_name|| TO_CHAR(SQLCODE)||'-'||SQLERRM;
    WHEN OTHERS THEN
      lc_err_flag := 'E';
      lc_err_mesg := lc_err_mesg||'Exception fetching lookup code for Person Type '||xx_person_cur_rec.person_type_name || TO_CHAR(SQLCODE)||'-'||SQLERRM;
    END;
    IF lc_system_person_type <> 'EMP' AND lc_system_person_type <> 'CWK' THEN
      lc_err_flag            := 'E';
      lc_err_mesg            := lc_err_mesg||'Error Person Type is Not Employee or Contingent Worker' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
    END IF;
    IF NVL(lc_err_flag,'N') <>'E' AND lc_system_person_type = 'EMP' THEN
      BEGIN
        HR_EMPLOYEE_API.CREATE_EMPLOYEE
        (p_validate                       =>false
        ,p_hire_date                      =>TRUNC(lc_hire_date)
        ,p_business_group_id              =>ln_business_group_id
        ,p_last_name                      =>xx_person_cur_rec.last_name
        ,p_sex                            =>lc_gender
        ,p_person_type_id                 =>lc_person_type_id
        ,p_per_comments                   =>NULL
        ,p_date_employee_data_verified    =>NULL
        ,p_date_of_birth                  =>TRUNC(to_date(lc_f_date_of_birth,'DD-MON-RRRR'))
        ,p_email_address                  =>lc_email_address
        ,p_employee_number                =>xx_person_cur_rec.employee_number
        ,p_expense_check_send_to_addres   =>NULL
        ,p_first_name                     =>xx_person_cur_rec.first_name
        ,p_known_as                       =>xx_person_cur_rec.preferred_name
        ,p_marital_status                 =>lc_status
        ,p_middle_names                   =>lc_middle_names
        ,p_nationality                    =>lc_nationality
        ,p_national_identifier            =>lc_ssn
        ,p_previous_last_name             =>NULL
        ,p_registered_disabled_flag       =>lc_registered_disabled
        ,p_title                          =>lc_title
        ,p_vendor_id                      =>NULL
        ,p_work_telephone                 =>NULL
        ,p_coord_ben_no_cvg_flag          =>'N'
        ,p_dpdnt_vlntry_svce_flag         =>'N'
        ,p_attribute_category             =>NULL--lc_attribute_category
        ,p_attribute1                     =>NULL--lc_benefit_number
        ,p_attribute2                     =>NULL--lc_personal_email_address
        ,p_attribute3                     =>NULL--lc_include_in_headcount
        ,p_attribute4                     =>NULL--NVL(xx_person_cur_rec.individual_tax_payor_number,'')
        ,p_attribute5                     =>NULL--NVL(xx_person_cur_rec.ctps_number ,'')
        ,p_attribute6                     =>NULL--NVL(xx_person_cur_rec.first_job ,'')
        ,p_attribute7                     =>NULL--NVL(xx_person_cur_rec.social_integration_program ,'')
        ,p_attribute8                     =>NULL--NVL(xx_person_cur_rec.income_tax_number ,'')
        ,p_attribute9                     =>NULL--NVL(xx_person_cur_rec.voters_card_number ,'')
        ,p_attribute10                    =>NULL
        ,p_attribute11                    =>NULL
        ,p_attribute12                    =>NULL
        ,p_attribute13                    =>NULL
        ,p_attribute14                    =>NULL
        ,p_attribute15                    =>NULL
        ,p_attribute16                    =>NULL
        ,p_attribute17                    =>NULL
        ,p_attribute18                    =>NULL
        ,p_attribute19                    =>NULL
        ,p_attribute20                    =>NULL
        ,p_attribute21                    =>NULL
        ,p_attribute22                    =>NULL
        ,p_attribute23                    =>NULL
        ,p_attribute24                    =>NULL
        ,p_attribute25                    =>NULL
        ,p_attribute26                    =>NULL
        ,p_attribute27                    =>NULL
        ,p_attribute28                    =>NULL
        ,p_attribute29                    =>NULL
        ,p_attribute30                    =>NULL
        ,p_per_information_category       =>NULL
        ,p_per_information1               =>ld_ethnic_origin
        ,p_per_information2               =>lc_i_9_status_tax
        ,p_per_information3               =>to_char(ld_i_9_expiration,'RRRR-MON-DD')
        ,p_per_information7               =>NULL
        ,p_per_information11              =>lc_ethnicity_disclosed
        ,p_pre_name_adjunct               =>lc_prefix
        ,p_suffix                         =>lc_suffix
        ,p_benefit_group_id               =>NULL
        ,p_receipt_of_death_cert_date     =>NULL
        ,p_coord_ben_med_pln_no           =>NULL
        ,p_coord_ben_med_ext_er           =>NULL
        ,p_coord_ben_med_pl_name          =>NULL
        ,p_coord_ben_med_insr_crr_name    =>NULL
        ,p_coord_ben_med_insr_crr_ident   =>NULL
        ,p_coord_ben_med_cvg_strt_dt      =>NULL
        ,p_coord_ben_med_cvg_end_dt       =>NULL
        ,p_uses_tobacco_flag              =>NULL
        ,p_dpdnt_adoption_date            =>NULL
        ,p_original_date_of_hire          =>NULL
        ,p_adjusted_svc_date              =>NULL
        ,p_town_of_birth                  =>NVL(xx_person_cur_rec.town_of_birth ,'')
        ,p_region_of_birth                =>NULL
        ,p_country_of_birth               =>lc_country_of_birth
        ,p_global_person_id               =>NULL
        ,p_party_id                       =>NULL
        ,p_work_schedule                  =>lc_work_schedule
        ,p_person_id                      =>ln_person_id
        ,p_assignment_id                  =>ln_assignment_id
        ,p_per_object_version_number      =>ln_per_object_version_number
        ,p_asg_object_version_number      =>ln_asg_object_version_number
        ,p_per_effective_start_date       =>ld_per_effective_start_date
        ,p_per_effective_end_date         =>ld_per_effective_end_date
        ,p_full_name                      =>lc_full_name
        ,p_per_comment_id                 =>ln_per_comment_id
        ,p_assignment_sequence            =>ln_assignment_sequence
        ,p_assignment_number              =>ln_assignment_number
        ,p_name_combination_warning       =>lb_name_combination_warning
        ,p_assign_payroll_warning         =>lb_assign_payroll_warning
        ,p_orig_hire_warning              =>lb_orig_hire_warning );
        COMMIT;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Inserted  is : ' || xx_person_cur_rec.employee_number);
         UPDATE xx_hr_emp_create_stg
         SET   process_flag    = 'Y'
                ,message  = NVL(lc_err_mesg,NULL)
         WHERE  employee_number = xx_person_cur_rec.employee_number
         AND   rowid           = xx_person_cur_rec.rowid ;

        ln_tot_suc := ln_tot_suc + 1;
        COMMIT;
      EXCEPTION
      WHEN OTHERS THEN
        lc_err_flag := 'E';
        lc_err_mesg := lc_err_mesg||'Exception when calling API of Employee:' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
         UPDATE xx_hr_emp_create_stg
         SET    process_flag    = 'E'
                ,message  = NVL(lc_err_mesg,NULL)
         WHERE  employee_number = xx_person_cur_rec.employee_number
         AND   rowid           = xx_person_cur_rec.rowid ;

        ln_tot_fal := ln_tot_fal + 1;
      END;
    ELSIF NVL(lc_err_flag,'N') <>'E' AND lc_system_person_type = 'CWK' THEN
      BEGIN
              HR_CONTINGENT_WORKER_API.CREATE_CWK
              (p_validate                  =>false
              ,p_start_date                =>TRUNC(lc_hire_date)
              ,p_business_group_id         =>ln_business_group_id
              ,p_last_name                 =>xx_person_cur_rec.last_name
              ,p_person_type_id            =>lc_person_type_id
              ,p_npw_number                =>xx_person_cur_rec.employee_number
              ,p_date_of_birth             =>TRUNC(to_date(lc_f_date_of_birth,'DD-MON-RRRR'))
              ,p_date_of_death             =>NULL
              ,p_dpdnt_adoption_date       =>NULL
              ,p_dpdnt_vlntry_svce_flag    =>NULL
              ,p_email_address             =>lc_email_address
              ,p_first_name                =>xx_person_cur_rec.first_name
              ,p_known_as                  =>xx_person_cur_rec.preferred_name
              ,p_middle_names              =>lc_middle_names
              ,p_national_identifier       =>lc_ssn
              ,p_registered_disabled_flag  =>lc_registered_disabled
              ,p_resume_exists             =>NULL
              ,p_resume_last_updated       =>NULL
              ,p_second_passport_exists    =>NULL
              ,p_sex                       =>lc_gender
              ,p_title                     =>lc_title
              ,p_work_telephone            =>NULL
              ,p_town_of_birth             =>NVL(xx_person_cur_rec.town_of_birth ,'')
              ,p_country_of_birth          =>lc_country_of_birth
              ,p_person_id                 =>ln_person_id
              ,p_per_object_version_number =>ln_per_object_version_number
              ,p_per_effective_start_date  =>ld_per_effective_start_date
              ,p_per_effective_end_date    =>ld_per_effective_end_date
              ,p_pdp_object_version_number =>ln_pdp_object_version_number
              ,p_full_name                 =>lc_full_name
              ,p_comment_id                =>ln_comment_id
              ,p_assignment_id             =>ln_assignment_id
              ,p_asg_object_version_number =>ln_asg_object_version_number
              ,p_assignment_sequence       =>ln_assignment_sequence
              ,p_assignment_number         =>ln_assignment_number
              ,p_name_combination_warning  =>lb_name_combination_warning
              );
              COMMIT;
             FND_FILE.PUT_LINE(FND_FILE.LOG,'Contigent Worker Inserted  is : ' || xx_person_cur_rec.employee_number);
               UPDATE xx_hr_emp_create_stg
               SET    process_flag    = 'Y'
                     ,message = NVL(lc_err_mesg,NULL)
               WHERE  employee_number = xx_person_cur_rec.employee_number
               AND    rowid           = xx_person_cur_rec.rowid ;

              ln_tot_suc := ln_tot_suc + 1;
              COMMIT;
            EXCEPTION
            WHEN OTHERS THEN
              lc_err_flag := 'E';
              lc_err_mesg := lc_err_mesg||'Exception when calling API of Contigent Worker:' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
               UPDATE xx_hr_emp_create_stg
               SET    process_flag    = 'E'
                      ,message = NVL(lc_err_mesg,NULL)
               WHERE  employee_number = xx_person_cur_rec.employee_number
               AND    rowid           = xx_person_cur_rec.rowid ;

             ln_tot_fal := ln_tot_fal + 1;
      END;
    ELSE
      ln_tot_fal  := ln_tot_fal + 1;
      lc_err_flag := 'E';
      lc_err_mesg := lc_err_mesg||'Error : Inserting Person Details - ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
       UPDATE xx_hr_emp_create_stg
       SET    process_flag        = 'E'
             ,message = NVL(lc_err_mesg,'Error')
       WHERE  employee_number = xx_person_cur_rec.employee_number
       AND    rowid           = xx_person_cur_rec.rowid ;
      COMMIT;
    END IF;
  END LOOP;
  FND_FILE.PUT_LINE(FND_FILE.LOG,'*****************************************************************************************');
  FND_FILE.PUT_LINE(FND_FILE.LOG,'*****************PERSON DETAILS UPLOAD REPORT*********************************');
  FND_FILE.PUT_LINE(FND_FILE.LOG,'***************************************************************');
  FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Numbers of Records Read        : ' || ln_tot_cnt);
  FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Numbers of Records Inserted    : ' || ln_tot_suc);
  FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Numbers of Records Errored Out : ' || ln_tot_fal);
  FND_FILE.PUT_LINE(FND_FILE.LOG,'*****************************************************************************************');
  FND_FILE.PUT_LINE(FND_FILE.LOG,'ERRORED OUT EMPLOYEE RECORDS ');
  FOR cur_err_rec IN cur_xx_person_err
  LOOP
    FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Number : '||cur_err_rec.employee_number ||': '||cur_err_rec.message);
  END LOOP;
  COMMIT;
END xx_hr_person_conv_proc;
END xx_hr_person_conv_pkg;