Oracle EBS - SQL Query to get the employee default expense account

 

SELECT

    concatenated_segments

FROM

    apps.gl_code_combinations_kfv

WHERE

    code_combination_id IN (

        SELECT DISTINCT

            default_code_comb_id

        FROM

            apps.per_all_assignments_f

            WHERE  effective_end_date >= sysdate

        AND

            person_id IN (

                SELECT

                    person_id

                FROM

                    apps.per_all_people_f

                WHERE

                    effective_end_date >= sysdate

                    and person_id LIKE '&person_id'

            )

    );

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;