Oracle EBS - Employee - Manager Hierarchy SQL Query

Subject: SQL Query required to get the employee and Manager hierarchy

--SQL Query:--

SELECT

    papf.employee_number,

    papf.full_name,

    papf.person_id,

    ppg.segment1                 officer_flag, --executive flag for officers (Y is officer, N is not)

    nvl(spapf.full_name, 'NONE') supervisor_name,

    spapf.person_id              supv_person_id,

    level,

    pjobs.name                                         "Job"  

FROM

    per_people_x      papf,

    per_people_x      spapf,

    per_assignments_x paaf,

    pay_people_groups ppg,

    per_jobs                  pjobs

WHERE

        papf.person_id = paaf.person_id

    AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

    AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date

    AND sysdate BETWEEN spapf.effective_start_date AND spapf.effective_end_date

--AND_xxx_person_type.is_employee(:p_eff_date, papf.person_id)

    AND paaf.assignment_type IN ( 'E', 'C' )

    AND paaf.supervisor_id = spapf.person_id

    AND paaf.people_group_id = ppg.people_group_id

    AND paaf.primary_flag = 'Y'

    AND pjobs.job_id (+) = paaf.job_id

  --  AND pjobs.name like '%Senior Director%'

START WITH

    papf.employee_number = :p_emp_num --Enter emp num of employee whom Hierachy Need to Find

CONNECT BY

    PRIOR spapf.employee_number = papf.employee_number --AND LEVEL < :p_level

ORDER BY

    level DESC

Oracle EBS SQL Query - To Get the Object Source Code

 select dbms_metadata.get_ddl('VIEW','PO_LINES_INQ_V','APPS') from dual;

Oracle EBS - SQL Query to get Inventory Interface Manager Status

 

SELECT

    x.process_type       "Name",

    decode((

        SELECT

            '1'

        FROM

            apps.fnd_concurrent_requests       cr, apps.fnd_concurrent_programs_vl    cp, apps.fnd_application               a

        WHERE

                cp.concurrent_program_id = cr.concurrent_program_id

            AND cp.concurrent_program_name = x.process_name

            AND cp.application_id = a.application_id

            AND a.application_short_name = x.process_app_short_name

            AND phase_code != 'C'

            AND ROWNUM = 1

    ),

           '1',

           'Active',

           'Inactive')   "Status",

    x.worker_rows        "Worker Rows",

    x.timeout_hours      "Timeout Hours",

    x.timeout_minutes    "Timeout Minutes",

    x.process_hours      "Process Interval Hours",

    x.process_minutes    "Process Interval Minutes",

    x.process_seconds    "Process Interval Seconds"

FROM

    (

        SELECT

            mipc.process_code,

            mipc.process_status,

            mipc.process_interval,

            mipc.manager_priority,

            mipc.worker_priority,

            mipc.worker_rows,

            mipc.processing_timeout,

            mipc.process_name,

            mipc.process_app_short_name,

            a.meaning                                                                                                                                                                                      process_type,

            floor(mipc.process_interval / 3600)                                                                                                                                                              process_hours,

            floor((mipc.process_interval -(floor(mipc.process_interval / 3600) * 3600)) / 60)                                                                                                            process_minutes,

            ( mipc.process_interval - ( floor(mipc.process_interval / 3600) * 3600 ) - ( floor((mipc.process_interval -(floor(mipc.

            process_interval / 3600) * 3600)) / 60) * 60 ) )                         process_seconds,

            floor(mipc.processing_timeout / 3600)                                                                                                                                                            timeout_hours,

            floor((mipc.processing_timeout - floor(mipc.processing_timeout / 3600) * 3600) / 60)                                                                                                          timeout_minutes

        FROM

            apps.mtl_interface_proc_controls    mipc,

            apps.mfg_lookups                    a

        WHERE

                a.lookup_type = 'PROCESS_TYPE'

            AND a.lookup_code = mipc.process_code

    ) x 

 -- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction

ORDER BY

    1;