pay grp assignment
SELECT
PART_DTLS.PARTICIPANT_ID,
PART_HDR.PARTICIPANT_NAME,
CPB.PERIOD_ID,
PART_DTLS.COUNTRY_CODE AS ACTUAL_COUNTRY,
CPG.NAME AS LATEST_PG,
-- Extract the country code from the Pay Group Name (e.g., the 'IN' in 'DELL_APAC_IN_PG')
-- This logic looks for the string between the 2nd and 3rd underscore
REGEXP_SUBSTR(CPG.NAME, '[^_]+', 1, 3) AS PG_COUNTRY_CODE,
-- Validation Logic
CASE
WHEN PART_DTLS.COUNTRY_CODE = REGEXP_SUBSTR(CPG.NAME, '[^_]+', 1, 3) THEN 'Y'
ELSE 'N'
END AS LATEST_PG_ASSIGNED,
LISTAGG(DISTINCT PART_DTLS.COUNTRY_CODE, ' -> ') WITHIN GROUP (ORDER BY PART_DTLS.START_DATE) AS COUNTRY_TRANSITION,
LISTAGG(DISTINCT PART_DTLS.CURRENCY_CODE, ' -> ') WITHIN GROUP (ORDER BY PART_DTLS.START_DATE) AS CURRENCY_TRANSITION
FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS
JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR
ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID
-- Join to Pay Groups (Assumes CN_SRP_PAY_GROUPS_ALL for assignment)
LEFT JOIN CN_SRP_PAY_GROUPS_ALL CSPG
ON CSPG.PARTICIPANT_ID = PART_DTLS.PARTICIPANT_ID
AND TRUNC(CSPG.START_DATE) <= TRUNC(CPB.END_DATE)
AND TRUNC(NVL(CSPG.END_DATE, TO_DATE('4712-12-31', 'YYYY-MM-DD'))) >= TRUNC(CPB.START_DATE)
LEFT JOIN CN_PAY_GROUPS_ALL CPG ON CPG.PAY_GROUP_ID = CSPG.PAY_GROUP_ID
JOIN (
SELECT * FROM CN_PERIODS_B
WHERE CALENDAR_ID = (SELECT CALENDAR_ID FROM CN_CALENDARS_TL WHERE CALENDAR_NAME = 'Feb to Jan Fiscal Calendar' AND LANGUAGE = 'US')
) CPB
ON TRUNC(PART_DTLS.START_DATE) <= TRUNC(CPB.END_DATE)
AND TRUNC(NVL(PART_DTLS.END_DATE, TO_DATE('4712-12-31', 'YYYY-MM-DD'))) >= TRUNC(CPB.START_DATE)
WHERE CPB.PERIOD_ID = 2026001
GROUP BY
PART_DTLS.PARTICIPANT_ID,
PART_HDR.PARTICIPANT_NAME,
CPB.PERIOD_ID,
PART_DTLS.COUNTRY_CODE,
CPG.NAME
HAVING COUNT(DISTINCT PART_DTLS.COUNTRY_CODE) > 1
ORDER BY PART_HDR.PARTICIPANT_NAME
Comments
Post a Comment