How to Open Period in Fusion Apps

Login to Application
General Accounting  -->

Query To Get Employee Information in fusion application

SELECT
  ppf.person_number,
  ppn.first_name,
  ppn.last_name,
  ppf.EFFECTIVE_START_DATE hire_date,
  pea.EMAIL_ADDRESS,
  PJF.NAME JOB_NAME,
  haou2.NAME Ledger,
  PU.USERNAME,
  (SELECT ppnf.full_name
  FROM per_person_names_f ppnf
  WHERE ppnf.person_id = pasf.manager_id
  AND ppnf.name_type   = 'GLOBAL'
  ) supervisorname,
  glcc.segment1
  ||'.'
  || glcc.segment2
  ||'.'
  || glcc.segment3
  ||'.'
  || glcc.segment4
  ||'.'
  || glcc.segment5
  ||'.'
  || glcc.segment6
  ||'.'
  || glcc.segment7
  ||'.'
  || glcc.segment8 defaultexpaccount,
  (SELECT listagg(prdv.role_name, ',') within GROUP (
  ORDER BY role_name) AS role_name
  FROM per_user_roles pur,
    per_users puq,
    per_roles_dn_vl prdv
  WHERE pur.user_id = puq.user_id
  AND pur.role_id   = prdv.role_id
  AND puq.user_id   =pu.user_id
  AND rownum        <10
  GROUP BY pur.user_id
  ) role_name
FROM per_all_people_f ppf,
  per_person_names_f ppn,
  PER_ALL_ASSIGNMENTS_M paam,
  per_assignment_supervisors_f pasf,
  gl_code_combinations glcc,
  PER_EMAIL_ADDRESSES pea,
  hr_all_organization_units haou2,
  PER_JOBS pjf,
  PER_USERS PU
WHERE 1                                    =1
AND ppf.person_id                          = ppn.person_id
AND ppf.person_id                          =paam.person_id(+)
AND ppf.person_id                          = pasf.person_id(+)
AND NVL( pasf.manager_type,'LINE_MANAGER') = 'LINE_MANAGER'
AND ppf.person_id                          = pea.person_id(+)
AND PAAM.JOB_ID                            =PJF.JOB_ID(+)
AND PAAM.default_code_comb_id              =glcc.code_combination_id(+)
AND PAAM.BUSINESS_UNIT_ID                  =haou2.organization_id(+)
AND PPF.PERSON_ID                          =PU.PERSON_ID(+)
AND ppn.name_type                          = 'GLOBAL'
AND TRUNC (SYSDATE) BETWEEN TRUNC (PAAM.effective_start_date) AND TRUNC (PAAM.effective_end_date)
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL(pasf.effective_start_date,sysdate)) AND TRUNC(NVL(pasf.effective_end_date,sysdate))
AND TRUNC (SYSDATE) BETWEEN TRUNC (ppn.effective_start_date) AND TRUNC (ppn.effective_end_date)
AND TRUNC (SYSDATE) BETWEEN TRUNC (ppf.effective_start_date) AND TRUNC (ppf.effective_end_date)
AND PAAM.ASSIGNMENT_TYPE IN ('E','C')
ORDER BY PERSON_NUMBER