Few Queries for Oracle Projects


Project Basic Info:

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

Budget Planning Element Information:

SELECT NVL(pe.name, p.name) AS task_name,
 rlm.alias AS planning_resource,
 ra.TOTAL_PLAN_RAW_COST
FROM pa_budget_versions bv,
 pa_fin_plan_types_b pt,
 pa_proj_fp_options po,
 pa_resource_assignments ra,
 pa_resource_list_members rlm,
 pa_projects_all p,
 pa_proj_elements pe,
 pa_proj_element_versions pev
WHERE 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.project_id = p.project_id
AND bv.fin_plan_type_id = pt.fin_plan_type_id
AND bv.budget_version_id = po.fin_plan_version_id
AND po.fin_plan_option_level_code = 'PLAN_VERSION'
AND po.fin_plan_version_id = bv.budget_version_id
AND bv.budget_version_id = ra.budget_version_id
AND ra.resource_list_member_id = rlm.resource_list_member_id
AND ra.task_id = pev.proj_element_id (+)
and pev.parent_structure_version_id(+)=pa_planning_element_utils.get_fin_struct_id(ra.project_id,ra.budget_version_id)
AND pev.proj_element_id = pe.proj_element_id (+);

Change Order Basic Information:

select pci.ci_id,
       pci.summary Change_Order_Name,
       pci.ci_number Change_Order_Number,
       pci.description,
       pci.status_code,
       pcim.description
from pa_control_items pci,
pa_ci_impacts pcim
where project_id=:p_project_id
and pci.ci_id=:p_change_order_id
and pci.ci_id=pcim.ci_id
and pcim.impact_type_code='FINPLAN';

Financial Plan CI Impact:

SELECT pbv.ci_id,
  NVL(pe.name ,p.name) task_name,
  rlm.alias planning_resource_name,
  (NVL(rac.total_projfunc_burdened_cost,0)+NVL(NULL,0)) total_cost,
  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_txn_raw_cost)             AS raw_cost_txn_cur,
  ROUND(DECODE(pra.rate_based_flag, 'Y', rac.txn_raw_cost_rate_override, to_number(NULL)),5)    AS raw_cost_rate_override,
  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_projfunc_raw_cost)        AS raw_cost_proj_func_cur,
  ROUND(DECODE(pra.rate_based_flag, 'Y', rac.txn_average_burden_cost_rate, to_number(NULL)),5)  AS avg_burd_cost_rate,
  ROUND(DECODE(pra.rate_based_flag, 'Y', rac.txn_burden_cost_rate_override, TO_NUMBER(NULL)),5) AS burd_cost_rate_override,
  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_projfunc_burdened_cost)   AS burd_cost_proj_func_cur,
  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_txn_burdened_cost) AS burd_cost_txn_cur,
  NVL(pe.element_number, p.segment1)
  || '('
  || NVL(pe.name ,p.name)
  || ')' task_name_num,
  NVL(ppe.element_version_id,0) element_version_id
FROM pa_resource_assignments pra,
  pa_proj_elements pe,
  pa_resource_list_members rlm,
  pa_resource_asgn_curr rac,
  pa_budget_versions pbv,
  pa_fin_plan_types_b pfpt,
  pa_proj_fp_options po,
  pa_proj_element_versions ppe,
  pa_control_items ci,
  pa_projects_all p
WHERE pra.resource_assignment_id       = rac.resource_assignment_id
and pra.resource_list_member_id        = rlm.resource_list_member_id
and p.project_id                       = :p_project_id
and pra.project_id                     = p.project_id
AND pbv.budget_version_id              = rac.budget_version_id
and pbv.budget_version_id              = pra.budget_version_id
and pbv.project_id                     = pra.project_id
and pbv.ci_id                          = ci.ci_id
AND pbv.ci_id                          = :p_change_order_id
AND pbv.fin_plan_type_id               = pfpt.fin_plan_type_id
AND pbv.budget_version_id              = po.fin_plan_version_id
AND po.fin_plan_option_level_code      = 'PLAN_VERSION'
and po.fin_plan_version_id             = pbv.budget_version_id
AND pra.task_id                        = pe.proj_element_id (+)
and ppe.parent_structure_version_id (+)= pa_project_structure_utils.get_fin_struc_ver_id(pe.project_id)
AND pe.proj_element_id                 = ppe.proj_element_id (+)
AND ( rac.total_quantity              IS NOT NULL
OR rac.txn_burden_cost_rate_override  IS NOT NULL
OR rac.total_txn_burdened_cost        IS NOT NULL
OR rac.txn_bill_rate_override         IS NOT NULL
OR rac.total_txn_revenue              IS NOT NULL
or rac.txn_raw_cost_rate_override     is not null
OR rac.total_txn_raw_cost             IS NOT NULL);

Project & Change Order Approval Action History:

SELECT ROWNUM,
  ACTION_DATE,
  ACTION,
  from_user,
  from_role,
  to_user,
  to_role,
  Details,
  SEQUENCE,
  NOTIFICATION_ID,
  ACTION_TYPE
FROM
  (SELECT ACTION_DATE,
    ACTION,
    from_user,
    from_role,
    to_user,
    to_role,
    Details,
    SEQUENCE,
    NOTIFICATION_ID,
    ACTION_TYPE
  FROM
    (SELECT c.comment_date DATE1,
      TO_CHAR(c.comment_date,'DD-MON-RRRR HH24:MI:SS') action_date,
      c.action action,
      c.from_user from_user,
      c.from_role from_role,
      c.to_user to_user,
      c.to_role to_role,
      c.user_comment Details,
      C.SEQUENCE SEQUENCE,
      C.NOTIFICATION_ID NOTIFICATION_ID,
      C.ACTION_TYPE ACTION_TYPE
    FROM WF_NOTIFICATIONS WFN,
      pa_wf_processes pa,
      wf_item_activity_statuses wfitems,
      wf_comments c
    WHERE wfitems.notification_id = wfn.group_id
    AND wfitems.NOTIFICATION_ID   = C.NOTIFICATION_ID
    AND pa.item_type              = wfitems.item_type
    AND pa.item_key               = wfitems.item_key
    AND pa.entity_key1            = :p_project_id
    AND pa.item_type             IN ('PAPROWF','PAWFCISC')
    UNION ALL
    SELECT c.comment_date DATE1,
      TO_CHAR(c.comment_date,'DD-MON-RRRR HH24:MI:SS') action_date,
      c.action action,
      c.from_user from_user,
      c.from_role from_role,
      c.to_user to_user,
      c.to_role to_role,
      c.user_comment Details,
      C.SEQUENCE SEQUENCE,
      C.NOTIFICATION_ID NOTIFICATION_ID,
      C.ACTION_TYPE ACTION_TYPE
    FROM WF_NOTIFICATIONS WFN,
      pa_wf_processes pa,
      WF_ITEM_ACTIVITY_STATUSES_H wfitems,
      wf_comments c
    WHERE wfitems.notification_id = wfn.group_id
    AND wfitems.NOTIFICATION_ID   = C.NOTIFICATION_ID
    AND pa.item_type              = wfitems.item_type
    AND pa.item_key               = wfitems.item_key
    AND pa.entity_key1            = :p_project_id
    AND pa.item_type             IN ('PAPROWF','PAWFCISC')
    )
  ORDER BY DATE1 DESC,
    notification_id,
    sequence
  );

Happy New Year & 2011 in blogging!



Wish you all a very happy new year……

Here is what WordPress says about this Blog. Just thought of sharing with you all.