Query to get project details in Oracle Fusion

Query to get project details in Oracle Fusion

Hi Friends, we are going to discuss about Query to get project details in Oracle Fusion. This query will help to extract the complete Project details in Oracle fusion. This is one of the most important sql query related to projects in Oracle Fusion. We can use this sql query , in our day to day to operations to fetch the project related information's in Oracle Fusion. We can refer this sql query to developed the custom BIP report in Oracle Fusion. Please find below the detail sql query to get project details in Oracle Fusion.

Query to get project details in Oracle Fusion

6 Important Table to get project details in Oracle Fusion

1.pjf_projects_all_vl
2.pjb_cntrct_proj_links
3.okc_k_headers_all_b
4.hr_all_organization_units
5.okc_contract_types_vl
6.pjf_project_types_vl

Detail SQL Query to get project details in Oracle Fusion

SELECT DISTINCT  PPA.segment1 project_number
      ,PPA.name project_name
      ,PPA.project_status_code
      ,HOU.name organization_name
      ,PPA.description   
      ,OCT.name contract_type
      ,PPT.project_type
   ,TO_CHAR(PPA.start_date, 'DD-MON-YYYY') start_date
   ,TO_CHAR(PPA.completion_date, 'DD-MON-YYYY') completion_date
   ,TO_CHAR(PPA.scheduled_finish_date, 'DD-MON-YYYY') closed_date
   ,OCH.contract_number
   ,OCH.estimated_amount contract_amount
  FROM pjf_projects_all_vl PPA
      ,pjb_cntrct_proj_links PCPL
      ,okc_k_headers_all_b OCH
      ,hr_all_organization_units HOU
      ,okc_contract_types_vl OCT
      ,pjf_project_types_vl PPT
 WHERE PPA.project_id = PCPL.project_id (+)
   AND OCH.id = PCPL.contract_id
   AND PPA.carrying_out_organization_id = HOU.organization_id
   AND OCH.contract_type_id = OCT.contract_type_id
   AND PPA.project_type_id = PPT.project_type_id
   AND pcpl.major_version=OCH.major_version

Query to get project details in Oracle Fusion