Oracle HRMS Query to Get Latest Basic Salary Change Date

SELECT ppps.proposed_salary_n from per_pay_proposals ppps
where paaf.assignment_id = ppps.assignment_id
AND ppps.last_change_date is not null
and ppps.change_date = (SELECT  TO_CHAR(max(ppp.change_date),'DD-MON-RRRR') as change_effective_date
from per_pay_proposals ppp
where 1=1
and paaf.assignment_id = ppp.assignment_id

AND last_change_date is not null )

Oracle HRMS Query for Grade and Salary History

Oracle HRMS Query to Get Previous Grade

SELECT
pg_old.name
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and  trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id
and paaf_new.effective_start_date =(select
((to_char(max(paaf_new.effective_start_date),'DD-MON-RRRR')))
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and  trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id )

Query to get Absence Detail Employee wise. Approved Absence details.

SELECT papf.EMPLOYEE_NUMBER
,papf.FULL_NAME
,pat.NAME absence_type
, paa.date_start leave_start_date
,paa.date_end leave_end_date
, paa.absence_days
, paa.abs_information_category
,paa.abs_information2
, paa.abs_information3
,paa.abs_information4
FROM per_absence_attendances paa,
per_absence_attendance_types pat,
per_all_people_f papf
WHERE paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND papf.person_id = paa.person_id
AND TRUNC (paa.date_start) BETWEEN TRUNC(papf.effective_start_date)
AND TRUNC (papf.effective_end_date)
AND papf.EMPLOYEE_NUMBER=nvl(:p_employee_number,papf.EMPLOYEE_NUMBER)
order by papf.EMPLOYEE_NUMBER,paa.date_start

Query: Salary Management

select
    ppf.employee_number                 "Employee Number",
    --
    ppf.full_name                         "Full Name",
    psf.assignment_number                 "Assignment Number",
    ppb.name                              "Salary basis",
    ppp.change_date                       "Approved Date",
    ppp.proposed_salary_n                 "Approved Salary",
    pro1.change_date                      "Change Date",
    pro1.proposed_salary_n                "Proposed Salary",
    proposal_reason                       "Proposal Reason",
    pppc.change_percentage                "COL %",
    pppc.change_amount_n                  "COL Amount",
    psf.employment_category               "Assignment Category",           -- lookup;EMP_CAT
    --
    ppf.person_id,
    ppb.pay_basis_id
from
     per_all_people_f ppf,
     per_assignments_f psf,
     per_pay_bases ppb,
      per_pay_proposals pro1,
     (select ppp.change_date,
                  ppp.proposed_salary_n,
                  ppp.approved,
                  ppp.assignment_id
             from per_pay_proposals ppp
            where approved = 'Y')  ppp,
    per_pay_proposal_components pppc        
where
    1=1
and ppf.person_id = psf.person_id
and psf.pay_basis_id = ppb.pay_basis_id
and psf.assignment_id = pro1.assignment_id(+)
and pro1.approved(+) = 'N'
and psf.assignment_id = ppp.assignment_id(+)
and (ppp.change_date is null
     or ppp.change_date = (select max (change_date)
                        from per_pay_proposals pro3
                        where ppp.assignment_id = pro3.assignment_id
                        and pro3.approved = 'Y'))
and pro1.pay_proposal_id = pppc.pay_proposal_id(+)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between psf.effective_start_date and psf.effective_end_date
--
and ppf.employee_number = '1309'
order by
    1,2;

Query: Job Extra Informations

select
    pjt.name                           "Name",
    --
    pjei.jei_information_category           "Type",
    decode (pjei.jei_information_category,
       'Job Category',pjei.jei_information1||'.'||pjei.jei_information2||'.'||pjei.jei_information3||'.'
                ||pjei.jei_information4|| '.'|| pjei.jei_information5|| '.'|| pjei.jei_information6,
       'MX_SS_SALARY_TYPE', pjei.jei_information1,
       'PER_SUCCESSION_PLANNING', pjei.jei_information2|| '.'|| pjei.jei_information1,
       null)                      "Details",
    --
    pj.job_id
from
    per_job_extra_info pjei,
    per_jobs pj,
    per_jobs_tl pjt
where
    1=1
and pjei.job_id = pj.job_id
and pj.job_id = pjt.job_id
and pjt.language = userenv('LANG')
--
and pjt.name like 'ACC400%'
order by
    1;

Query: Position Valid Grades

select
    hapf.name                           "Position Name",
    --
    pgl.name                            "Grade",
    pg.date_from                        "Date From",
    --
    pg.date_to                          "Date To",
    hapf.position_id
from
    per_valid_grades pvg,
    per_grades pg,
    per_grades_tl pgl,
    hr_all_positions_f hapf
where 1=1
and pgl.grade_id = pg.grade_id
and pgl.language = userenv('LANG')
and pg.grade_id = pvg.grade_id
and pvg.position_id = hapf.position_id
--
and hapf.name like '1001.Operations Vice President';

Query: Grade Rates

select
    pr.name                       "Rate",
    --
    pgt.name                      "Grade Name",
    fcv.name                      "Currency",
    pgr.value                     "Value",
    pgr.minimum                   "Minimum",
    pgr.maximum                   "Maximum",
    pgr.mid_value                 "Mid Value",
    pgr.effective_start_date      "Eff: Dates From",
    pgr.effective_end_date        "Eff: Dates To",
    --
    pgt.grade_id,
    pr.rate_id  
from
    per_grades_tl pgt,
    pay_grade_rules_f pgr,
    pay_rates pr    ,
    fnd_currencies_vl fcv  
where
    1=1
and pgt.grade_id(+) = pgr.grade_or_spinal_point_id
and pr.rate_id = pgr.rate_id
and fcv.currency_code(+) = pgr.currency_code
--
and pr.name = 'Standard Hourly Rates'
order by
    1,2,3,4

Query: Grade Pay Scales

select
    pps.name                            "Name",
    --
    pps.last_automatic_increment_date   "Last Auto Increment",
    pps.increment_frequency             "Increment Freq:Number",
    pps.increment_period                "Increment Freq:Period",
    psp.spinal_point                    "Point",
    psp.sequence                        "Sequence",
    --
    pps.parent_spine_id
from
    per_parent_spines pps,
    per_spinal_points psp
where
    1=1
and pps.parent_spine_id = psp.parent_spine_id
--
and pps.name = 'Hrly Pay Scale:Welder /Laborer'
order by
    1,2,3;

Query: Grade Scale Rates

select
    pgtl.name                   "Grade Name" ,
    --
    pps.name                    "Pay Scale Name",
    pspsf.sequence              "Ceiling: Step",
    grs.starting_step           "Starting Step",
    grs.effective_start_date    "Effective Start Date",
    rownum                      "Step",
    psp.spinal_point            "Point",
    --
    pgtl.grade_id
from
    per_grades_tl pgtl,
    per_grade_spines_f grs,
    per_parent_spines pps,
    per_spinal_points psp,
    per_spinal_point_steps_f pspsf
where
    1=1
and grs.grade_id = pgtl.grade_id
and grs.parent_spine_id = pps.parent_spine_id
and psp.parent_spine_id = pps.parent_spine_id
AND grs.ceiling_step_id = pspsf.step_id
--
and  pgtl.name = '200.2.Central'
order by
    1,2,5,6;

Query: People Assignment Salary - Current

select
    ppf.employee_number                                             "Employee Number",
    --
    ppp.change_date                                                 "From",
    ppp.proposed_salary_n                                           "Salary",
    (ppp.proposed_salary_n*12)                                      "Annual Salary",
    (ppp.proposed_salary_n*12)                                      "FT Annual Salary",
    per_saladmin_utility.get_fte_factor (ppp.assignment_id,ppp.change_date)     "FTE Factor",  
    hr_general.decode_lookup ('PER_SAL_PROPOSAL_STATUS', approved)  "Status",
    ppp.next_perf_review_date                                       "Next Salary Review",
    hr_general.decode_lookup ('PROPOSAL_REASON', proposal_reason)   "Reason For Change",
    ppp.date_to                                                     "To",
    --
    ppp.pay_proposal_id,
    ppp.assignment_id,
    ppp.event_id,
    ppp.business_group_id
    --
from
    per_pay_proposals ppp,
    per_performance_reviews ppr,
    per_assignments_f2 paa,
    per_people_f ppf,
    per_pay_bases ppb,
    pay_input_values_f piv,
    pay_element_types_f pet  
where  
    1=1
and ppp.performance_review_id =ppr.performance_review_id(+)
and ppp.assignment_id = paa.assignment_id
and paa.person_id = ppf.person_id
and sysdate between ppp.change_date and nvl (ppp.date_to, hr_general.end_of_time)
and ppp.change_date between paa.effective_start_date and paa.effective_end_date
and paa.pay_basis_id(+) = ppb.pay_basis_id
and ppb.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and ppp.change_date between piv.effective_start_date and piv.effective_end_date
and ppp.change_date between pet.effective_start_date and pet.effective_end_date
--
and ppf.employee_number = '1309'
order by
   1,2,3;

Query: People

select
    ppf.employee_number       "Employee Number",
--
    ppf.last_name             "Last Name",
    ppf.first_name            "First Name",
    ppf.title                 "Title",
    ppf.pre_name_adjunct      "Prefix",
    ppf.suffix                "Suffix",
    ppf.middle_names          "Middle",
    decode(ppf.sex,
           'M','Male',
           'F','Female',
           ppf.sex)             "Gender",
    ppttl.user_person_type      "Person Type",
    ppf.applicant_number        "Applicant Number",
    ppf.national_identifier     "Social Security",
    ppf.effective_start_date    "Effective Date - From",
    ppf.effective_end_date      "Effective Date - To",
    ppf.start_date              "Latest Start Date",
    --
    ppf.date_of_birth         "Personal: Birth Date",
    ppf.town_of_birth         "Personal: Town Of Birth",
    ppf.region_of_birth       "Personal: Region of Birth",
    ppf.country_of_birth      "Personal: Country of Birth",
    trunc((sysdate -
      ppf.date_of_birth)/365) "Personal: Age",
    hl1.meaning               "Personal: Status",                
    ppf.nationality           "Personal: Nationality",
    hl2.meaning               "Personal: Registered Disabled",
    --
    hl5.meaning                         "Employment:Ethnic Origin",
    decode(ppf.per_information11,
        'N','No',
        'Y','Yes',
        ppf.per_information11)          "Employment:Ethnicity Disclosed",
    hl6.meaning                         "Employment:VETS100",
    decode(ppf.per_information9,
        'N','No',
        'Y','Yes',
        ppf.per_information9)           "Employment:Child Support",
    decode(ppf.per_information10,
        'N','No',
        'Y','Yes',
        ppf.per_information10)          "Employment:Opted for Medicare",
    decode(ppf.per_information2,
        'N','No',
        'Y','Yes',
        ppf.per_information2)           "Employment:I-9 Status",
    ppf.per_information3                "Employment:I-9 Expiration",
    hl7.meaning                         "Employment:New Hire",
    hl9.meaning                         "Employment:Exception Reason",
    hl8.meaning                         "Employment:Vets 100A",
    ppf.office_number              "Office Details: Office",
    ppf.internal_location          "Office Details:Location",
    ppf.mailstop                   "Office Details:MailStop",
    ppf.email_address              "Office Details:Email Address",
    hl1.meaning                    "Office Details:Mail To",
    ppf.resume_exists              "Applicant:Resume Exists",
    ppf.resume_last_updated        "Applicant:Last Updated",
    ppf.hold_applicant_date_until  "Hold Application Until",
    ppf.background_check_status    "Background:Checked",
    ppf.background_date_check      "Background:Date Checked",
    ppf.rehire_recommendation      "Rehire:Recommendation",
    ppf.rehire_reason              "Rehire:Reason",
    ppf.honors                     "Further Name:Honors",
    ppf.known_as                   "Further Name:Preferred Name",
    ppf.previous_last_name         "Previuos Last Name",
    hl3.meaning                    "Medical: Blood Type",
    ppf.last_medical_test_date     "Medical:Last Test Date",
    ppf.last_medical_test_by       "Medical:Last Test By",
    ppf.work_schedule              "Other:Work Schedule",
    ppf.fte_capacity               "Other:Full Time Availability",
    ppf.correspondence_language    "Correspondance language",
    ppf.date_of_death              "Date of Death",
    ppf.student_status             "Other:Student Status",
    ppf.date_employee_data_verified  "Other:Date Last Verified",
    ppf.on_military_service          "Other:On Military Service",
    ppf.second_passport_exists       "Other:Second Passport Exist",
    bbg.name                         "Benifits:Benifit Group",
    ppf.uses_tobacco_flag            "Benifits:Uses Tobacco",
    ppf.coord_ben_med_pln_no         "Benifits:Medical Plan",
    ppf.dpdnt_adoption_date          "Benifits:Adoption Date",
    ppf.receipt_of_death_cert_date   "Date Received Death Cert",
    pps.adjusted_svc_date            "Benifits:Adjusted Service Date" ,
    ppf.original_date_of_hire        "Benifits:Original Date Of Hire",
    ppf.coord_ben_no_cvg_flag        "Benifits:Other Coverage",
    ppf.dpdnt_vlntry_svce_flag       "Benifits:Voluntary Service",
    --
    ppf.person_id
from
    per_people_f ppf,
    per_person_types ppt,
    per_person_types_tl ppttl,
    hr_lookups hl1,
    hr_lookups hl2,
    ben_benfts_grp bbg,
    per_periods_of_service pps,
    hr_lookups hl11,
    hr_lookups hl12,
    hr_lookups hl3,
    hr_lookups hl4,
    hr_lookups hl5,
    hr_lookups hl6,
    hr_lookups hl7,
    hr_lookups hl8,
    hr_lookups hl9
where
    1=1
and ppt.person_type_id = ppf.person_type_id
and ppt.person_type_id = ppttl.person_type_id
and bbg.benfts_grp_id(+) = ppf.benefit_group_id
and pps.person_id(+) = ppf.person_id
and hl1.lookup_type(+) = 'STUDENT_STATUS'
and hl1.lookup_code(+) = ppf.student_status
and hl2.lookup_type(+) = 'REGISTERED_DISABLED'
and hl2.lookup_code(+) = ppf.registered_disabled_flag
and hl11.lookup_type(+) = 'HOME_OFFICE'
and hl11.lookup_code(+) = ppf.expense_check_send_to_address
and hl12.lookup_type(+) = 'WORK_SCHEDULE'
and hl12.lookup_code(+) = ppf.work_schedule
and hl3.lookup_type(+) = 'BLOOD_TYPE'
and hl3.lookup_code(+) = ppf.blood_type
and hl4.lookup_type(+) = 'STUDENT_STATUS'
and hl4.lookup_code(+) = ppf.student_status
and hl5.lookup_type(+) = 'US_ETHNIC_GROUP'
and hl5.lookup_code(+) = ppf.per_information1
and hl6.lookup_type(+) = 'US_VETERAN_STATUS'
and hl6.lookup_code(+) = ppf.per_information5
and hl7.lookup_type(+) = 'US_NEW_HIRE_STATUS'
and hl7.lookup_code(+) = ppf.per_information7
and hl8.lookup_type(+) = 'US_VETERAN_STATUS'
and hl8.lookup_code(+) = ppf.per_information25
and hl9.lookup_type(+) = 'US_NEW_HIRE_EXCEPTIONS'
and hl9.lookup_code(+) = ppf.per_information8
and ppttl.language = userenv ('LANG')
--
and ppf.employee_number = '1309'
order by
    1,2,3,4

Query: People Fedral Tax Rules

select
    ppf.employee_number                                         "Employee Number",
    --
    (select state_name
        from pay_us_states
        where state_abbrev = add_information17)               "Resident",
    (select hlat.region_2
       from hr_locations_all hlat
      where hlat.location_id = paaf.location_id)              "Work",
    pus.state_name                                            "SUI State",
    paa.add_information18                                     "Resident City",
    pus.state_name                                            "Work City",
    paa.add_information19                                     "Resident County",
     (select hlat.region_1
       from hr_locations_all hlat
      where hlat.location_id = paaf.location_id)               "Work County",
    hrl1.meaning                                              "Filing Status",
    ftr.withholding_allowances                                 "Allowances",
    ftr.fit_additional_tax                                     "Additional Tax",
    ftr.fit_exempt                                             "Tax Exemption :Fit",
    ftr.futa_tax_exempt                                        "Tax Exemption :FUTA",
    ftr.medicare_tax_exempt                                    "Tax Exemption :MEDICARE",
    ftr.ss_tax_exempt                                          "Tax Exemption :SS",
    ftr.fit_override_amount                                   "FO:Regular Amount",
    ftr.fit_override_rate                                     "FO:Regular Rate",
    ftr.supp_tax_override_rate                                 "FO:Supplemental Rate",
    ftr.excessive_wa_reject_date                               "Lock in Date",
    ftr.statutory_employee                                     "Statutory Employee",
    ftr.cumulative_taxation                                    "Cumulative Taxation",
    ftr.wage_exempt                                            "FIt Exempt Wage Acc",
    hrl2.meaning                                               "EIC filling status",
    ftr.effective_start_date                                  "Effec Dates: From",
    decode (ftr.effective_end_date,
    to_date ('31124712', 'DDMMYYYY'), null,
    ftr.effective_end_date)                                   "Effec Dates: To",
    --
    ftr.emp_fed_tax_rule_id,
    paaf.assignment_id,
    paaf.location_id,
    paa.address_id
    --
from
    pay_us_emp_fed_tax_rules_f ftr,
    per_all_assignments_f paaf,
    pay_us_states pus,
    per_addresses paa,
    hr_lookups hrl1,
    hr_lookups hrl2,
    per_people_f ppf
where  
    ftr.sui_state_code = pus.state_code
and ftr.assignment_id=paaf.assignment_id
and paa.person_id=paaf.person_id
and ftr.filing_status_code = hrl1.lookup_code
and hrl1.lookup_type = 'US_FIT_FILING_STATUS'
and ftr.eic_filing_status_code = hrl2.lookup_code
and hrl2.lookup_type = 'US_EIC_FILING_STATUS'
and nvl(paaf.effective_end_date,sysdate)>sysdate-1
and ftr.assignment_id = paaf.assignment_id
and paaf.person_id = ppf.person_id
--
and ppf.employee_number='1309'
order by
      1,2;

Query: People State Tax Rules

select
    ppf.employee_number                                         "Employee Number",
    --
    pus.state_name                                             "State",
    hrl.meaning                                                "W4 Info :Filing Status",
    str.withholding_allowances                                 "W4 Info :Allowances",
    str.secondary_wa                                           "W4 Info: Secon Allowances",
    str.additional_wa_amount                                   "W4 Info:Exemption Aaount",
    str.sit_additional_tax                                     "W4 Info:Addn Tax",
    str.sit_optional_calc_ind                                  "W4 Info :Opt Calc" ,
    str.sit_exempt                                             "Tax Ex: SIT",
    str.sdi_exempt                                             "Tax Ex: SDI",
    str.sui_exempt                                             "Tax Ex: SUI",
    str.wc_exempt                                              "Tax Ex: WC",
    str.sit_override_rate                                      "SIT Override:Regular Rate",
    str.sit_override_amount                                    "SIT Override:Regular Amount",
    str.supp_tax_override_rate                                 "SIT Override: Supp Rate",
    str.sui_wage_base_override_amount                          "SUI Base Override",
    str.excessive_wa_reject_date                               "Lock in Date",
    str.state_non_resident_cert                                "Non Resi Certi",
    str.wage_exempt                                            "SIT Exm Fm Wg Accm",
    str.effective_start_date                                   "Effective Date : From",
    decode (str.effective_end_date,
    to_date ('31124712', 'DDMMYYYY'), null,
    str.effective_end_date)                                    "Effective Date: To",
    --
    str.assignment_id,
    str.business_group_id
    --
from
    pay_us_emp_state_tax_rules_f str,
    pay_us_states pus,
    pay_state_rules psr,
    hr_lookups hrl,
    per_all_assignments_f paaf,
    per_people_f ppf
where    
    pus.state_code = str.state_code
and psr.state_code = pus.state_abbrev
and hrl.lookup_type = psr.fs_lookup_type
and nvl(str.effective_end_date,sysdate)>sysdate-1
and hrl.lookup_code = ltrim (str.filing_status_code, '0')
and str.assignment_id = paaf.assignment_id
and nvl(paaf.effective_end_date,sysdate)>sysdate-1
and paaf.person_id = ppf.person_id
--
and ppf.employee_number='1309'
order by
      1,2;         

Query: People County Tax Rules

select
    ppf.employee_number                               "Employee Number",
    --
    pus.state_name                                    "State",
    puc.county_name                                   "County",
    hrl.meaning                                       "W4 Info: Filing Status",
    ctr.withholding_allowances                        "W4 Info: Allowances",
    ctr.lit_additional_tax                            "W4 Info: Additional Tax",
    ctr.additional_wa_rate                            "W4 Info:Add Alllow Rate",
    ctr.lit_exempt                                    "Tax Ex:Local Income",
    ctr.sd_exempt                                     "Tax Ex:School District",
    ctr.ht_exempt                                     "Tax Ex: Occu Privilage",
    ctr.lit_override_rate                             "LIT Override: Rate",
    ctr.lit_override_amount                           "LIT Override: Amount",
    psd.school_dst_name                               "School District",
    ctr.wage_exempt                                   "LIT Ex from Wge Accu",
    ctr.effective_start_date                          "Effective Dates: From",
    decode (ctr.effective_end_date,
    to_date ('31124712', 'DDMMYYYY'), null,
    ctr.effective_end_date)                           "Effective Dates: To",
    --
    ctr.assignment_id
from
    pay_us_emp_county_tax_rules_f ctr,
    pay_us_states pus,
    pay_us_counties puc,
    hr_lookups hrl,
    pay_us_county_school_dsts psd,
    per_all_assignments_f paaf,
    per_people_f ppf
where  
    1=1
and ctr.state_code = pus.state_code
and ctr.state_code = puc.state_code
and ctr.county_code = puc.county_code
and ctr.filing_status_code = hrl.lookup_code
and hrl.lookup_type = 'US_LIT_FILING_STATUS'
and ctr.state_code = psd.state_code(+)
and ctr.county_code = psd.county_code(+)
and ctr.school_district_code = psd.school_dst_code(+)
and ctr.assignment_id = paaf.assignment_id
and nvl(paaf.effective_end_date,sysdate)>sysdate-1
and paaf.person_id = ppf.person_id
--
and ppf.employee_number='1309'
order by
      1,2;

Query: People City Tax Rules

select
    ppf.employee_number                                         "Employee Number",
    --
    pus.state_name                                             "State",
    pucnty.county_name                                         "County",
    pucity.city_name                                           "City",
    hrl.meaning                                                "W4 Info: Filing Status",
    ctr.withholding_allowances                                 "W4 Info: Allowances",
    ctr.lit_additional_tax                                     "W4 Info: Additional Tax",
    ctr.additional_wa_rate                                     "W4 Info: Additional All Rate",
    ctr.lit_exempt                                             "Tax Exn:Local Income",
    ctr.sd_exempt                                              "Tax Exn:School District",
    ctr.ht_exempt                                              "Tax Exn: Occupational Pri",
    ctr.lit_override_rate                                      "LIT Override:Rate",  
    ctr.lit_override_amount                                    "LIT Override:Amount",
    psd.school_dst_name                                        "School District",
    ctr.wage_exempt                                            "LIT Exmp From Wage Accn",
    ctr.effective_start_date                                   "Effective Dates: From",
    decode (ctr.effective_end_date,
    to_date ('31124712', 'DDMMYYYY'), null,
                  ctr.effective_end_date)                      "Effective Dates: To",
    --
    ctr.business_group_id,
    ctr.emp_city_tax_rule_id,
    ctr.assignment_id
    --
from
    pay_us_emp_city_tax_rules_f ctr,
    pay_us_states pus,
    pay_us_counties pucnty,
    pay_us_city_names pucity,
    hr_lookups hrl,
    pay_us_city_school_dsts psd,
    per_all_assignments_f paaf,
    per_people_f ppf
where
    ctr.state_code = pus.state_code
and ctr.state_code = pucnty.state_code
and ctr.county_code = pucnty.county_code
and ctr.state_code = pucity.state_code
and ctr.county_code = pucity.county_code
and ctr.city_code = pucity.city_code
and (pucity.primary_flag = 'Y' or substr (pucity.city_code, 1, 1) = 'U')
and ctr.filing_status_code = hrl.lookup_code
and hrl.lookup_type = 'US_LIT_FILING_STATUS'
and ctr.state_code = psd.state_code(+)
and ctr.county_code = psd.county_code(+)
and ctr.city_code = psd.city_code(+)
and ctr.school_district_code = psd.school_dst_code(+)
and ctr.assignment_id = paaf.assignment_id
and nvl(paaf.effective_end_date,sysdate)>sysdate-1
and paaf.person_id = ppf.person_id
--
and ppf.employee_number='1309'
order by
      1,2;

Query: Job Work Preferences

select
    pjt.name                         "Job",
    --
    pdf.work_any_location            "All Locations",
    pdf.passport_required            "Passport Required",
    pdf.relocation_required          "Relocation Required",
    pdf.relocate_domestically        "Relocation for Position",
    pdf.travel_required              "Travel Required",
    pdf.work_duration                "Work Duration",
    pdf.work_hours                   "Work Hours",
    pdf.service_minimum              "Minimum Service",     -- lookup :per_lengths_of_service
    pdf.work_schedule                "Work Schedule",       -- lookup :per_work_schedule
    pdf.fte_capacity                 "FTE Capacity",        -- lookup :per_work_hours
    pdf.work_any_country             "All Countries",
    pdf.relocate_internationally     "Willing to Relocate ",
    pdf.other_requirements           "Required Locations",
    ptv1.territory_short_name        "Rquired Countries1",
    hrl1tl.location_code             "Location1",
    ptv2.territory_short_name        "Rquired Countries2",
    hrl2tl.location_code             "Location2",
    ptv3.territory_short_name        "Rquired Countries3",
    hrl3tl.location_code             "Location3",
    --
    pj.job_id
from
    per_deployment_factors pdf,
    per_jobs pj,
    per_jobs_tl pjt,
    fnd_territories_vl ptv1,
    fnd_territories_vl ptv2,
    fnd_territories_vl ptv3,
    hr_locations_no_join hrl1,
    hr_locations_no_join hrl2,
    hr_locations_no_join hrl3,
    hr_locations_all_tl hrl1tl,
    hr_locations_all_tl hrl2tl,
    hr_locations_all_tl hrl3tl
where
    1=1
and pdf.job_id = pj.job_id
and pj.job_id = pjt.job_id
and pjt.language = userenv('LANG')
and ptv1.territory_code(+) = pdf.country1
and ptv2.territory_code(+) = pdf.country2
and ptv3.territory_code(+) = pdf.country3
and hrl1.location_id(+) = pdf.location1
and hrl2.location_id(+) = pdf.location2
and hrl3.location_id(+) = pdf.location3
and hrl1.location_id = hrl1tl.location_id(+)
and hrl2.location_id = hrl2tl.location_id(+)
and hrl3.location_id = hrl3tl.location_id(+)
and decode (hrl1tl.location_id, null, '1', hrl1tl.language) = decode (hrl1tl.location_id, null, '1', userenv ('LANG'))
and decode (hrl2tl.location_id, null, '1', hrl2tl.language) = decode (hrl2tl.location_id, null, '1', userenv ('LANG'))
and decode (hrl3tl.location_id, null, '1', hrl3tl.language) = decode (hrl3tl.location_id, null, '1', userenv ('LANG'))
--
and pjt.name = 'AA900.Administrative Assistant'
order by
    1,2,3;

Query: Contracts

select
    per.employee_number                                                              "Employee Number",
    --
    per.full_name                                                                    "Full Name",
    con.reference                                                                    "Contract Reference",
    asg.assignment_number                                                            "Assignment Number",
    hr_person_type_usage_info.get_user_person_type (sysdate,per.person_id)           "Person Type",
    per.applicant_number                                                             "Applicant Number",
    pps.date_start                                                                   "Hire Date",
    substr (hr_general.decode_lookup('CONTRACT_TYPE', con.type), 1, 80)              "Contract Type",  
    substr (hr_general.decode_lookup('CONTRACT_STATUS', con.status),1,80)            "Contract Status",
    substr ( hr_general.decode_lookup('CONTRACT_STATUS_REASON',con.status_reason),1,80) "Contract Status Reason",
    con.description                                                                     "Contract Description",
    con.duration                                                                        "Duration",
    substr ( hr_general.decode_lookup ('QUALIFYING_UNITS',con.duration_units), 1,80)    "Duration Units",
    con.contractual_job_title                                                           "Contractual Job Title",
    con.parties                                                                         "Parties",
    substr (hr_general.decode_lookup ('CONTRACT_START_REASON',con.start_reason),1,80)   "Start Reason",
    substr (hr_general.decode_lookup ('CONTRACT_END_REASON',con.end_reason),1,80)       "End Reason",
    con.number_of_extensions                                                            "Number of Extensions",
    con.extension_reason                                                                "Extension Reason",
    con.extension_period                                                                "Extension Period",
    substr (hr_general.decode_lookup ('QUALIFYING_UNITS',con.extension_period_units),1,80)  "Extension Period Units",
    greatest (con.effective_start_date, per.effective_start_date)                   "Effective Start Date",
    least (con.effective_end_date, per.effective_end_date)                          "Effective End Date",
     substr( hr_general.decode_lookup ('DOCUMENT_STATUS',con.doc_status),1,80)      "Document Status",
    con.doc_status_change_date                                                      "Date of Change",
    fnd_attachment_util_pkg.get_atchmt_exists ('PER_CONTRACTS',
                             con.contract_id,null,null,null,null,
                            'PERWSCTR','O')                                         "Attachment Exists",
    --
    asg.people_group_id,
    per.business_group_id,
    per.person_id
from
    per_people_f per,
    per_assignments_f asg,
    per_person_types ppt,
    per_periods_of_service pps,
    per_contracts_f con,
    per_business_groups bg
where
    con.person_id(+) = per.person_id
and pps.person_id = per.person_id
and asg.person_id = per.person_id
and asg.assignment_type in ('E','C')
and ppt.person_type_id = per.person_type_id
and sysdate between asg.effective_start_date and asg.effective_end_date
and sysdate between per.effective_start_date and per.effective_end_date
and per.business_group_id = bg.business_group_id
--
and bg.name = 'Vision Corporation'
and per.employee_number='388'
order by
    1,2,3,4;

Query: Contract Details

select
    ppf.employee_number                                              "Employee Number",
    --
    pcf1.reference                                                                  "Reference",
    hr_contract_api.get_meaning (pcf1.type, 'CONTRACT_TYPE')                        "Type",
    hr_contract_api.get_meaning (pcf1.status, 'CONTRACT_STATUS')                    "Status",
    hr_contract_api.get_meaning (pcf1.status_reason, 'CONTRACT_STATUS_REASON')      "Status Reason",
    pcf1.description                                                                "Description",
    hr_contract_api.get_meaning (pcf1.doc_status,'DOCUMENT_STATUS')                 "Document Status",
    doc_status_change_date                                                          "Date of Change",
    hr_contract_api.get_active_start_date(pcf1.contract_id,sysdate,pcf1.status)     "Active Dates:Start",
    hr_contract_api.get_active_end_date(pcf1.contract_id,sysdate,pcf1.status)       "Active Dates: End",
    hr_contract_api.get_pps_start_date(pcf1.person_id,
                hr_contract_api.get_active_start_date(pcf1.contract_id,
                        sysdate,pcf1.status))                                       "Per of Ser Dates:From",
    hr_contract_api.get_pps_end_date ( pcf1.person_id,
                hr_contract_api.get_active_start_date(pcf1.contract_id,
                        sysdate,pcf1.status))                                       "Per of Ser Dates:To",  
    pcf1.duration                                                                   "Duration",
    hr_contract_api.get_meaning (pcf1.duration_units,'QUALIFYING_UNITS')            "Units",
    hr_contract_api.get_meaning (pcf1.start_reason,'CONTRACT_START_REASON')         "Start Reason",
    hr_contract_api.get_meaning (pcf1.end_reason,'CONTRACT_END_REASON')             "End Reason",
    pcf1.contractual_job_title                                                      "Contractual Job",
    pcf1.parties                                                                    "Parties",
    pcf1.extension_period                                                           "Extension Period",
    hr_contract_api.get_meaning (pcf1.extension_period_units,'QUALIFYING_UNITS')    "Extension Period Units",
    pcf1.extension_reason                                                           "Extension Reason",
    pcf1.number_of_extensions                                                       "Number of Extensions",
    pcf1.effective_start_date                                                       "Effective Dates : From",
    pcf1.effective_end_date                                                         "Effective Dates : TO",
    --
    pcf1.contract_id,
    pcf1.person_id,
    pcf1.business_group_id
from
    per_contracts_f pcf1,
    per_people_f ppf,
    per_business_groups bg
where
    1=1
and pcf1.person_id = ppf.person_id
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and ppf.business_group_id = bg.business_group_id
--
and bg.name = 'Vision Corporation'
and ppf.employee_number='388'
order by
    1,2,3;

Query: Events and Booking

select
    hr.meaning                                              "Type",
    pe.date_start                                           "Date:Start",
    pe.date_end                                             "Date: End",
    pe.time_start                                           "Time:Start",
    pe.time_end                                             "Time:End",
    hotl.name                                               "Org Run By",
    hltl.location_code                                      "Location",
    hl.timezone_code                                        "TimeZone",
    pe.external_contact                                     "External Contact: Name",
    pe.contact_telephone_number                             "External contact:Telephone",
    pap.full_name                                           "Internal Contact:Name",
    nvl (pap.employee_number, pap.npw_number)               "Internal Contact:Number",
    (select full_name
        from per_all_people_f
        where person_id = pb.person_id
        and sysdate between effective_start_date and effective_end_date)        "Bookings Name",
    (select employee_number
        from per_all_people_f
        where person_id = pb.person_id
        and sysdate between effective_start_date and effective_end_date)        "Worker Number",
    (select applicant_number
        from per_all_people_f
        where person_id = pb.person_id
        and sysdate between effective_start_date and effective_end_date)        "Applicant Number",
    --
    pe.event_id,
    pe.business_group_id,
    pe.assignment_id
    --
from
    per_events pe,
    per_bookings pb,
    per_all_people_f pap,
    hr_lookups hr,
    hr_locations_all_tl hltl,
    hr_locations_no_join hl,
    hr_all_organization_units ho,
    hr_all_organization_units_tl hotl
where  
    1=1
and ho.organization_id = hotl.organization_id(+)
and pe.organization_run_by_id = ho.organization_id(+)
and pe.event_or_interview = 'E'
and pe.type = hr.lookup_code
and pe.event_id=pb.event_id
and hr.lookup_type =decode (pe.emp_or_apl, 'E', 'EMP_EVENT_TYPE','A', 'APL_EVENT_TYPE')
and pe.location_id = hl.location_id(+)
and pe.internal_contact_person_id = pap.person_id(+)
and pe.date_start between nvl (pap.effective_start_date, pe.date_start)
and nvl (pap.effective_end_date, pe.date_start)
and decode (hotl.organization_id, null, '1', hotl.language) = decode (hotl.organization_id, null, '1', userenv ('LANG'))
and hl.location_id = hltl.location_id(+)
and decode (hltl.location_id, null, '1', hltl.language) =decode (hltl.location_id, null, '1', userenv ('LANG'))
--
and hr.meaning = 'Orientation Day'
order by
    1,2,3,4,5

Query: Organization Manager

select
    org_tl.name                                                         "Organization",
    ppf.full_name                                                       "Manager",
    fnd_date.canonical_to_date (org_info2.org_information3)             "Start Date",
    fnd_date.canonical_to_date (org_info2.org_information4)             "End Date",
    --
    org.organization_id,
    org.business_group_id,
    ppf.person_id
from
    hr_all_organization_units org,
    hr_all_organization_units_tl org_tl,
    hr_organization_information org_info2,
    per_all_people_f ppf
where
    1=1
and org_info2.organization_id = org.organization_id
and org_info2.org_information_context = 'Organization Name Alias'
and org_info2.org_information2 = to_char (ppf.person_id(+))
and org.organization_id = org_tl.organization_id
and org_tl.language = userenv ('LANG')
and exists (select null
    from hr_org_info_types_by_class oitbc, hr_organization_information org_info
    where org_info.organization_id = org.organization_id
    and org_info.org_information_context = 'CLASS'
    and org_info.org_information2 = 'Y'
    and oitbc.org_classification = org_info.org_information1
    and oitbc.org_information_type = 'Organization Name Alias')
and (decode(hr_security.view_all,
        'Y', 'TRUE',
        hr_security.show_record('HR_ALL_ORGANIZATION_UNITS',org.organization_id)) = 'TRUE'
    and decode (hr_general.get_xbg_profile,
            'Y', org.business_group_id,
            hr_general.get_business_group_id) = org.business_group_id
    or decode (hr_security.view_all,
            'Y', 'TRUE',
            hr_security.show_record ('PER_ALL_PEOPLE_F',
                               ppf.person_id,
                               ppf.person_type_id,
                               ppf.employee_number,
                               ppf.applicant_number)) = 'TRUE'
        and decode(hr_general.get_xbg_profile,
                'Y', ppf.business_group_id,
                hr_general.get_business_group_id) = ppf.business_group_id)
--
and org_tl.name = 'Sales'
order by
    1,2