new query
SELECT
PART_HDR.PARTICIPANT_NAME AS "Name",
PART_DTLS.COUNTRY_CODE AS "Latest country",
CPG_TL.NAME AS "Latest PG",
-- Safe extraction: String before the last underscore
-- If no underscore exists, it returns NULL rather than crashing
CASE
WHEN INSTR(CPG_TL.NAME, '_') > 0
THEN SUBSTR(CPG_TL.NAME,
INSTR(CPG_TL.NAME, '_', -1, 2) + 1,
INSTR(CPG_TL.NAME, '_', -1, 1) - INSTR(CPG_TL.NAME, '_', -1, 2) - 1)
ELSE NULL
END AS "PG Country Code",
-- Latest PG Assigned logic
CASE
WHEN PART_DTLS.COUNTRY_CODE =
CASE
WHEN INSTR(CPG_TL.NAME, '_') > 0
THEN SUBSTR(CPG_TL.NAME, INSTR(CPG_TL.NAME, '_', -1, 2) + 1, INSTR(CPG_TL.NAME, '_', -1, 1) - INSTR(CPG_TL.NAME, '_', -1, 2) - 1)
ELSE NULL
END
THEN 'Y' ELSE 'N'
END AS "Latest PG Assigned",
CPAY.NAME AS "latest payment batch",
-- Payment batch assigned logic: Checks if batch name matches PG country format
CASE
WHEN CPAY.NAME IS NOT NULL AND CPAY.NAME LIKE '%' || PART_DTLS.COUNTRY_CODE || '%'
THEN 'Y' ELSE 'N'
END AS "Payment batch assigned",
-- Audit columns
PART_DTLS.START_DATE AS "Rec Start",
(SELECT CASE WHEN COUNT(DISTINCT p2.COUNTRY_CODE) > 1 THEN 'MULTI' ELSE 'SINGLE' END
FROM CN_SRP_PARTICIPANT_DETAILS_ALL p2
WHERE p2.PARTICIPANT_ID = PART_DTLS.PARTICIPANT_ID
AND TRUNC(p2.START_DATE) <= TRUNC(CPB.END_DATE)
AND TRUNC(NVL(p2.END_DATE, TO_DATE('4712-12-31', 'YYYY-MM-DD'))) >= TRUNC(CPB.START_DATE)
) AS "Country Status"
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 (
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)
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_B CPG_B ON CPG_B.PAY_GROUP_ID = CSPG.PAY_GROUP_ID
LEFT JOIN CN_PAY_GROUPS_TL CPG_TL ON CPG_TL.PAY_GROUP_ID = CPG_B.PAY_GROUP_ID AND CPG_TL.LANGUAGE = 'US'
LEFT JOIN CN_TP_PAYRUNS_ALL CPAY
ON CPAY.PAY_GROUP_ID = CPG_B.PAY_GROUP_ID
AND CPAY.PAY_PERIOD_ID = CPB.PERIOD_ID
WHERE
CPB.PERIOD_ID = :your_period_id
ORDER BY "Country Status" DESC, PART_HDR.PARTICIPANT_NAME
Comments
Post a Comment