Thursday 19 September 2019

Script to get Project Listing

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(+)         

1 comment:

  1. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad
    Oracle Fusion HCM Training In Hyderabad

    ReplyDelete