Oracle Job Requisition and Pre employment Query and Tables

 with REQ_EFF as (SELECT  jobfamilytranslationpeo.job_family_name Requisition_type

       ,irv.RECRUITING_TYPE_CODE

       ,irv.REQUISITION_NUMBER

   ,(SELECT pjt.name

     FROM per_jobs_f pj

      ,per_jobs_f_tl pjt

WHERE pj.JOB_ID = irv.job_id

AND pj.job_id = pjt.job_id

AND sysdate between pjt.EFFECTIVE_START_DATE and pjt.EFFECTIVE_END_DATE

AND sysdate between pj.EFFECTIVE_START_DATE and pj.EFFECTIVE_END_DATE

AND pjt.LANGUAGE = USERENV('LANG')

    ) job_name

,ppnf.full_name cand_name

,phn.phone_number cand_phone_number

,email.email_address cand_email

,per.date_of_birth

,irv.open_date date_needed_by

,irv.attribute_char8 client

,irv.attribute_char7 project

,(hgeo.geography_element3 ||','|| hgeo.geography_element2 ||','|| hgeo.geography_element1) primary_location

,plt.location_name primary_work_location

-- ,(case when houft.organization_id = irv.LEGAL_EMPLOYER_ID then houft.name else null)

,ple.name legal_employer

,houft.name department_name

,fabu.bu_name business_unit

,DECODE(irv.WORKER_TYPE_CODE,'E','Employee','Contingent Worker') WORKER_TYPE_CODE

,irv.ATTRIBUTE_CHAR10 Worker_Category

,sub.SUBMISSION_ID

,(select PEI_INFORMATION15

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='LOGISTICS' ) logistics_owned_by

,(select PEI_INFORMATION_DATE3

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='XX_LOGISTICS' ) logistics_completion_date

  ,(select PEI_INFORMATION_DATE2

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='Craft' )craft_mobilisation_date

  ,(select PEI_INFORMATION20

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='Screening' )owned_by

  ,(select PEI_INFORMATION_DATE15

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='xxx' )xx_raft_completion_date

  ,(select PEI_INFORMATION8

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='Medical_Health' 

  AND PEI_INFORMATION1='Health (Internal)')medical_health

  ,(select PEI_INFORMATION_DATE3

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='Medical_Health' 

  AND PEI_INFORMATION1='Health (Internal)')medical_completion_date

FROM irc_requisitions_vl irv

    ,irc_states_vl st

,irc_phases_vl ph

    ,irc_submissions sub

    ,irc_candidates cand

,per_person_names_f ppnf

,per_phones phn

,per_email_addresses email

,per_persons per

,irc_geo_hier_nodes igeo

    ,hz_geographies hgeo

,per_locations pl

,per_location_details_f plf

,per_location_details_f_tl plt

,per_job_family_f jobfamilypeo 

    ,per_job_family_f_tl jobfamilytranslationpeo

,hr_organization_units_f_tl houft

,fun_all_business_units_v fabu

-- ,irc_ja_extra_info ijei

,per_legal_employers ple

WHERE 1=1

--AND irv.requisition_number = '297' 

AND irv.requisition_id = sub.requisition_id

AND ph.phase_id = sub.current_phase_id

AND st.state_id = sub.current_state_id

AND sub.active_flag = 'Y'

AND cand.person_id = sub.person_id

AND ppnf.person_id = cand.person_id

AND ppnf.name_type = 'GLOBAL'

--AND cand.person_id in (300000147313216)

AND phn.phone_id(+) = cand.cand_phone_id

AND email.email_address_id(+) = cand.cand_email_id

AND per.person_id= cand.person_id

AND igeo.GEOGRAPHY_ID = hgeo.GEOGRAPHY_ID

AND irv.GEOGRAPHY_NODE_ID = igeo.GEOGRAPHY_NODE_ID

AND pl.location_id = irv.location_id

AND pl.location_id = plf.location_id

AND plt.LOCATION_DETAILS_ID = plf.LOCATION_DETAILS_ID

AND plt.LANGUAGE = USERENV('LANG')

AND JobFamilyPEO.JOB_FAMILY_ID = JobFamilyTranslationPEO.JOB_FAMILY_ID

AND JobFamilyTranslationPEO.LANGUAGE = USERENV('LANG')

AND SYSDATE BETWEEN JobFamilyPEO.EFFECTIVE_START_DATE AND JobFamilyPEO.EFFECTIVE_END_DATE

AND SYSDATE BETWEEN JobFamilyTranslationPEO.EFFECTIVE_START_DATE AND JobFamilyTranslationPEO.EFFECTIVE_END_DATE

AND JobFamilyPEO.JOB_FAMILY_ID = irv.JOB_FAMILY_ID

AND houft.organization_id = irv.DEPARTMENT_ID

AND fabu.BU_ID = irv.BUSINESS_UNIT_ID

AND houft.LANGUAGE = USERENV('LANG')

AND ple.organization_id =irv.LEGAL_EMPLOYER_ID

AND ple.STATUS ='A'

AND (jobfamilytranslationpeo.job_family_name IN (:p_requisition_type) OR COALESCE(:p_requisition_type, NULL) IS NULL)

AND (irv.requisition_id IN (:p_requisition_number) OR COALESCE(:p_requisition_number, NULL) IS NULL)

AND (ppnf.full_name IN (:p_candidate_name) OR COALESCE(:p_candidate_name, NULL) IS NULL)

AND (irv.open_date IN (:p_date_need_by) OR COALESCE(:p_date_need_by, NULL) IS NULL)

AND (fabu.BU_ID IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

AND (houft.organization_id IN (:p_department_id) OR COALESCE(:p_department_id, NULL) IS NULL)

AND (plt.location_name IN (:p_primary_location) OR COALESCE(:p_primary_location, NULL) IS NULL)

AND (ple.organization_id IN (:p_legal_employer) OR COALESCE(:p_legal_employer, NULL) IS NULL)

order by ppnf.full_name

)

SELECT Requisition_type

,RECRUITING_TYPE_CODE

,REQUISITION_NUMBER

,job_name

,cand_name

,cand_phone_number

,cand_email

,date_of_birth

,date_needed_by

,client

,project

,primary_location

,primary_work_location

,legal_employer

,department_name

,business_unit

,WORKER_TYPE_CODE

,Worker_Category

,SUBMISSION_ID

,logistics_owned_by

,logistics_completion_date

,screening_trade_and_craft_mobilisation_date

,screening_trade_and_craft_owned_by

,screening_trade_and_craft_completion_date

,medical_occupational_health_owned_by

,medical_occupational_health_completion_date

FROM REQ_EFF

WHERE 1=1

AND (logistics_completion_date IN (:p_logistics_completion_date) OR COALESCE(:p_logistics_completion_date, NULL) IS NULL)

AND (screening_trade_and_craft_completion_date IN (:p_screening_trade_and_craft_completiond_date) OR COALESCE(:p_screening_trade_and_craft_completiond_date, NULL) IS NULL)

AND (medical_occupational_health_completion_date IN (:p_medical_occupational_health_completion_date) OR COALESCE(:p_medical_occupational_health_completion_date, NULL) IS NULL)

Employee with all element entries Query in Oracle HCM Cloud

 SELECT 

       ldg.name                         ldg_name,

       pect1.classification_name        primary_classification,

       (SELECT pect2.classification_name

       FROM pay_ele_classifications_tl pect2

       WHERE pect2.classification_id          = pet.secondary_classification_id

       AND pect2.LANGUAGE                     = USERENV('LANG')

       )                                secondary_classification,

       pldf.attribute1                  region,

       ftl.territory_short_name         country_name,

       ppslv.person_number              person_number,

       ppnf.full_name                   employee_name,

       pldft.location_name              location,

       houft.name                       department,

       (SELECT ffvv.description

        FROM gl_code_combinations   gcc,

             fnd_flex_values_vl     ffvv,

             fnd_id_flex_segments   fifs

        WHERE gcc.code_combination_id   = paam.default_code_comb_id

        AND gcc.segment5                = ffvv.flex_value

        AND ffvv.flex_value_set_id      = fifs.flex_value_set_id

        AND fifs.SEGMENT_NAME           = 'Team'

        AND fifs.id_flex_code           = 'GL#'

       ) team,

       pet.reporting_name                  reporting_name,

       to_char(pee.creation_date, 'MM-DD-YYYY') entry_date,

       to_char(pee.effective_start_date, 'MM-DD-YYYY') effective_start_date,

       to_char(pee.effective_end_date, 'MM-DD-YYYY') effective_end_date,

       pet.element_name                    element_name,

       pet.base_element_name               element_code,

       pet.description                     element_description,

       DECODE(pet.processing_type, 'R', 'Yes', 'N', 'No', 'No') recurring_entry,

       ldg.default_currency_code           currency,

       peevf.screen_entry_value            amount,

       ( CASE

WHEN ldg.default_currency_code = 'USD'

THEN to_number(peevf.screen_entry_value)

            ELSE

            ROUND((to_number(peevf.screen_entry_value)) * gdr.conversion_rate,2)

         END

) amount_usd,

       pgft.name                        job_grade,

       pjf.job_code                     job_code,

       pelf.attribute1                  bonus_eligible,

       pelf.attribute2                  merit_eligible,

       pelf.attribute3                  pension_eligible

  FROM pay_legislative_data_groups      ldg,

       pay_element_types_vl             pet,

   (select gdr1.conversion_rate, gdr1.from_currency

FROM gl_daily_rates gdr1

WHERE  gdr1.to_currency = 'USD'

AND gdr1.conversion_type = 'Corporate'

AND gdr1.conversion_date = (SELECT MAX(gdr2.conversion_date)

FROM  gl_daily_rates gdr2

WHERE gdr2.to_currency = 'USD'

AND gdr2.conversion_type = 'Corporate'

AND gdr2.from_currency=gdr1.from_currency

AND gdr2.conversion_date <= sysdate)

)                    gdr,

       pay_element_links_f              pelf,

       pay_ele_classifications_tl       pect1,

       pay_element_entries_f            pee,

       pay_element_entry_values_f       peevf,

       pay_input_values_f               piv,

       per_person_names_f               ppnf,

       per_person_secured_list_v        ppslv,

       per_all_assignments_m            paam,

       hr_organization_units_f_tl       houft,

       per_location_details_f           pldf,

       per_location_details_f_tl        pldft,

       per_grades_f_tl                  pgft,

       per_jobs_f                       pjf,

       per_locations                    pl,

       fnd_territories_tl               ftl

WHERE (( coalesce(NULL, :p_ldg_name) IS NULL )

          OR ( ldg.name IN (:p_ldg_name) ) )

   AND ( ( coalesce(NULL, :p_reporting_name) IS NULL )

          OR ( pet.reporting_name IN ( :p_reporting_name ) ) )

   AND ( ( coalesce(NULL, :p_element_name) IS NULL )

          OR ( pet.element_type_id IN ( :p_element_name ) ) )

   AND ( ( coalesce(NULL, :p_processing_type) IS NULL )

          OR ( pet.processing_type IN ( :p_processing_type ) ) )

   AND ldg.legislative_data_group_id  = pet.legislative_data_group_id

   AND ldg.default_currency_code      = gdr.from_currency(+)

   AND pet.element_type_id            = pee.element_type_id

   AND pet.element_type_id            = pelf.element_type_id

   AND pet.classification_id          = pect1.classification_id

   AND UPPER(pet.element_name) NOT LIKE '%SALARY%'

   AND pect1.LANGUAGE                 = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN pet.effective_start_date AND pet.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN pelf.effective_start_date AND pelf.effective_end_date

   AND pee.person_id                  = ppnf.person_id

   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

   AND pee.element_entry_id           = peevf.element_entry_id

   AND pee.last_update_date           = peevf.last_update_date

   AND pee.effective_start_date       = peevf.effective_start_date

   AND peevf.input_value_id           = piv.input_value_id

   AND piv.element_type_id            = pet.element_type_id

   AND piv.base_name                  = 'Amount'

   AND TRUNC(SYSDATE) BETWEEN piv.effective_start_date AND piv.effective_end_date

   AND ppnf.person_id                 = ppslv.person_id

   AND TRUNC(SYSDATE) BETWEEN ppslv.effective_start_date AND ppslv.effective_end_date

   AND ppnf.person_id                 = paam.person_id

   AND ppnf.name_type                 = 'GLOBAL'

   AND paam.organization_id           = houft.organization_id

   AND houft.LANGUAGE                 = USERENV('LANG')

   AND paam.effective_latest_change   = 'Y'

   AND paam.assignment_type           = 'E'

   AND paam.assignment_status_type    = 'ACTIVE'

   AND paam.primary_assignment_flag   = 'Y'

   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

   AND paam.location_id               = pldf.location_id

   AND pldf.location_details_id       = pldft.location_details_id

   AND pldft.LANGUAGE                 = USERENV('LANG')

   AND paam.grade_id                  = pgft.grade_id

   AND pgft.LANGUAGE                  = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN pgft.effective_start_date AND pgft.effective_end_date

   AND paam.job_id                    = pjf.job_id

   AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date

   AND paam.location_id               = pl.location_id

   AND pl.country                     = ftl.TERRITORY_CODE

   AND ftl.LANGUAGE                   = USERENV('LANG')

   AND (( coalesce(NULL, :p_full_name) IS NULL )

         OR ( ppnf.full_name IN (:p_full_name) ) )

   AND pee.effective_start_date <= :p_end_date

   AND pee.effective_end_date >= :p_start_date 

   --AND :p_start_date <= :P_end_date

ORDER BY ldg.name, ppnf.full_name, pet.element_name,pee.effective_start_date DESC

Payroll Element Configuration Query in HCM Oracle Cloud

 WITH pay_retro AS

(

SELECT rule_value, pet1.element_name

          FROM pay_tmplt_rule_values ptrv, 

               pay_tmplt_rules_f ptrf ,

               pay_element_types_vl pet1, 

               pay_templates pt

         WHERE ptrv.template_id = pt.template_id 

           AND pt.base_name = pet1.element_name

           AND ptrf.rule_id = ptrv.rule_id

           AND ptrf.rule_name = 'Retro'

),

pay_proration AS

(

SELECT rule_value, pet2.element_name

          FROM pay_tmplt_rule_values ptrv, 

               pay_tmplt_rules_f ptrf ,

               pay_element_types_vl pet2, 

               pay_templates pt

         WHERE ptrv.template_id = pt.template_id 

           AND pt.base_name = pet2.element_name

           AND ptrf.rule_id = ptrv.rule_id

           AND ptrf.rule_name = 'Proration'

)

SELECT ldg.name legislative_data_group

      ,pec.classification_name   primary_classification

      ,pec1.classification_name  secondary_classification

      ,NVL ((SELECT territory_short_name

      FROM fnd_territories_vl

      WHERE (territory_code = SUBSTR(pet.element_name,4,2))

     ),

(SELECT territory_short_name

      FROM fnd_territories_vl

      WHERE (territory_code = ldg.legislation_code))) country_name

      ,pet.element_name          name

      ,pet.reporting_name

      ,pet.description     

      ,TO_CHAR(pef.effective_start_date,'MM/DD/YYYY') effective_date

      ,TO_CHAR(pef.effective_end_date,'MM/DD/YYYY')   effective_end_date

      ,pef.input_currency_code   input_currency

      ,ptd1.definition_name      earliest_entry_date

      ,ptd2.definition_name      latest_entry_date

      ,DECODE(pet.use_at_asg_level, 'Y', 'Assignment level',

              DECODE(pet.use_at_term_level, 'Y', 'Term level', 'Pay relationship level')

             ) employment_level

      ,DECODE(hrl_petproc.meaning, 'Recurring', 'Yes', 'Nonrecurring', 'No') recurring

      ,pef.multiple_entries_allowed_flag multiple_entries_allowed

      ,pp.rule_value proration

      ,pr.rule_value retroactive

      ,pelf.element_link_name  element_eligibility_name

      ,pelf.standard_link_flag automatic_entry 

      , CASE WHEN :p_effective_start_date >= pef.effective_start_date AND :p_effective_start_date < pef.effective_end_date  

             THEN 'ACTIVE'

             ELSE 'INACTIVE'

             END AS            status

      ,pelf.attribute2         merit_eligible

      ,pelf.attribute1         bonus_eligible

      ,pelf.attribute3         pension_eligible

  FROM pay_element_types_vl       pet

      ,pay_ele_classifications_vl pec

      ,pay_ele_classifications_vl pec1

      ,pay_element_links_f        pelf

      ,pay_element_types_f        pef

      ,per_legislative_data_groups_vl ldg                     

      ,hr_lookups                 hrl_petproc

      ,pay_time_definitions       ptd1

      ,pay_time_definitions       ptd2

  ,pay_retro                  pr

  ,pay_proration              pp

 WHERE 1=1

   AND pet.classification_id           = pec.classification_id

   AND pet.secondary_classification_id = pec1.classification_id(+)

   AND pet.element_type_id             = pelf.element_type_id(+)

   AND pet.element_type_id             = pef.element_type_id

   AND ldg.legislative_data_group_id   = pet.legislative_data_group_id

   AND UPPER(pet.reporting_name) not like '%SALARY%'                

   AND pet.processing_type             = hrl_petproc.lookup_code

   AND hrl_petproc.lookup_type LIKE 'PAY_PROCESSING_TYPE'

   AND TRUNC(SYSDATE) BETWEEN hrl_petproc.start_date_active AND hrl_petproc.end_date_active

   AND pet.starting_time_def_id = ptd1.time_definition_id

   AND pet.ending_time_def_id   = ptd2.time_definition_id

   AND pet.element_name                = pp.element_name(+)

   AND pet.element_name                = pr.element_name(+)

   AND ((COALESCE(NULL, :param_ldg_name) IS NULL)

       OR (ldg.NAME IN (:param_ldg_name)))

   AND ((COALESCE(NULL, :param_element_name) IS NULL)

       OR (pet.element_type_id IN (:param_element_name)))

   AND ((COALESCE(NULL, :param_rep_name) IS NULL)                 

       OR (pet.reporting_name IN (:param_rep_name)))              

   AND (CASE WHEN :param_status = 'All'

             THEN 'All'

             WHEN :p_effective_start_date >= pef.effective_start_date AND :p_effective_start_date < pef.effective_end_date  

             THEN 'ACTIVE'

             ELSE 'INACTIVE'

         END)  = :param_status

   ORDER BY ldg.legislation_code, pet.element_name

AR Customer Tables , Architecture and joins , HZ tables and Joins in oracle Fusion Cloud

HZ tables Tables and Their Joins in Oracle Fusion Cloud  - 

Active Employees and Their Jobs in oracle apps

Active Employees and Their Jobs

SELECT papf.full_name emp_name,
      papf.current_employee_flag current_emp_flag,
      papf.employee_number emp_number,
      b.d_job_id job_title,
      papf.email_address email

FROM  apps.PER_ALL_PEOPLE_F       papf,
      apps.PER_ALL_ASSIGNMENTS_F asg,
      apps.PER_ASSIGNMENTS_V7    b,
      apps.PER_JOBS              pjb
   
WHERE papf.person_id       = asg.person_id(+)
AND asg.person_id          = b.person_id
AND b.effective_start_date =
  (SELECT MAX (b2.effective_start_date)
  FROM apps.per_assignments_v7 b2
  WHERE b2.person_id = b.person_id
  )
AND NVL (papf.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (asg.effective_end_date, SYSDATE  + 1) > SYSDATE
AND b.job_id                                   = pjb.job_id(+)
ORDER BY papf.full_name

Employee supervisor Oracle R12 SQL Query

# Oracle EBS SQL Query that will list all employees in the hr.per_all_assignments_f 
# table and their supervisor, in readable format.

SELECT DISTINCT papf1.full_name leve1_full_name
     , papf1.employee_number level1_empno
     , papf2.full_name leve2_full_name
     , papf2.employee_number level2_empno
  FROM hr.per_all_people_f papf1
     , hr.per_all_assignments_f paaf1
     , hr.per_all_assignments_f paaf2
     , hr.per_all_people_f papf2
  WHERE  papf1.person_id = paaf1.person_id
  AND paaf1.supervisor_id = papf2.person_id(+)
  AND papf2.person_id = paaf2.person_id

  ORDER BY leve1_full_name;