Project Accounting - Useful Queries

Project Basic Query:


select proj.carrying_out_organization_name  project_organization,
  proj.segment1  project_number,
  proj.name  Project_Short_Name,
  proj.city ,
  proj.region,
  proj.long_name project_long_name,
  proj.start_date  transaction_start_date,
  proj.target_finish_date  Target_Finish_Date,
  proj.project_type ,
  proj.project_type_class_code ,
  proj.completion_date ,
  proj.public_sector_flag ,
  proj.project_status_name ,
  proj.project_status_code ,
  proj.wf_status_code ,
  proj.country_name ,
  proj.country_code ,
  proj.record_version_number ,
  proj.target_start_date ,
  proj.scheduled_start_date ,
  proj.scheduled_finish_date ,
  proj.actual_start_date ,
  proj.actual_finish_date,
  opr.name operating_unit
FROM pa_projects_prm_v proj,
  hr_all_organization_units_vl opr
where proj.org_id   = opr.organization_id
AND proj.project_id = :p_project_id;


Project Parties (Project Key Members):


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)));


Approved Cost Budget Version Detail:

SELECT po.project_id,
  bv.version_name,
  pt.name AS plan_type_name,
  bv.description,
  po.fin_plan_preference_code,
  bv.budget_version_id,
  bv.record_version_number,
  bv.budget_status_code,
  bv.raw_cost Row_Cost_Total
FROM pa_proj_fp_options po,
  pa_fin_plan_types_vl pt,
  pa_budget_versions bv
WHERE bv.project_id      = :p_project_id
AND bv.budget_version_id =
  (SELECT budget_version_id
  from pa_budget_versions
  WHERE project_id    =:p_project_id
  AND fin_plan_type_id=
    (SELECT fin_plan_type_id
    FROM pa_fin_plan_types_vl
    WHERE migrated_frm_bdgt_typ_code='AC'
    AND UPPER(NAME)                 =UPPER('Approved Cost Budget')
    )
  AND budget_status_code='B'
  AND CURRENT_FLAG      ='Y'
  )
AND bv.budget_version_id          = po.fin_plan_version_id
AND bv.ci_id                     IS NULL
AND po.fin_plan_option_level_code = 'PLAN_VERSION'
AND po.fin_plan_type_id           = pt.fin_plan_type_id
AND pt.fin_plan_type_id           =
  (SELECT fin_plan_type_id
  FROM pa_fin_plan_types_vl
  WHERE migrated_frm_bdgt_typ_code='AC'
  and upper(name)                 =upper('Approved Cost Budget')
  );