new query with period

WITH Country_Jumpers AS ( /* Step 1: Identify participants with > 1 country in the specific period */ SELECT PART_DTLS.PARTICIPANT_ID, PART_HDR.PARTICIPANT_NAME, CPB.PERIOD_ID, CPB.START_DATE AS PER_START, CPB.END_DATE AS PER_END, PART_HDR.ORG_ID, -- The "New" country code (Last one in the period) MAX(PART_DTLS.COUNTRY_CODE) KEEP (DENSE_RANK LAST ORDER BY PART_DTLS.START_DATE) AS LATEST_COUNTRY_IN_PERIOD, -- The Exact Transition Date (Start of the new record) MAX(PART_DTLS.START_DATE) KEEP (DENSE_RANK LAST ORDER BY PART_DTLS.START_DATE) AS TRANSITION_DATE, -- The "Old" country code (First one in the period) MAX(PART_DTLS.COUNTRY_CODE) KEEP (DENSE_RANK FIRST ORDER BY PART_DTLS.START_DATE) AS PREVIOUS_COUNTRY_IN_PERIOD FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID AND PART_HDR.ORG_ID = (SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV') JOIN CN_PERIODS_B CPB ON 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 CPB.PERIOD_ID = :Period_id AND CPB.CALENDAR_ID = (SELECT CALENDAR_ID FROM CN_CALENDARS_TL WHERE CALENDAR_NAME = 'Feb to Jan Fiscal Calendar' AND LANGUAGE = 'US') GROUP BY PART_DTLS.PARTICIPANT_ID, PART_HDR.PARTICIPANT_NAME, PART_HDR.ORG_ID, CPB.PERIOD_ID, CPB.START_DATE, CPB.END_DATE HAVING COUNT(DISTINCT PART_DTLS.COUNTRY_CODE) > 1 ) SELECT HOU.NAME AS BUSINESS_UNIT, CJ.PARTICIPANT_ID, CJ.PARTICIPANT_NAME, -- Period Boundaries for Clarity CJ.PER_START AS PERIOD_START_DATE, CJ.PER_END AS PERIOD_END_DATE, CJ.TRANSITION_DATE AS DATE_OF_TRANSITION, -- Move Classification CASE WHEN TRUNC(CJ.TRANSITION_DATE) > TRUNC(CJ.PER_START) THEN 'MID-PERIOD MOVE' ELSE 'FIRST DAY MOVE' END AS MOVE_TYPE, CJ.PREVIOUS_COUNTRY_IN_PERIOD || ' -> ' || CJ.LATEST_COUNTRY_IN_PERIOD AS COUNTRY_TRANSITION, CJ.LATEST_COUNTRY_IN_PERIOD AS ACTUAL_COUNTRY, CPG_TL.PAY_GROUP_NAME AS LATEST_PG, -- Extraction of Country from Pay Group Name CASE WHEN INSTR(CPG_TL.PAY_GROUP_NAME, '_', 1, 2) > 0 THEN SUBSTR(CPG_TL.PAY_GROUP_NAME, INSTR(CPG_TL.PAY_GROUP_NAME, '_', -1, 2) + 1, INSTR(CPG_TL.PAY_GROUP_NAME, '_', -1, 1) - INSTR(CPG_TL.PAY_GROUP_NAME, '_', -1, 2) - 1) ELSE NULL END AS PG_COUNTRY_CODE, -- Validation Flag CASE WHEN CJ.LATEST_COUNTRY_IN_PERIOD = CASE WHEN INSTR(CPG_TL.PAY_GROUP_NAME, '_', 1, 2) > 0 THEN SUBSTR(CPG_TL.PAY_GROUP_NAME, INSTR(CPG_TL.PAY_GROUP_NAME, '_', -1, 2) + 1, INSTR(CPG_TL.PAY_GROUP_NAME, '_', -1, 1) - INSTR(CPG_TL.PAY_GROUP_NAME, '_', -1, 2) - 1) ELSE NULL END THEN 'Y' ELSE 'N' END AS LATEST_PG_ASSIGNED, CPAY.PAYRUN_NAME AS LATEST_PAYMENT_BATCH, -- Audit Action Logic CASE WHEN (TRUNC(CJ.TRANSITION_DATE) > TRUNC(CJ.PER_START)) AND (CPG_TL.PAY_GROUP_NAME IS NULL OR CJ.LATEST_COUNTRY_IN_PERIOD != NVL(SUBSTR(CPG_TL.PAY_GROUP_NAME, INSTR(CPG_TL.PAY_GROUP_NAME, '_', -1, 2) + 1, INSTR(CPG_TL.PAY_GROUP_NAME, '_', -1, 1) - INSTR(CPG_TL.PAY_GROUP_NAME, '_', -1, 2) - 1), 'X')) THEN 'URGENT: CHECK MID-MONTH SETUP' WHEN CPG_TL.PAY_GROUP_NAME IS NULL THEN 'MISSING PAY GROUP' ELSE 'REVIEW' END AS AUDIT_ACTION FROM Country_Jumpers CJ LEFT JOIN HR_OPERATING_UNITS HOU ON HOU.ORGANIZATION_ID = CJ.ORG_ID -- Join Pay Group using specified table names LEFT JOIN CN_SRP_PAY_GROUPS_ALL CSPG ON CSPG.SALESREP_ID = CJ.PARTICIPANT_ID AND TRUNC(CSPG.START_DATE) <= TRUNC(CJ.PER_END) AND TRUNC(NVL(CSPG.END_DATE, TO_DATE('9999-12-31', 'YYYY-MM-DD'))) >= TRUNC(CJ.PER_START) 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' -- Join Payment Batch LEFT JOIN CN_TP_PAYRUNS_ALL CPAY ON CPAY.PAY_GROUP_ID = CPG_B.PAY_GROUP_ID AND CPAY.PAY_PERIOD_ID = CJ.PERIOD_ID ORDER BY AUDIT_ACTION DESC, CJ.TRANSITION_DATE ASC

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika