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

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika