Extract project details with customer and specific key members

I got the requirement to build a new integration for Oracle Projects with an external system. For this I defined a custom view which extracts the required information from eBS.

The view extracts main project information for a specific project organization combined with the billing and shipping customer and address details and the names of the resources with specific roles on the project – in this case the Project Manager and Account Manager. Adapt the SQL to fit your own requirements.

SELECT
    PPA.PROJECT_ID
    , PPA.NAME PROJECT_NAME
    , PPA.SEGMENT1 PROJECT_NUMBER
    , PPS.PROJECT_STATUS_NAME PROJECT_STATUS
    , PPA.START_DATE PROJECT_START_DATE
    , PPA.COMPLETION_DATE PROJECT_COMPLETION_DATE
    , PPM.FULL_NAME PROJECT_MANAGER
    , PPM.EMPLOYEE_NUMBER PROJECT_MANAGER_EMP_NUMBER
    , PAM.FULL_NAME ACCOUNT_MANAGER
    , PAM.EMPLOYEE_NUMBER ACCOUNT_MANAGER_EMP_NUMBER
    , PA_CUST_V.*
FROM
    PA.PA_PROJECTS_ALL PPA
    , HR.HR_ALL_ORGANIZATION_UNITS HAOU
    , PA.PA_PROJECT_STATUSES PPS
    , (SELECT PPPV.PROJECT_ID, PPPV.ROLE, PPPV.FULL_NAME, PPPV.EMPLOYEE_NUMBER
        FROM APPS.PA_PROJECT_PLAYERS_V PPPV
        WHERE PPPV.ROLE = 'Project Manager') PPM
    , (SELECT PPPV.PROJECT_ID, PPPV.ROLE, PPPV.FULL_NAME, PPPV.EMPLOYEE_NUMBER
        FROM APPS.PA_PROJECT_PLAYERS_V PPPV
        WHERE PPPV.ROLE = 'Account Manager') PAM
    , (SELECT PPC.PROJECT_ID PC_PROJECT_ID
    , SHIP_LOC.ADDRESS1 SHIP_ADDRESS1
    , SHIP_LOC.ADDRESS2 SHIP_ADDRESS2
    , SHIP_LOC.ADDRESS3 SHIP_ADDRESS3
    , SHIP_LOC.ADDRESS4 SHIP_ADDRESS4
    , SHIP_LOC.POSTAL_CODE SHIP_POSTAL_CODE
    , SHIP_LOC.CITY SHIP_CITY
    , SHIP_LOC.STATE SHIP_STATE
    , SHIP_LOC.PROVINCE SHIP_PROVINCE
    , SHIP_LOC.COUNTY SHIP_COUNTY
    , SHIP_TER.TERRITORY_SHORT_NAME SHIP_COUNTRY
    , BILL_LOC.ADDRESS1 BILL_ADDRESS1
    , BILL_LOC.ADDRESS2 BILL_ADDRESS2
    , BILL_LOC.ADDRESS3 BILL_ADDRESS3
    , BILL_LOC.ADDRESS4 BILL_ADDRESS4
    , BILL_LOC.POSTAL_CODE BILL_POSTAL_CODE
    , BILL_LOC.CITY BILL_CITY
    , BILL_LOC.STATE BILL_STATE
    , BILL_LOC.PROVINCE BILL_PROVINCE
    , BILL_LOC.COUNTY BILL_COUNTY
    , BILL_TER.TERRITORY_SHORT_NAME BILL_COUNTRY
FROM
    PA.PA_PROJECT_CUSTOMERS PPC
    , AR.HZ_CUST_ACCT_SITES_ALL SHIP_HCAS
    , AR.HZ_CUST_ACCT_SITES_ALL BILL_HCAS
    , AR.HZ_PARTY_SITES SHIP_PS
    , AR.HZ_PARTY_SITES BILL_PS
    , AR.HZ_LOCATIONS SHIP_LOC
    , AR.HZ_LOCATIONS BILL_LOC
    , APPLSYS.FND_TERRITORIES_TL SHIP_TER
    , APPLSYS.FND_TERRITORIES_TL BILL_TER
WHERE
    1 = 1
    AND PPC.BILL_TO_ADDRESS_ID = BILL_HCAS.CUST_ACCT_SITE_ID(+)
    AND BILL_HCAS.PARTY_SITE_ID = BILL_PS.PARTY_SITE_ID(+)
    AND BILL_PS.LOCATION_ID = BILL_LOC.LOCATION_ID(+)
    AND BILL_LOC.COUNTRY = BILL_TER.TERRITORY_CODE
    AND BILL_TER.LANGUAGE = 'US'
    AND PPC.SHIP_TO_ADDRESS_ID = SHIP_HCAS.CUST_ACCT_SITE_ID(+)
    AND SHIP_HCAS.PARTY_SITE_ID = SHIP_PS.PARTY_SITE_ID(+)
    AND SHIP_PS.LOCATION_ID = SHIP_LOC.LOCATION_ID(+)
    AND SHIP_LOC.COUNTRY = SHIP_TER.TERRITORY_CODE
    AND SHIP_TER.LANGUAGE = 'US') PA_CUST_V
WHERE
    1 = 1
    AND PPA.ORG_ID = HAOU.ORGANIZATION_ID
    AND PPA.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE
    AND PPA.PROJECT_ID = PPM.PROJECT_ID(+) -- Project Manager
    AND PPA.PROJECT_ID = PAM.PROJECT_ID(+) -- Account Manager
    AND PPA.PROJECT_ID = PA_CUST_V.PC_PROJECT_ID(+) -- Customers
    AND HAOU.NAME = '<your own organization name>'
    AND PPA.TEMPLATE_FLAG = 'N' -- exclude project templates
ORDER BY
    PPA.SEGMENT1