Section 8
-- Materialize latest plan component - FILTERED
LATEST_PLAN_COMPONENT AS (
SELECT
PARTICIPANT_ID,
PAYRUN_NAME,
ORG_ID,
PAYRUN_ID,
PERIOD_ID,
PLAN_COMPONENT_ID,
PLAN_COMPONENT_name,
INCENTIVE_TYPE,
ROLE_NAME,
PAY_GROUP_NAME,
START_DATE,
END_DATE
FROM (
SELECT DISTINCT
PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID,
PB.PAYRUN_NAME,
PART_PAY.ORG_ID,
PB.PAYRUN_ID,
PB.PAY_PERIOD_ID AS PERIOD_ID,
PART_PAY.PLAN_COMPONENT_ID,
PC.INCENTIVE_TYPE,
PC.Plan_component_name,
roles.ROLE_NAME,
PG.PAY_GROUP_NAME,
TRUNC(SRP_CP.START_DATE) AS START_DATE,
TRUNC(SRP_CP.END_DATE) AS END_DATE,
DENSE_RANK() OVER (
PARTITION BY
PART_PAY.CREDITED_PARTICIPANT_ID,
PART_PAY.ORG_ID,
PB.PAY_PERIOD_ID,
PB.PAYRUN_ID
ORDER BY SRP_CP.START_DATE DESC
) AS LATEST_PC_RECORD
FROM CN_TP_PARTICIPANT_PAY_ALL PART_PAY
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = PART_PAY.PAYRUN_ID
AND PB.ORG_ID = PART_PAY.ORG_ID
AND PART_PAY.SOURCE_EVENT_DATE <= PB.PAY_DATE
AND PART_PAY.INCENTIVE_TYPE_CODE IN ('COMMISSION', 'BONUS')
AND PART_PAY.EARNING_ID IS NOT NULL
INNER JOIN CN_PAY_GROUPS_ALL_TL PG
ON PB.PAY_GROUP_ID = PG.PAY_GROUP_ID
AND PG.LANGUAGE = 'US'
INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC
ON PART_PAY.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
INNER JOIN CN_SRP_PLAN_COMPONENTS_ALL SRP_PC
ON SRP_PC.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
AND SRP_PC.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID
INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP
ON SRP_PC.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID
AND SRP_CP.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID
INNER JOIN CN_PARTICIPANT_ROLES cpr
ON SRP_CP.SRP_RULE_ID = cpr.PARTICIPANT_ROLE_ID
AND SRP_CP.PARTICIPANT_ID = cpr.PARTICIPANT_ID
AND SRP_CP.SRP_RULE_TYPE_CODE = 'ROLE'
INNER JOIN CN_ROLES_TL roles
ON cpr.ROLE_ID = roles.ROLE_ID
AND roles.LANGUAGE = 'US'
WHERE PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID)
)
WHERE LATEST_PC_RECORD = 1
),
-- Materialize calendar data
PAY_CAL AS (
SELECT
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR007) AS PAYMENT_BATCH_NAME,
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR008) AS PAYGROUP_NAME,
PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 AS PERIOD_ID,
PAYRUN_CAL.CURRENCY_CODE,
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR011) AS INCENTIVE_TYPE,
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR012) AS COUNTRY,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP009) AS MPA_CUTOFF_START_DT,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010) AS MPA_CUTOFF_END_DT,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP007) AS PAYSHEET_CUTOFF_START_DT,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP012) AS PAYROLL_SUBMISSION_END_CST,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008) AS PAYSHEET_CUTOFF_DT
FROM ZCA_REF_ENTITIES PAYRUN_CAL
WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c'
AND (:period_id IS NULL OR PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 = :period_id)
),
Comments
Post a Comment