INput query

SELECT DISTINCT PART_DTLS.PARTICIPANT_ID, PART_HDR.PARTICIPANT_NAME, CPB.PERIOD_ID, PART_DTLS.COUNTRY_CODE AS ACTUAL_COUNTRY, CPG_TL.PAY_GROUP_NAME AS LATEST_PG, -- Extract the country code from the PG name REGEXP_SUBSTR(CPG_TL.PAY_GROUP_NAME, '[^_]+', 1, REGEXP_COUNT(CPG_TL.PAY_GROUP_NAME, '_')) AS PG_COUNTRY_CODE, -- Validation for Pay Group CASE WHEN PART_DTLS.COUNTRY_CODE = REGEXP_SUBSTR(CPG_TL.PAY_GROUP_NAME, '[^_]+', 1, REGEXP_COUNT(CPG_TL.PAY_GROUP_NAME, '_')) THEN 'Y' ELSE 'N' END AS LATEST_PG_ASSIGNED, CPAY.PAYRUN_NAME AS LATEST_PAYMENT_BATCH, PART_DTLS.CURRENCY_CODE, PART_DTLS.START_DATE AS NEW_COUNTRY_START_DATE, -- NEW: FLAG TO IDENTIFY RECORDS WITH MULTIPLE COUNTRIES IN THE PERIOD (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_RECORD_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.SALESREP_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_b CPG_B ON CPG_B.PAY_GROUP_ID = CSPG.PAY_GROUP_ID LEFT JOIN CN_PAY_GROUPS_ALL_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 = :period_id ORDER BY COUNTRY_RECORD_STATUS DESC, PART_HDR.PARTICIPANT_NAME, PART_DTLS.START_DATE

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika