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
dev7 from eror
select rules.* , -- BBAction_c DECODE(Rule_Number, 1, 'MAV_DIR_BB_ACTION_1000', 2, 'MAV_DIR_BB_ACTION_1003', 3, 'MAV_DIR_BB_ACTION_1001', 4, 'MAV_DIR_BB_ACTION_1003', 5, 'MAV_DIR_BB_ACTION_1003', 6, 'MAV_DIR_BB_ACTION_1003', 7, 'MAV_DIR_BB_ACTION_1003', 8, 'MAV_DIR_BB_ACTION_1003', 9, 'MAV_DIR_BB_ACTION_1003', 10, 'MAV_DIR_BB_ACTION_1003', 99, 'MAV_DIR_BB_ACTION_1002', 11 , 'MAV_DIR_BB_ACTION_1002', -- Pre-approved zero BB for active sale rep NULL ) AS "BBAction_c", -- BBReasonCode_c DECODE(Rule_Number, 1, 'MVA_DIR_BB_REASON_1018', 2, 'MVA_DIR_BB_REASON_1001', 3, 'MVA_DIR_BB_REASON_1018', 4, 'MVA_DIR_BB_REASON_1001', 5, 'MVA_DIR_BB_REASON_1001', 6, 'MVA_DI...
Comments
Post a Comment