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
Post a Comment