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

One Response to Few Queries for Oracle Projects

  1. Sreehari says:

    Hi,

    Please help me on this iisue.

    here is my requirement.

    Whenever user issue the material from inventory ( on hand quantity changed), we have to generate a data file which contains item number, sub inventory and quantity info. for this we have created a utl file package. But here my issue is, without creating triggers / Alerts , i have to run the utl package.

    Please advice me how to do this / is there any middle ware tools available to run the concurrent program immediately when ever on hand qty changed in sub inv without creating alerts and triggers.

    We are using R12.

    Regards,
    Sreehari.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: