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

Popular posts from this blog

Section 15-Done

Section 16-Done

Section 13-Done