Project related data query-1

 @⁨Lakshmi Lux⁩ 

Use this query for Project related data

SELECT 

  Project.SEGMENT2 , 

  TO_CHAR(Fund.ISSUE_DATE,'MM/DD/YYYY') AS ISSUE_DATE, 

  Fund.ISSUE_DESCRIPTION , 

  Fund.ISSUE_NUMBER , 

  Contract.CONTRACT_NUMBER , 

  Fund.PROJECT_FUNDING_AMOUNT , 

  Fund.TOTAL_FUNDING_AMOUNT ,

  Project.PROJECT_STATUS_CODE1,

  Project.PROJECT_ID , 

  Project.PROJECT_STATUS_NAME,

DECODE(PCL.ACTIVE_FLAG,'Y','Y','N','N',' ') "PROJECT ASSOCIATED TO AWARD CONTRACT"

FROM 

  (

    SELECT 

      GMFA.ID, 

      GMFA.AWARD_ID, 

      GMFA.FUNDING_AMOUNT AS PROJECT_FUNDING_AMOUNT, 

      GAFV.ISSUE_DATE, 

      GAFV.ISSUE_DESCRIPTION, 

      GAFV.ISSUE_NUMBER, 

      GAFV.ID AS ID1,

(GAFV.DIRECT_FUNDING_AMOUNT + GAFV.INDIRECT_FUNDING_AMOUNT) AS TOTAL_FUNDING_AMOUNT,  

      GAP.PROJECT_ID AS PROJECT_ID2, 

      GAHB.ID AS GAHBId 

    FROM 

      GMS_AWARD_FUND_ALLOCATIONS GMFA, 

      GMS_AWARD_HEADERS_B GAHB, 

      GMS_AWARD_FUNDINGS_VL GAFV, 

      GMS_AWARD_PROJECTS GAP 

    WHERE 

        GMFA.AWARD_ID = GAHB.ID 

        AND GMFA.FUNDING_ID = GAFV.ID 

        AND GMFA.PROJECT_ID = GAP.ID

  ) Fund, 

  (

    SELECT 

      PPAB.PROJECT_ID, 

      PPAB.SEGMENT1 AS SEGMENT2, 

      PPSV.PROJECT_STATUS_NAME, 

      PPSV.PROJECT_STATUS_CODE AS PROJECT_STATUS_CODE1

    FROM 

      PJF_PROJECTS_ALL_B PPAB, 

      PJF_PROJECT_STATUSES_VL PPSV 

    WHERE 

      PPAB.PROJECT_STATUS_CODE = PPSV.PROJECT_STATUS_CODE

  

  ) Project, 

  (

    SELECT 

      GAHB.ID 

    FROM 

      GMS_AWARD_HEADERS_B GAHB 

    WHERE 1=1

      AND GAHB.TEMPLATE_FLAG = 'N'

  ) AWARD, 

  (

    SELECT 

      OKHAB.ID AS HDRPEO_ID, 

      OKHAB.MAJOR_VERSION AS HDRB_MAJOR_VERSION, 

      OKHAB.CONTRACT_NUMBER ,

  OKHAB.STS_CODE,

  OKHAB.CONTRACT_ID,

  OKHAB.ID

    FROM 

      OKC_K_HEADERS_ALL_B OKHAB 

    WHERE 

          UPPER(OKHAB.VERSION_TYPE) = UPPER('C')

  AND OKHAB.CONTRACT_NUMBER NOT IN ('Labor','Capit Interest')

  ) Contract ,

  PJB_CNTRCT_PROJ_LINKS PCL

WHERE 

    Fund.PROJECT_ID2 = Project.PROJECT_ID 

    AND Fund.AWARD_ID = AWARD.ID 

    AND AWARD.ID = Contract.HDRPEO_ID

AND Project.PROJECT_ID = PCL.PROJECT_ID(+)

AND Contract.HDRB_MAJOR_VERSION = PCL.MAJOR_VERSION(+)

AND Contract.ID = PCL.CONTRACT_ID(+)