Section 11-Done
-- NEW CTE: Dual Snapshot for HR Status and Sales Rep
PARTICIPANT_DUAL_SNAPSHOT AS (
SELECT
BG.PAYRUN_ID,
BG.CREDITED_PARTICIPANT_ID,
BG.ORG_ID,
BG.PAY_DATE,
PAY_CAL.PAYSHEET_CUTOFF_DT,
-- Snapshot at PAY_DATE
PD_PAY.ATTRIBUTE_CHAR2 AS HR_Status_PayDt,
PD_PAY.ATTRIBUTE_CHAR17 AS SalesRep_PayDt,
PD_PAY.COUNTRY_CODE AS Employment_Country_PayDt,
-- Snapshot at PAYSHEET_CUTOFF_DT
PD_CUTOFF.ATTRIBUTE_CHAR2 AS HR_Status_CutoffDt,
PD_CUTOFF.ATTRIBUTE_CHAR17 AS SalesRep_CutoffDt,
PD_CUTOFF.COUNTRY_CODE AS Employment_Country_CutoffDt,
-- Apply NVL logic here (prioritize cutoff date)
NVL(PD_CUTOFF.ATTRIBUTE_CHAR2, PD_PAY.ATTRIBUTE_CHAR2) AS HRStatus_c,
NVL(PD_CUTOFF.ATTRIBUTE_CHAR17, PD_PAY.ATTRIBUTE_CHAR17) AS SalesRP_c,
NVL(PD_CUTOFF.COUNTRY_CODE, PD_PAY.COUNTRY_CODE) AS EmploymentCountry_c
FROM BG
-- Join for PAY_DATE snapshot
LEFT JOIN PARTICIPANT_DETAILS_CACHE PD_PAY
ON TRUNC(BG.PAY_DATE) BETWEEN PD_PAY.START_DATE
AND NVL(PD_PAY.END_DATE, DATE '9999-12-31')
AND BG.CREDITED_PARTICIPANT_ID = PD_PAY.PARTICIPANT_ID
AND BG.ORG_ID = PD_PAY.ORG_ID
-- Join to get PAYSHEET_CUTOFF_DT
LEFT JOIN LATEST_PLAN_COMPONENT LPC_TEMP
ON LPC_TEMP.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND LPC_TEMP.ORG_ID = BG.ORG_ID
AND LPC_TEMP.PERIOD_ID = BG.PAY_PERIOD_ID
AND LPC_TEMP.PAYRUN_ID = BG.PAYRUN_ID
LEFT JOIN PAY_CAL
ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(LPC_TEMP.PAYRUN_NAME)
AND PAY_CAL.PAYGROUP_NAME = UPPER(LPC_TEMP.PAY_GROUP_NAME)
AND PAY_CAL.PERIOD_ID = BG.PAY_PERIOD_ID
AND PAY_CAL.CURRENCY_CODE = BG.LATEST_HOME_CURRENCY
AND TO_CHAR(PAY_CAL.INCENTIVE_TYPE) = UPPER(TO_CHAR(LPC_TEMP.INCENTIVE_TYPE))
-- Join for PAYSHEET_CUTOFF_DT snapshot
LEFT JOIN PARTICIPANT_DETAILS_CACHE PD_CUTOFF
ON TRUNC(PAY_CAL.PAYSHEET_CUTOFF_DT) BETWEEN PD_CUTOFF.START_DATE
AND NVL(PD_CUTOFF.END_DATE, DATE '9999-12-31')
AND BG.CREDITED_PARTICIPANT_ID = PD_CUTOFF.PARTICIPANT_ID
AND BG.ORG_ID = PD_CUTOFF.ORG_ID
)
Comments
Post a Comment