Query To Get The Project Parties (Project Key Members) Details

SELECT *

FROM

  (SELECT DISTINCT PPP.PROJECT_ID project_id,

    DECODE(PA.ASSIGNMENT_ID,NULL,PPRT.MEANING,PA.ASSIGNMENT_NAME) project_role_meaning,

    PPP.RESOURCE_SOURCE_ID resource_source_id,

    PE.FULL_NAME resource_source_name,

    PPP.PROJECT_ROLE_ID project_role_id,

    PPRT.PROJECT_ROLE_TYPE project_role_type,

    PPP.START_DATE_ACTIVE start_date_active,

    ppp.end_date_active end_date_active,

    pa_project_parties_utils.active_party(ppp.start_date_active,ppp.end_date_active) active,

    'EMPLOYEE' party_type

  FROM PA_PROJECT_PARTIES PPP,

    PA_PROJECTS_ALL PPA,

    PA_PROJECT_ROLE_TYPES PPRT,

    PER_ALL_PEOPLE_F PE,

    PA_PROJECT_ASSIGNMENTS PA,

    fnd_user u,

    (SELECT pj.name job_name,

      haou.organization_id org_id,

      haou.name org_name,

      paf.person_id,

      paf.assignment_type

    FROM per_all_assignments_f paf,

      per_jobs pj,

      hr_all_organization_units haou

    WHERE TRUNC(sysdate) BETWEEN TRUNC(paf.effective_start_date) AND TRUNC(paf.effective_end_date)

    AND paf.primary_flag     = 'Y'

    AND paf.organization_id  = haou.organization_id

    AND NVL(paf.job_id, -99) = pj.job_id(+)

    ) prd

  WHERE PPP.RESOURCE_TYPE_ID  = 101

  AND PPP.PROJECT_ID          = PPA.PROJECT_ID

  AND PPP.PROJECT_ROLE_ID     = PPRT.PROJECT_ROLE_ID

  AND PPP.RESOURCE_SOURCE_ID  = PE.PERSON_ID

  AND PE.EFFECTIVE_START_DATE =

    (SELECT MIN(PAPF.EFFECTIVE_START_DATE)

    FROM PER_ALL_PEOPLE_F PAPF

    WHERE PAPF.PERSON_ID         =PE.PERSON_ID

    AND PAPF.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)

    )

  AND PE.EFFECTIVE_END_DATE          >=TRUNC(SYSDATE)

  AND PPP.PROJECT_PARTY_ID            = PA.PROJECT_PARTY_ID(+)

  AND NVL(prd.assignment_type,'-99') IN ('C',DECODE(DECODE(PE.CURRENT_EMPLOYEE_FLAG,'Y','Y',DECODE(PE.CURRENT_NPW_FLAG,'Y','Y','N')),'Y','E', 'B'),'E', '-99')

  AND ppp.resource_source_id          = prd.person_id(+)

  AND u.employee_id (+)               = ppp.resource_source_id

  AND ppp.object_type                 = 'PA_PROJECTS'

  AND ppp.object_id                   = ppa.project_id

  UNION ALL

  SELECT DISTINCT ppp.project_id,

    pprt.meaning,

    ppp.resource_source_id,

    hzp.party_name,

    ppp.project_role_id,

    pprt.project_role_type,

    ppp.start_date_active,

    ppp.end_date_active,

    pa_project_parties_utils.active_party(ppp.start_date_active,ppp.end_date_active),

    'PERSON'

  FROM pa_project_parties ppp,

    pa_projects_all ppa,

    pa_project_role_types pprt,

    hz_parties hzp,

    hz_parties hzo,

    hz_relationships hzr,

    hz_contact_points hzcp,

    fnd_user u

  WHERE ppp.resource_type_id     = 112

  AND ppp.project_id             = ppa.project_id

  AND ppp.project_role_id        = pprt.project_role_id

  AND ppp.resource_source_id     = hzp.party_id

  AND hzp.party_type             = 'PERSON'

  AND hzo.party_type             = 'ORGANIZATION'

  AND hzr.relationship_code     IN ('EMPLOYEE_OF', 'CONTACT_OF')

  AND hzr.status                 = 'A'

  AND hzr.subject_id             = hzp.party_id

  AND hzr.object_id              = hzo.party_id

  AND hzr.object_table_name      = 'HZ_PARTIES'

  AND hzr.directional_flag       = 'F'

  AND hzcp.owner_table_name (+)  = 'HZ_PARTIES'

  AND hzcp.owner_table_id (+)    = hzp.party_id

  AND hzcp.contact_point_type (+)= 'PHONE'

  AND hzcp.phone_line_type (+)   = 'GEN'

  AND hzcp.primary_flag (+)      = 'Y'

  AND u.person_party_id (+)      = ppp.resource_source_id

  AND ppp.object_type            = 'PA_PROJECTS'

  AND ppp.object_id              = ppa.project_id

  UNION ALL

  SELECT DISTINCT ppp.project_id,

    pprt.meaning,

    ppp.resource_source_id,

    hzo.party_name,

    ppp.project_role_id,

    pprt.project_role_type,

    ppp.start_date_active,

    ppp.end_date_active,

    pa_project_parties_utils.active_party(ppp.start_date_active,ppp.end_date_active),

    'ORGANIZATION'

  FROM pa_project_parties ppp,

    pa_projects_all ppa,

    pa_project_role_types_vl pprt,

    hz_parties hzo,

    hz_contact_points hzcp

  WHERE ppp.resource_type_id     = 112

  AND ppp.project_id             = ppa.project_id

  AND ppp.project_role_id        = pprt.project_role_id

  AND ppp.resource_source_id     = hzo.party_id

  AND hzo.party_type             = 'ORGANIZATION'

  AND hzcp.owner_table_name (+)  = 'HZ_PARTIES'

  AND hzcp.owner_table_id (+)    = hzo.party_id

  AND hzcp.contact_point_type (+)= 'PHONE'

  AND hzcp.phone_line_type (+)   = 'GEN'

  AND hzcp.primary_flag (+)      = 'Y'

  AND ppp.object_type            = 'PA_PROJECTS'

  AND ppp.object_id              = ppa.project_id

  UNION ALL

  SELECT ppc.project_id,

    'Customer Person' meaning,

    NULL,

    ppc.customer_name,

    NULL,

    NULL,

    NULL,

    NULL,

    DECODE(ppc.customer_status,'A','Y','I','N'),

    'ORGANIZATION' party_type

  FROM pa_project_customers_v ppc,

    hz_parties hzo,

    hz_contact_points hzcp

  WHERE hzcp.owner_table_name (+)= 'HZ_PARTIES'

  AND hzcp.owner_table_id (+)    = hzo.party_id

  AND hzcp.contact_point_type (+)= 'PHONE'

  AND hzcp.phone_line_type (+)   = 'GEN'

  AND hzcp.primary_flag (+)      = 'Y'

  AND ppc.party_type             ='PERSON'

  AND ppc.party_id               =hzo.party_id

  )

WHERE (project_id = :p_project_id

and party_type   <> 'ORGANIZATION'

AND TRUNC(sysdate) BETWEEN start_date_active AND NVL(end_date_active,TRUNC(sysdate)));