SELECT PART_DTLS.PARTICIPANT_ID, PART_HDR.PARTICIPANT_NAME, CPB.PERIOD_ID, PART_DTLS.COUNTRY_CODE, -- Re-check if this is NULL PART_DTLS.ATTRIBUTE_CHAR40 AS EMPLOYMENT_COUNTRY, -- Sometimes used instead PART_DTLS.START_DATE AS REC_START, PART_DTLS.END_DATE AS REC_END, CPB.START_DATE AS PER_START, CPB.END_DATE AS PER_END FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID -- 1. POSSIBLY REMOVE THIS: Some setups use different Org IDs for Headers vs Details AND PART_DTLS.ORG_ID = PART_HDR.ORG_ID JOIN CN_PERIODS_B CPB -- 2. ENSURE ORG MATCH: Periods are usually Org-specific ON CPB.ORG_ID = PART_DTLS.ORG_ID AND TRUNC(PART_DTLS.START_DATE) <= TRUNC(CPB.END_DATE) AND TRUNC(NVL(PART_DTLS.END_DATE, TO_DATE('9999-12-31', 'YYYY-MM-DD'))) >= TRUNC(CPB.START_DATE) WHERE PART_HDR.PARTICIPANT_NAME = 'Insert Name Here' -- Test with one known person ORDER BY CPB.PERIOD_ID, PART_DTLS.START_DATE

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika