CTE
CN_PART_DETAILS AS (
SELECT
PD.PARTICIPANT_ID,
PH.PARTICIPANT_NAME,
-- Directly selecting from joined HR tables
PAPF.PERSON_NUMBER AS HR_PRIMARY_WORKER_NUMBER,
PD.ORG_ID,
PD.START_DATE,
PD.END_DATE,
PD.COUNTRY_CODE AS HR_Country,
PD.ATTRIBUTE_NUMBER1 AS HR_TI,
PD.CURRENCY_CODE AS HOME_CURRENCY,
PD.ATTRIBUTE_CHAR2 AS HR_Status,
PD.ATTRIBUTE_CHAR17 AS SalesRep,
PD.ATTRIBUTE_CHAR33 AS ActionCode,
-- Analyst details joined once instead of 4 separate lookups
ANALYST.PARTY_ID AS ANALYST_PARTY_ID,
ANALYST.HR_PRIMARY_WORKER_NUMBER AS ANALYST_BADGE_ID,
RES.RESOURCE_PROFILE_ID AS ANALYST_RESOURCE_PROFILE_ID,
RES.EMAIL_ADDRESS AS OWNER_EMAIL_ID
FROM CN_SRP_PARTICIPANT_DETAILS_ALL PD
INNER JOIN CN_SRP_PARTICIPANTS_ALL PH
ON PD.PARTICIPANT_ID = PH.PARTICIPANT_ID
AND PD.ORG_ID = PH.ORG_ID
-- JOIN 1: Fetch Participant's HR Worker Number
LEFT JOIN (
SELECT HPP.PARTY_ID, P.PERSON_NUMBER
FROM HZ_PERSON_PROFILES HPP
JOIN PER_ALL_PEOPLE_F P ON P.PERSON_ID = HPP.HR_PERSON_ID
WHERE TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
) PAPF ON PAPF.PARTY_ID = PH.PARTY_ID
-- JOIN 2: Fetch Analyst Header Info
LEFT JOIN CN_SRP_PARTICIPANTS_ALL ANALYST
ON PH.ANALYST_ID = ANALYST.PARTICIPANT_ID
AND PH.ORG_ID = ANALYST.ORG_ID
-- JOIN 3: Fetch Analyst Resource/Email Info
LEFT JOIN JTF_RS_RESOURCE_PROFILES RES
ON ANALYST.PARTY_ID = RES.PARTY_ID
)
Comments
Post a Comment