Script to get Project Listing

Introduction

This Post illustrates the steps required to get Project Listing Query in Oracle EBS R12.

Script to get Project Listing Query

SELECT pt.project_id, ppa.NAME project_name,
ppa.description project_description, ppa.segment1 project_number,
pt.task_number, ppa.project_status_code project_status,
(SELECT DISTINCT (SELECT person_number
FROM per_people_x ppx
WHERE ppx.person_id = ppn.person_id)
FROM pjf_project_parties proj_dir,
pjf_proj_role_types_tl prt,
per_all_assignments_f paf,
per_person_names_f ppn
WHERE proj_dir.project_id = ppa.project_id
AND proj_dir.project_role_id = prt.project_role_id
AND prt.project_role_name = ‘Project Manager’
AND proj_dir.resource_source_id = paf.person_id
AND paf.assignment_status_type = ‘ACTIVE’
AND paf.person_id = ppn.person_id
AND ppn.name_type = ‘GLOBAL’
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
AND NVL (paf.effective_end_date,
SYSDATE + 1
)
AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active
AND NVL (proj_dir.end_date_active,
SYSDATE + 1
)
AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date
AND NVL (ppn.effective_end_date,
SYSDATE + 1
)
AND paf.primary_flag = ‘Y’
AND ROWNUM < 2) project_manager
FROM pjf_tasks_v pt, pjf_projects_all_vl ppa
WHERE 1 = 1 AND ppa.project_id = pt.project_id(+)

What we expect in the script.

This script helps us to comprehend how to get Project Listing Query. Couple of tables which is being used in the query are per_person_names_f ppn, per_all_assignments_f paf,pjf_proj_role_types_tl prt,  pjf_project_parties proj_dir etc.

Summary

This Post described the script get Project Listing Query in Oracle EBS R12.

Got any queries?

Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.