Query to get Employee Responsibility details in Oracle Fusion

SELECT papf.person_number

      ,ppnfv.display_name person_display_name

  ,paam.assignment_name

  ,hl.meaning AS representative_type

  ,par.asg_responsibility_id

  ,par.responsibility_name

  ,par.business_unit_id

  ,par.work_contacts_flag

  FROM per_asg_responsibilities par

      ,per_all_people_f papf

  ,per_all_assignments_m paam

  ,per_person_names_f_v ppnfv

  ,hcm_lookups hl

 WHERE par.person_id = papf.person_id

   AND par.assignment_id = paam.assignment_id

   AND par.person_id = ppnfv.person_id

   AND par.responsibility_type = hl.lookup_code

   AND hl.lookup_type = 'PER_RESPONSIBILITY_TYPES'

   AND par.status = 'Active'

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

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

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

   AND TRUNC(SYSDATE) BETWEEN par.start_date AND NVL(par.end_date,TO_DATE('31-12-4712','DD-MM-YYYY'))

ORDER BY papf.person_number

Employee assigned Responsibilities in Oracle Fusion

SELECT (SELECT DISTINCT person_number

          FROM per_all_people_f per

         WHERE per.person_id = aor.person_id

       ) person_number

  ,(SELECT DISTINCT full_name

          FROM per_person_names_f per

         WHERE per.person_id = aor.person_id

   AND name_type = 'GLOBAL'

    ) person_name

  ,aor.responsibility_name

  ,TO_CHAR(aor.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') start_date

  ,TO_CHAR(aor.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') end_date

  ,aor.responsibility_type

  ,aor.status

  ,(SELECT hou.name

          FROM hr_all_organization_units hou

         WHERE hou.organization_id = aor.business_unit_id

       ) business_unit_name

  ,(SELECT hou.name

          FROM hr_all_organization_units hou

         WHERE hou.organization_id = aor.legal_entity_id

        ) legal_entity

  ,(SELECT hou.name

          FROM hr_all_organization_units hou

         WHERE hou.organization_id = aor.organization_id

        ) department_name

      ,(SELECT location_name

          FROM hr_locations hl

         WHERE hl.location_id = aor.location_id

        ) location_name

  ,(SELECT DISTINCT name

          FROM hr_all_positions_f_vl pp

         WHERE pp.position_id = aor.position_id

        ) position_name

  ,(SELECT DISTINCT name

          FROM per_jobs_f_tl pj

         WHERE pj.job_id = aor.job_id

        ) job_name

  ,(SELECT DISTINCT name

          FROM per_grades_f_tl pg

         WHERE pg.grade_id = aor.grade_id

        ) grade_name

  ,aor.assignment_category

  ,(SELECT pay.payroll_name

          FROM pay_all_payrolls_f pay

         WHERE pay.payroll_id = aor.payroll_id

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

        ) payroll_name

  ,(SELECT name

          FROM per_legislative_data_groups_vl pld

         WHERE pld.legislative_data_group_id = aor.legislative_data_group_id

            ) legislative_data_group

  FROM per_asg_responsibilities aor

Query to get Employee Assignment Status in Oracle Fusion

SELECT papf.person_number

      ,pastt.user_status assignment_status

  FROM per_all_people_f papf

      ,per_all_assignments_m paam

  ,per_assignment_status_types past

  ,per_assignment_status_types_tl pastt

 WHERE papf.person_id = paam.person_id

   AND paam.assignment_status_type_id = past.assignment_status_type_id

   AND past.assignment_status_type_id = pastt.assignment_status_type_id

   AND pastt.source_lang = USERENV('LANG')

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

   AND paam.primary_assignment_flag = 'Y'

   AND paam.assignment_type = 'E'

   and paam.effective_latest_change = 'Y'

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

   AND TRUNC(SYSDATE) BETWEEN past.start_date AND NVL(past.end_date,SYSDATE)

   AND papf.person_number = nvl(:p_person_number,papf.person_number)

ORDER BY papf.person_number asc

        ,pastt.user_status 

Query to get Employee Contract Duration in Oracle Apps

Nav: Global HRMS Manager--> People-->Enter and Maintain--> Query Employee--> Click on Others Button-->Select Contract from List

select papf.person_id ,papf.employee_number ,papf.full_name ,pcf.duration ,hl.meaning Units from per_all_people_f papf ,per_contracts_f pcf ,hr_lookups hl where papf.person_id = pcf.person_id AND papf.person_id = :p_person_id AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE) AND NVL (TRUNC (pcf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE) AND pcf.duration_units = hl.lookup_code AND hl.lookup_type(+) = 'QUALIFYING_UNITS'

Query to get Employee Dependent Date Of Birth(Contact Information) in Oracle APPS

Nav: Global HRMS Manager--> People-->Enter and Maintain--> Query Employee--> Click on Others Button-->Select Contact from List

SELECT papf.person_id ,papf.employee_number ,papf.full_name employee_name ,papf_cont.full_name contact_name ,TO_CHAR(papf_cont.date_of_birth, 'DD-MON-RRRR') contact_date_of_birth ,hl.meaning contact_type FROM per_contact_relationships pcr ,per_all_people_f papf ,hr_lookups hl ,per_all_people_f papf_cont WHERE 1 = 1 AND papf.person_id = pcr.person_id AND pcr.contact_person_id = papf_cont.person_id AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE) AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE) AND hl.lookup_type(+) = 'CONTACT' AND hl.lookup_code(+) = pcr.contact_type AND papf.person_id = :p_person_id

Unable to create Employee Type Supplier in Oracle APPS R12

While creating Employee Type Supplier, Supplier Type not showing "Supplier used to process expense payments to internal employees" from list of values and showing only "Standard supplier".

Please follow Oracle Support Note "R12:AP: Suppliers Form: Unable To Create Or View An Employee Type Supplier (Doc ID 1371295.1)" to resolve the issue.