Full query
SELECT rules.*,
-- BBAction_c
DECODE(Rule_Number,
1, 'MAV_DIR_BB_ACTION_1000',
2, 'MAV_DIR_BB_ACTION_1003',
3, 'MAV_DIR_BB_ACTION_1001',
4, 'MAV_DIR_BB_ACTION_1003',
5, 'MAV_DIR_BB_ACTION_1003',
6, 'MAV_DIR_BB_ACTION_1003',
7, 'MAV_DIR_BB_ACTION_1003',
8, 'MAV_DIR_BB_ACTION_1003',
9, 'MAV_DIR_BB_ACTION_1003',
10, 'MAV_DIR_BB_ACTION_1003',
99, 'MAV_DIR_BB_ACTION_1002',
11, 'MAV_DIR_BB_ACTION_1002',
NULL
) AS "BBAction_c",
-- BBReasonCode_c
DECODE(Rule_Number,
1, 'MVA_DIR_BB_REASON_1018',
2, 'MVA_DIR_BB_REASON_1001',
3, 'MVA_DIR_BB_REASON_1018',
4, 'MVA_DIR_BB_REASON_1001',
5, 'MVA_DIR_BB_REASON_1001',
6, 'MVA_DIR_BB_REASON_1001',
7, 'MVA_DIR_BB_REASON_1001',
8, 'MVA_DIR_BB_REASON_1001',
9, 'MVA_DIR_BB_REASON_1001',
10, 'MVA_DIR_BB_REASON_1001',
99, 'MVA_DIR_BB_REASON_1001',
11, 'MVA_DIR_BB_REASON_1027',
NULL
) AS "BBReasonCode_c",
-- BBMPAComment_c
DECODE(Rule_Number,
1, 'Full BB is pre-approved by system as the rep is active sale rep within the defined threshold limits (BB % HR TI ≥ -5% and ≤ 5%). Pre-approved not applicable for Austria country.',
2, 'Full CED put on Hold by system as the rep is on LOA with positive or negative CED amount. PENDING analyst to review and action.',
3, 'Full CED is pre-approved by system as the rep is terminated within the defined threshold limits of CED amount (CED > 0 and ≤ 10 USD). Pre-approved not applicable for Austria country.',
4, 'Full CED put on Hold by system as the rep is terminated within the defined threshold limits of CED amount (CED > 0 and ≤ 10 USD). PENDING analyst to review and action.',
5, 'Full CED put on Hold by system as the rep is terminated within the defined threshold limits of CED amount (CED > 10 USD). PENDING analyst to review and action.',
6, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.',
7, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Consider Payroll collection, if negative CED amount is final.',
8, 'Full CED put on Hold by system as the rep moved to non-sales role with positive CED amount. PENDING analyst to review and action.',
9, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.',
10, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Consider Payroll collection if negative CED amount is final. Rep''s consent NEEDS to be attached for such case.',
11, 'Zero BB is pre-approved by system for active sale rep',
99, 'Full BB put on Hold by system as the rep is active sale rep OVER the defined pre-approved BB threshold limits (BB % HR TI ≥ -5% and ≤ 5%). PENDING analyst to review and action.',
NULL
) AS "BBMPAComment_c",
-- MPAReasonCode_c
DECODE(Rule_Number,
1, NULL,
3, NULL,
'MAV_DIR_MPA_CODE_1008'
) AS "MPAReasonCode_c",
-- MPAGenerated_c
DECODE(Rule_Number,
1, NULL,
2, (-1) * CED_c,
3, NULL,
4, (-1) * CED_c,
5, (-1) * CED_c,
6, (-1) * CED_c,
7, (-1) * CED_c,
8, (-1) * CED_c,
9, (-1) * CED_c,
10, (-1) * CED_c,
99, (-1) * BeginningBalance_c,
NULL
) AS "MPAGenerated_c",
-- ExpectedPaymentAmount_c
DECODE(Rule_Number,
1, NULL,
3, NULL,
99, TO_NUMBER(RULES.PTDEarnings_c),
0
) AS "ExpectedPaymentAmount_c",
-- MPAStatus_c
DECODE(Rule_Number,
1, NULL,
3, NULL,
'Auto approved'
) AS "MPAStatus_c",
-- RecoverableFlagV1_c
DECODE(Rule_Number,
1, NULL,
3, NULL,
'Y'
) AS "RecoverableFlagV1_c"
FROM (SELECT base.*,
-- Evaluate complex conditions ONLY ONCE
CASE
WHEN base.HRStatus_c = 'A'
AND base.EmploymentCountry_c != 'AT'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.HRTI_c) != 0
AND TO_NUMBER(base.BBOfHRTI_c) BETWEEN -5 AND 5
AND TO_NUMBER(base.BeginningBalance_c) != 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 1
WHEN base.HRStatus_c = 'L'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CED_c) != 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 2
WHEN base.HRStatus_c = 'T'
AND base.EmploymentCountry_c != 'AT'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CEDUSD_c) BETWEEN 0 AND 10
AND base.IncentiveType_c = 'COMMISSION'
THEN 3
WHEN base.HRStatus_c = 'T'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CEDUSD_c) BETWEEN 0 AND 10
AND base.IncentiveType_c = 'BONUS'
THEN 4
WHEN base.HRStatus_c = 'T'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CEDUSD_c) > 10
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 5
WHEN base.HRStatus_c = 'T'
AND base.EmploymentCountry_c = 'RO'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CED_c) < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 6
WHEN base.HRStatus_c = 'T'
AND base.EmploymentCountry_c != 'RO'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CED_c) < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 7
WHEN base.HRStatus_c = 'A'
AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL)
AND TO_NUMBER(base.CED_c) > 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 8
WHEN base.HRStatus_c = 'A'
AND base.EmploymentCountry_c = 'RO'
AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL)
AND TO_NUMBER(base.CED_c) < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 9
WHEN base.HRStatus_c = 'A'
AND base.EmploymentCountry_c != 'RO'
AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL)
AND TO_NUMBER(base.CED_c) < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 10
WHEN base.HRStatus_c = 'A'
AND (UPPER(base.SalesRP_c) = 'Y')
AND TO_NUMBER(base.BeginningBalance_c) = 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 11
WHEN base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 99
ELSE 0
END AS Rule_Number
FROM (
WITH
-- Materialize conversion rates once
CONVERSION_RATES_USD AS (
SELECT
CR.FROM_CURRENCY,
CR.TO_CURRENCY,
CR.CONVERSION_START_DATE,
CR.CONVERSION_END_DATE,
CR.CONVERSION_RATE
FROM CN_CONVERSION_RATES CR
INNER JOIN CN_CONVERSION_TYPES_TL CT
ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID
WHERE CT.CONVERSION_TYPE_ID = '-1001'
AND CT.LANGUAGE = 'US'
AND CR.TO_CURRENCY = 'USD'
),
-- Materialize person numbers once - FILTERED
PERSON_NUMBERS AS (
SELECT DISTINCT
cspl.participant_id,
papf.person_number,
cspl.PARTICIPANT_NAME
FROM CN_SRP_PARTICIPANTS_ALL cspl
INNER JOIN hz_person_profiles hpp
ON cspl.party_id = hpp.party_id
INNER JOIN PER_ALL_PEOPLE_F papf
ON papf.person_id = hpp.hr_person_id
WHERE cspl.participant_id = NVL(:participant_id, cspl.participant_id)
),
-- Materialize participant details once - FILTERED
PARTICIPANT_DETAILS_CACHE AS (
SELECT
a.analyst_id,
b.PARTICIPANT_ID,
b.PARTICIPANT_DETAIL_ID,
b.ORG_ID,
b.START_DATE,
b.END_DATE,
b.COUNTRY_CODE,
b.ATTRIBUTE_CHAR40,
b.ATTRIBUTE_NUMBER1,
b.CURRENCY_CODE,
b.ATTRIBUTE_CHAR2,
b.ATTRIBUTE_CHAR17
FROM CN_SRP_PARTICIPANTS_ALL a
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL b
ON a.participant_id = b.participant_id
WHERE b.PARTICIPANT_ID = NVL(:participant_id, b.PARTICIPANT_ID)
),
-- Materialize multi-currency detection - FILTERED
BB_MULTI_CURRENCY AS (
SELECT
pay_1.PAYRUN_ID,
pay_1.PAYMENT_BATCH_PERIOD,
pay_1.PAYEE_PARTICIPANT_ID,
'Y' AS BBMultiCurrencyFlag
FROM (
SELECT
P.PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
HOME_CURRENCY_CODE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID)
AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)
GROUP BY
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
PAYRUN_ID,
HOME_CURRENCY_CODE
) pay_1
INNER JOIN (
SELECT
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD
AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID)
AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)
GROUP BY
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
PAYRUN_ID
) pay_2
ON pay_1.PAYRUN_ID = pay_2.PAYRUN_ID
AND pay_1.PAYMENT_BATCH_PERIOD = pay_2.PAYMENT_BATCH_PERIOD
AND pay_1.PAYEE_PARTICIPANT_ID = pay_2.PAYEE_PARTICIPANT_ID
GROUP BY
pay_1.PAYRUN_ID,
pay_1.PAYMENT_BATCH_PERIOD,
pay_1.PAYEE_PARTICIPANT_ID
HAVING COUNT(1) > 1
),
-- FILTERED
PERIOD_MULTI_CURRENCY AS (
SELECT
MULTI_CURR.PARTICIPANT_ID,
MULTI_CURR.PAYRUN_ID,
MULTI_CURR.ORG_ID,
MULTI_CURR.PERIOD_ID,
'Y' AS MULTI_CURR_YN
FROM (
SELECT
PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID,
PD.CURRENCY_CODE AS HOME_CURRENCY_CODE,
PART_PAY.PAYRUN_ID,
PART_PAY.ORG_ID,
PART_PAY.PAYMENT_BATCH_PERIOD AS PERIOD_ID,
SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) AS TotalAmt
FROM CN_TP_PARTICIPANT_PAY_EXP_V PART_PAY
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON PD.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID
AND PART_PAY.SOURCE_EVENT_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, PART_PAY.SOURCE_EVENT_DATE)
WHERE PART_PAY.SOURCE_EVENT_DATE IS NOT NULL
AND PART_PAY.PAYRUN_ID = NVL(:payrun_id, PART_PAY.PAYRUN_ID)
AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID)
AND PART_PAY.PAYMENT_BATCH_PERIOD = NVL(:period_id, PART_PAY.PAYMENT_BATCH_PERIOD)
GROUP BY
PART_PAY.CREDITED_PARTICIPANT_ID,
PD.CURRENCY_CODE,
PART_PAY.PAYRUN_ID,
PART_PAY.ORG_ID,
PART_PAY.PAYMENT_BATCH_PERIOD
HAVING SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) <> 0
) MULTI_CURR
GROUP BY
MULTI_CURR.PARTICIPANT_ID,
MULTI_CURR.PAYRUN_ID,
MULTI_CURR.ORG_ID,
MULTI_CURR.PERIOD_ID
HAVING COUNT(MULTI_CURR.HOME_CURRENCY_CODE) > 1
),
MULTI_CURRENCY_FLAGS AS (
SELECT
NVL(bb_MultiC.PAYRUN_ID, PRD_ML.PAYRUN_ID) AS PAYRUN_ID,
NVL(bb_MultiC.PAYMENT_BATCH_PERIOD, PRD_ML.PERIOD_ID) AS PERIOD_ID,
NVL(bb_MultiC.PAYEE_PARTICIPANT_ID, PRD_ML.PARTICIPANT_ID) AS PARTICIPANT_ID,
NVL(bb_MultiC.BBMultiCurrencyFlag, 'N') AS BBMultiCurrencyFlag,
NVL(PRD_ML.MULTI_CURR_YN, 'N') AS MULTI_CURR_YN
FROM BB_MULTI_CURRENCY bb_MultiC
FULL OUTER JOIN PERIOD_MULTI_CURRENCY PRD_ML
ON bb_MultiC.PAYEE_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID
AND bb_MultiC.PAYRUN_ID = PRD_ML.PAYRUN_ID
AND bb_MultiC.PAYMENT_BATCH_PERIOD = PRD_ML.PERIOD_ID
WHERE NVL(PRD_ML.MULTI_CURR_YN, 'N') = 'Y'
OR NVL(bb_MultiC.BBMultiCurrencyFlag, 'N') = 'Y'
),
-- Materialize base payment data - FILTERED
BASE_PAYMENTS AS (
SELECT
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAYRUN_NAME,
PB.PAY_PERIOD_ID,
PB.PAY_DATE,
PG.PAY_GROUP_ID,
PG.PAY_GROUP_NAME,
P.PLAN_COMPONENT_ID,
PC.INCENTIVE_TYPE,
PS.PAYSHEET_ID,
PS.BONUS_PTD,
PS.PMT_TXN_AMT_DUE,
(
SELECT meaning
FROM cn_lookups
WHERE lookup_type = 'CN_PAYSHEET_STATUS'
AND lookup_code = PS.OBJECT_STATUS
AND enabled_flag = 'Y'
AND lookup_code != 'REVIEWED'
) AS PAYSHEET_STATUS,
P.PAY_AMT_PAY_CURR,
P.HOME_CURRENCY_CODE,
P.PAY_CURRENCY_CODE,
P.SOURCE_EVENT_DATE
FROM CN_TP_PARTICIPANT_PAY_ALL P
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
INNER JOIN CN_PAY_GROUPS_ALL_VL PG
ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID
AND PG.ORG_ID = P.ORG_ID
INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC
ON P.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
INNER JOIN CN_TP_PAYSHEETS_ALL PS
ON PS.PARTICIPANT_ID = P.CREDITED_PARTICIPANT_ID
AND PS.PAYRUN_ID = P.PAYRUN_ID
AND PS.QUOTA_ID IS NULL
WHERE (
(P.SOURCE_EVENT_DATE IS NOT NULL
AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE))
OR
(P.SOURCE_EVENT_DATE IS NULL
AND PB.PAY_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, PB.PAY_DATE))
)
AND PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID)
AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
),
-- Materialize participant detail for date range - FILTERED
PARTICIPANT_DETAIL_DATERANGE AS (
SELECT
PART_DTLS.PARTICIPANT_ID,
PART_HDR.HR_PRIMARY_WORKER_NUMBER,
PART_DTLS.PARTICIPANT_DETAIL_ID,
PART_DTLS.ORG_ID AS PD_ORG_ID,
PART_DTLS.START_DATE AS PD_START_DATE,
PART_DTLS.END_DATE AS PD_END_DATE,
PART_DTLS.COUNTRY_CODE AS HR_Country,
PART_DTLS.COUNTRY_CODE AS Employment_Country,
(
SELECT PARTY_ID
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID
AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID
) AS ANALYST_PARTY_ID,
PART_DTLS.ATTRIBUTE_NUMBER1 AS PD_HR_TI,
PART_DTLS.CURRENCY_CODE AS LATEST_HOME_CURRENCY,
PART_DTLS.ATTRIBUTE_CHAR2 AS HR_Status,
PART_DTLS.ATTRIBUTE_CHAR17 AS SalesRep
FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS
INNER JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR
ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID
AND PART_DTLS.ORG_ID = PART_HDR.ORG_ID
WHERE PART_DTLS.PARTICIPANT_ID = NVL(:participant_id, PART_DTLS.PARTICIPANT_ID)
),
-- Aggregate to USD
PAYMENTS_USD AS (
SELECT
NUMBER_in_USD.ORG_ID,
NUMBER_in_USD.PAYRUN_ID,
NUMBER_in_USD.CREDITED_PARTICIPANT_ID,
NUMBER_in_USD.PAYRUN_NAME,
NUMBER_in_USD.PAY_PERIOD_ID,
NUMBER_in_USD.PAY_DATE,
NUMBER_in_USD.PAY_GROUP_ID,
NUMBER_in_USD.PAY_GROUP_NAME,
NUMBER_in_USD.PAYSHEET_ID,
NUMBER_in_USD.PAYSHEET_STATUS,
NUMBER_in_USD.PAY_AMT_PAY_CURR_USD,
NUMBER_in_USD.paysheet_payout_usd,
NUMBER_in_USD.paysheet_payout_LC,
pd.CURRENCY_CODE LATEST_HOME_CURRENCY
FROM (
SELECT
SPD.ORG_ID,
SPD.PAYRUN_ID,
SPD.CREDITED_PARTICIPANT_ID,
SPD.PAYRUN_NAME,
SPD.PAY_PERIOD_ID,
SPD.PAY_DATE,
SPD.PAY_GROUP_ID,
SPD.PAY_GROUP_NAME,
SPD.PAYSHEET_ID,
SPD.PAYSHEET_STATUS,
SUM(
SPD.PAY_AMT_PAY_CURR *
NVL(
(CASE
WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END),
1
)
) AS PAY_AMT_PAY_CURR_USD,
MAX(
SPD.PMT_TXN_AMT_DUE *
NVL(
(CASE
WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END),
1
)
) AS paysheet_payout_usd,
MAX(SPD.PMT_TXN_AMT_DUE) AS paysheet_payout_LC,
MAX(HOME_CURRENCY_CODE) AS LATEST_HOME_CURRENCY
FROM (
SELECT
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAYRUN_NAME,
PB.PAY_PERIOD_ID,
PB.PAY_DATE,
PG.PAY_GROUP_ID,
PG.PAY_GROUP_NAME,
P.PLAN_COMPONENT_ID,
PC.INCENTIVE_TYPE,
PS.PAYSHEET_ID,
PS.BONUS_PTD,
PS.PMT_TXN_AMT_DUE,
(
SELECT meaning
FROM cn_lookups
WHERE lookup_type = 'CN_PAYSHEET_STATUS'
AND lookup_code = PS.OBJECT_STATUS
AND enabled_flag = 'Y'
AND lookup_code != 'REVIEWED'
) AS PAYSHEET_STATUS,
P.PAY_AMT_PAY_CURR,
PD.CURRENCY_CODE HOME_CURRENCY_CODE,
P.PAY_CURRENCY_CODE,
P.SOURCE_EVENT_DATE
FROM CN_TP_PARTICIPANT_PAY_ALL P
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
INNER JOIN CN_PAY_GROUPS_ALL_VL PG
ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID
AND PG.ORG_ID = P.ORG_ID
INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC
ON P.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
INNER JOIN CN_TP_PAYSHEETS_ALL PS
ON PS.PARTICIPANT_ID = P.CREDITED_PARTICIPANT_ID
AND PS.PAYRUN_ID = P.PAYRUN_ID
AND PS.QUOTA_ID IS NULL
WHERE (
(P.SOURCE_EVENT_DATE IS NOT NULL
AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE))
OR
(P.SOURCE_EVENT_DATE IS NULL
AND PB.PAY_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, PB.PAY_DATE))
)
AND PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID)
AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
) SPD
LEFT JOIN (
SELECT
CR.FROM_CURRENCY,
CR.TO_CURRENCY,
CR.CONVERSION_START_DATE,
CR.CONVERSION_END_DATE,
CR.CONVERSION_RATE
FROM CN_CONVERSION_RATES CR
INNER JOIN CN_CONVERSION_TYPES_TL CT
ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID
WHERE CT.CONVERSION_TYPE_ID = '-1001'
AND CT.LANGUAGE = 'US'
AND CR.TO_CURRENCY = 'USD'
) conversion
ON TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE
AND conversion.CONVERSION_END_DATE
AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE
GROUP BY
SPD.ORG_ID,
SPD.PAYRUN_ID,
SPD.CREDITED_PARTICIPANT_ID,
SPD.PAYRUN_NAME,
SPD.PAY_PERIOD_ID,
SPD.PAY_DATE,
SPD.PAY_GROUP_ID,
SPD.PAY_GROUP_NAME,
SPD.PAYSHEET_ID,
SPD.PAYSHEET_STATUS
) NUMBER_in_USD
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON TRUNC(NUMBER_in_USD.PAY_DATE) BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, DATE '9999-12-31')
AND NUMBER_in_USD.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND NUMBER_in_USD.ORG_ID = PD.ORG_ID
),
-- Convert back to local currency
BG AS (
SELECT
after_group.ORG_ID,
after_group.PAYRUN_ID,
after_group.CREDITED_PARTICIPANT_ID,
after_group.PAYRUN_NAME,
after_group.PAY_PERIOD_ID,
after_group.PAY_DATE,
after_group.PAY_GROUP_ID,
after_group.PAY_GROUP_NAME,
after_group.PAYSHEET_ID,
after_group.PAY_AMT_PAY_CURR_USD,
after_group.paysheet_payout_LC,
after_group.LATEST_HOME_CURRENCY,
after_group.PAYSHEET_STATUS,
TO_CHAR(
1 / (
NVL(
(CASE
WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
),
'FM9999999990.0000'
) AS conversionrate,
TO_CHAR(
after_group.PAY_AMT_PAY_CURR_USD * (
1 / (
NVL(
(CASE
WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
)
),
'FM9999999990.0000'
) AS finalPayout,
TO_CHAR(
after_group.paysheet_payout_usd * (
1 / (
NVL(
(CASE
WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
)
),
'FM9999999990.0000'
) AS finalPayout_paysheet
FROM PAYMENTS_USD after_group
LEFT JOIN CONVERSION_RATES_USD conversion2
ON TRUNC(after_group.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE
AND conversion2.CONVERSION_END_DATE
AND conversion2.FROM_CURRENCY = after_group.LATEST_HOME_CURRENCY
),
-- 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)
),
--BB
-- Step 1: Convert BB to USD (grouped by HOME_CURRENCY_CODE across all old periods)
BB_AMT_USD AS (
SELECT
P.PAYRUN_ID,
P.PAYEE_PARTICIPANT_ID AS CREDITED_PARTICIPANT_ID,
P.PAYMENT_BATCH_PERIOD AS PAY_PERIOD_ID,
LISTAGG(DISTINCT P.HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS HOME_CURRENCY_CODE,
SUM(P.PAY_AMT_PAY_CURR) AS BB_AMOUNT_HOME_CURR,
SUM(
P.PAY_AMT_PAY_CURR *
CASE
WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE NVL(CR_USD.CONVERSION_RATE, 1)
END
) AS BB_AMOUNT_USD,
MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY,
MAX(P.PAY_DATE) AS PAY_DATE,
LISTAGG(DISTINCT P.PAY_PERIOD_ID, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS OLD_PAY_PERIOD_IDS
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
INNER JOIN PARTICIPANT_DETAILS_CACHE PD
ON P.PAYEE_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.PAY_DATE)
INNER JOIN PARTICIPANT_DETAILS_CACHE PDX
ON P.PAYEE_PARTICIPANT_ID = PDX.PARTICIPANT_ID
AND (
(P.SOURCE_EVENT_DATE IS NOT NULL
AND P.SOURCE_EVENT_DATE BETWEEN PDX.START_DATE
AND NVL(PDX.END_DATE, (P.SOURCE_EVENT_DATE)))
OR
(P.SOURCE_EVENT_DATE IS NULL
AND P.PAY_DATE BETWEEN PDX.START_DATE
AND NVL(PDX.END_DATE, P.PAY_DATE))
)
LEFT JOIN CONVERSION_RATES_USD CR_USD
ON P.HOME_CURRENCY_CODE != 'USD'
AND CR_USD.FROM_CURRENCY = PDX.CURRENCY_CODE
AND TRUNC(P.PAY_DATE) BETWEEN CR_USD.CONVERSION_START_DATE
AND CR_USD.CONVERSION_END_DATE
WHERE P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID)
AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)
AND P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD
AND EXISTS (
SELECT 1 FROM BB_MULTI_CURRENCY bb
WHERE bb.PAYEE_PARTICIPANT_ID = P.PAYEE_PARTICIPANT_ID
AND bb.PAYRUN_ID = P.PAYRUN_ID
AND bb.PAYMENT_BATCH_PERIOD = P.PAYMENT_BATCH_PERIOD
)
GROUP BY
P.PAYRUN_ID,
P.PAYEE_PARTICIPANT_ID,
P.PAYMENT_BATCH_PERIOD,
P.HOME_CURRENCY_CODE
),
-- Step 2: Convert each currency's BB from USD to Latest Home Currency
BB_AMT_BY_CURRENCY AS (
SELECT
BB.PAYRUN_ID,
BB.CREDITED_PARTICIPANT_ID,
BB.PAY_PERIOD_ID,
BB.HOME_CURRENCY_CODE,
BB.BB_AMOUNT_HOME_CURR,
BB.BB_AMOUNT_USD,
BB.LATEST_HOME_CURRENCY,
BB.OLD_PAY_PERIOD_IDS,
BB.BB_AMOUNT_USD *
CASE
WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1)
END AS BB_IN_LATEST_HC_NUM,
CASE
WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1)
END AS CONVERSION_RATE_USD_TO_LC
FROM BB_AMT_USD BB
LEFT JOIN CONVERSION_RATES_USD CR_LC
ON BB.LATEST_HOME_CURRENCY != 'USD'
AND CR_LC.FROM_CURRENCY = BB.LATEST_HOME_CURRENCY
AND TRUNC(BB.PAY_DATE) BETWEEN CR_LC.CONVERSION_START_DATE
AND CR_LC.CONVERSION_END_DATE
),
-- Step 3: Aggregate all currencies into single row per participant
BB_AMT AS (
SELECT
PAYRUN_ID,
CREDITED_PARTICIPANT_ID,
PAY_PERIOD_ID,
LISTAGG(HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES,
LISTAGG(
HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000'),
', '
) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES_WITH_AMOUNTS,
MAX(LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY,
MAX(OLD_PAY_PERIOD_IDS) AS OLD_PAY_PERIOD_IDS,
SUM(BB_AMOUNT_USD) AS BB_AMOUNT_USD,
TO_CHAR(
SUM(BB_AMOUNT_USD),
'FM9999999990.0000'
) AS BB_IN_USD,
SUM(BB_IN_LATEST_HC_NUM) AS BB_IN_LATEST_HC_NUM,
TO_CHAR(
SUM(BB_IN_LATEST_HC_NUM),
'FM9999999990.0000'
) AS BB_IN_LATEST_HC,
TO_CHAR(
AVG(CONVERSION_RATE_USD_TO_LC),
'FM9999999990.0000'
) AS BB_CONVERSION_RATE_AVG,
SUM(BB_AMOUNT_HOME_CURR) AS BB_TOTAL_HOME_CURR_SUM,
LISTAGG(
HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000'),
' | '
) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_BREAKDOWN_BY_CURRENCY
FROM BB_AMT_BY_CURRENCY
GROUP BY
PAYRUN_ID,
CREDITED_PARTICIPANT_ID,
PAY_PERIOD_ID
),
-- PTD USD
PTD_EARNINGS_USD AS (
SELECT
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID,
SUM(
P.PAY_AMT_PAY_CURR *
NVL(
(CASE
WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END),
1
)
) AS PTD_EARNINGS_USD,
MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY
FROM CN_TP_PARTICIPANT_PAY_ALL P
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.ORG_ID = PD.ORG_ID
LEFT JOIN CONVERSION_RATES_USD conversion
ON TRUNC(
CASE
WHEN P.SOURCE_EVENT_DATE IS NOT NULL THEN P.SOURCE_EVENT_DATE
ELSE PB.PAY_DATE
END
) BETWEEN conversion.CONVERSION_START_DATE
AND conversion.CONVERSION_END_DATE
AND conversion.FROM_CURRENCY = P.HOME_CURRENCY_CODE
WHERE (
(P.SOURCE_EVENT_DATE IS NOT NULL
AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE))
OR
(P.SOURCE_EVENT_DATE IS NULL
AND PB.PAY_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, PB.PAY_DATE))
)
AND P.PAY_PERIOD_ID = PB.PAY_PERIOD_ID
AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
GROUP BY
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID
),
--PTD earnings MC
PTD_EARNINGS_AMT AS (
SELECT
ptd_usd.PAYRUN_ID,
ptd_usd.CREDITED_PARTICIPANT_ID,
ptd_usd.ORG_ID,
ptd_usd.PAY_PERIOD_ID,
ptd_usd.PTD_EARNINGS_USD,
PDX.CURRENCY_CODE LATEST_HOME_CURRENCY,
TO_CHAR(
ptd_usd.PTD_EARNINGS_USD * (
1 / (
NVL(
(CASE
WHEN PDX.CURRENCY_CODE = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
)
),
'FM9999999990.0000'
) AS PTD_EARNINGS_LC
FROM PTD_EARNINGS_USD ptd_usd
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = ptd_usd.PAYRUN_ID
AND PB.ORG_ID = ptd_usd.ORG_ID
LEFT JOIN (
SELECT * FROM (
SELECT
PB.PAYRUN_ID,
PB.PAY_DATE,
PDX.PARTICIPANT_ID,
PDX.ORG_ID,
PDX.START_DATE,
PDX.END_DATE,
CPV.start_date period_start_date,
CPV.end_date period_end_date,
PDX.CURRENCY_CODE,
PB.PAY_PERIOD_ID,
RANK() OVER (ORDER BY PDX.start_date DESC) rank
FROM CN_TP_PAYRUNS_ALL PB,
CN_SRP_PARTICIPANT_DETAILS_ALL PDX,
CN_PERIODS_VL CPV
WHERE PB.ORG_ID = PDX.ORG_ID
AND PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID)
AND PDX.PARTICIPANT_ID = NVL(:participant_id, pdx.PARTICIPANT_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
AND PB.pay_period_id = CPV.period_id
AND PDX.START_DATE >= CPV.start_date
) a
WHERE rank = 1
) PDX
ON ptd_usd.PAYRUN_ID = PDX.PAYRUN_ID
AND ptd_usd.CREDITED_PARTICIPANT_ID = PDX.PARTICIPANT_ID
AND ptd_usd.ORG_ID = PDX.Org_ID
AND ptd_usd.PAY_PERIOD_ID = PDX.PAY_PERIOD_ID
LEFT JOIN CONVERSION_RATES_USD conversion2
ON TRUNC(PB.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE
AND conversion2.CONVERSION_END_DATE
AND conversion2.FROM_CURRENCY = PDX.CURRENCY_CODE
),
-- Materialize prorated TI - FILTERED
PM_TI AS (
SELECT
PARTICIPANT_ID,
ORG_ID,
PERIOD_ID,
START_DATE,
END_DATE,
Prorated_MonthlyTI
FROM (
SELECT
SPFM.PARTICIPANT_ID,
SPFM.ORG_ID,
SPFM.PERIOD_ID,
SRP_CP.START_DATE,
SRP_CP.END_DATE,
SPFM.OUTPUT_ACHIEVED_ITD AS Prorated_MonthlyTI,
ROW_NUMBER() OVER (
PARTITION BY
SPFM.PARTICIPANT_ID,
PERIOD.ORG_ID,
PERIOD.PERIOD_ID
ORDER BY NVL(SRP_CP.END_DATE, DATE '9999-12-31') DESC
) AS LATEST_TI_RECORD
FROM CN_SRP_PER_FORM_METRICS_ALL SPFM
INNER JOIN CN_FORMULAS_ALL_B Monthly_TI
ON Monthly_TI.FORMULA_ID = SPFM.FORMULA_ID
AND Monthly_TI.ATTRIBUTE9 = 'EmployeeEvents'
AND Monthly_TI.ATTRIBUTE10 = 'MonthlyTI'
INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP
ON SRP_CP.PARTICIPANT_ID = SPFM.PARTICIPANT_ID
AND SPFM.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID
AND SPFM.ORG_ID = SRP_CP.ORG_ID
INNER JOIN CN_PERIOD_DIMS_ALL_TL PERIOD
ON PERIOD.ORG_ID = SPFM.ORG_ID
AND PERIOD.PERIOD_TYPE = 'Monthly'
AND PERIOD.PERIOD_ID = SPFM.PERIOD_ID
WHERE (
PERIOD.END_DATE BETWEEN SRP_CP.START_DATE
AND NVL(SRP_CP.END_DATE, DATE '9999-12-31')
OR
NVL(SRP_CP.END_DATE, DATE '9999-12-31') BETWEEN PERIOD.START_DATE
AND PERIOD.END_DATE
)
AND SPFM.PARTICIPANT_ID = NVL(:participant_id, SPFM.PARTICIPANT_ID)
AND SPFM.PERIOD_ID = NVL(:period_id, SPFM.PERIOD_ID)
)
WHERE LATEST_TI_RECORD = 1
),
-- 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
)
SELECT
-- Build unique record identifier
TO_CHAR(
BG.PAY_PERIOD_ID
|| '-' ||
SUBSTR(
BG.PAYRUN_NAME,
1 + INSTR(BG.PAYRUN_NAME, '_', -1, 2),
(INSTR(BG.PAYRUN_NAME, '_', -1, 1) - INSTR(BG.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || PN.person_number
|| '-' || BG.PAYSHEET_ID
) AS RecordId_c,
-- Basic identifiers
BG.PAYRUN_ID AS PayRunId_c,
BG.PAYSHEET_ID AS PaysheetId_c,
BG.PAY_PERIOD_ID AS PeriodId_c,
PN.person_number AS PersonNumber_c,
PN.PARTICIPANT_NAME AS ParticipantName_c,
BG.CREDITED_PARTICIPANT_ID AS ParticipantId_c,
-- Dates
TO_CHAR(BG.PAY_DATE, 'YYYY-MM-DD') AS PayDate_c,
TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'YYYY-MM-DD') AS PaysheetCutoffDate_c,
TO_CHAR(PAY_CAL.MPA_CUTOFF_START_DT, 'YYYY-MM-DD') AS MPACutoffStartDate_c,
TO_CHAR(PAY_CAL.MPA_CUTOFF_END_DT, 'YYYY-MM-DD') AS MPACutoffEndDate_c,
TO_CHAR(PAY_CAL.PAYROLL_SUBMISSION_END_CST, 'YYYY-MM-DD') AS PayrollSubmissionEndDate_c,
-- Plan and role information
LPC.PLAN_COMPONENT_ID AS PlanComponentId_c,
LPC.PLAN_COMPONENT_name AS PlanComponentName_c,
LPC.INCENTIVE_TYPE AS IncentiveType_c,
LPC.ROLE_NAME AS RoleName_c,
BG.PAY_GROUP_NAME AS PayGroupName_c,
-- Currency information
BG.LATEST_HOME_CURRENCY AS HomeCurrency_c,
-- **MODIFIED: Use DUAL_SNAP instead of parDetails**
DUAL_SNAP.HRStatus_c AS HRStatus_c,
DUAL_SNAP.SalesRP_c AS SalesRP_c,
DUAL_SNAP.EmploymentCountry_c AS EmploymentCountry_c,
-- HR Country and Analyst
parDetails.COUNTRY_CODE AS HRCountry_c,
(
SELECT PARTY_NAME
FROM HZ_PARTIES
WHERE PARTY_ID = parDetails.ANALYST_PARTY_ID
) AS AnalystName_c,
-- HR TI (Target Incentive)
TO_CHAR(parDetails.PD_HR_TI, 'FM9999999990.0000') AS HRTI_c,
-- Prorated Monthly TI
TO_CHAR(
NVL(PM_TI.Prorated_MonthlyTI, 0),
'FM9999999990.0000'
) AS ProratedMonthlyTI_c,
-- Beginning Balance (BB) fields
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_LATEST_HC
ELSE TO_CHAR(
NVL(
SUM(
CASE
WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR
ELSE 0
END
),
0
),
'FM9999999990.0000'
)
END AS BeginningBalance_c,
-- BB in USD
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_USD
ELSE TO_CHAR(
NVL(
SUM(
CASE
WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN
BP.PAY_AMT_PAY_CURR *
NVL(
(CASE
WHEN BP.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE CR_USD.CONVERSION_RATE
END),
1
)
ELSE 0
END
),
0
),
'FM9999999990.0000'
)
END AS BeginningBalanceUSD_c,
-- BB Multi-Currency Flag
NVL(MC_FLAGS.BBMultiCurrencyFlag, 'N') AS BBMultiCurrencyFlag_c,
-- BB Home Currencies (for multi-currency cases)
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_HOME_CURRENCIES
ELSE NULL
END AS BBHomeCurrencies_c,
-- BB Breakdown by Currency
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_BREAKDOWN_BY_CURRENCY
ELSE NULL
END AS BBBreakdownByCurrency_c,
-- Old Pay Period IDs (where BB originated)
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.OLD_PAY_PERIOD_IDS
ELSE LISTAGG(
DISTINCT
CASE
WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN TO_CHAR(BP.PAY_PERIOD_ID)
END,
', '
) WITHIN GROUP (ORDER BY BP.PAY_PERIOD_ID)
END AS OldPayPeriodIds_c,
-- PTD Earnings (Period-to-Date)
CASE
WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN PTD_AMT.PTD_EARNINGS_LC
ELSE BG.finalPayout
END AS PTDEarnings_c,
-- PTD Earnings in USD
CASE
WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN
TO_CHAR(PTD_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000')
ELSE
TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000')
END AS PTDEarningsUSD_c,
-- Period Multi-Currency Flag
NVL(MC_FLAGS.MULTI_CURR_YN, 'N') AS PeriodMultiCurrencyFlag_c,
-- Current Earnings Distribution (CED) = PTD - BB
TO_CHAR(
TO_NUMBER(
CASE
WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN PTD_AMT.PTD_EARNINGS_LC
ELSE BG.finalPayout
END
) -
TO_NUMBER(
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_LATEST_HC
ELSE TO_CHAR(
NVL(
SUM(
CASE
WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR
ELSE 0
END
),
0
),
'FM9999999990.0000'
)
END
),
'FM9999999990.0000'
) AS CED_c,
-- CED in USD
TO_CHAR(
TO_NUMBER(
CASE
WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN
TO_CHAR(PTD_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000')
ELSE
TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000')
END
) -
TO_NUMBER(
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_USD
ELSE TO_CHAR(
NVL(
SUM(
CASE
WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN
BP.PAY_AMT_PAY_CURR *
NVL(
(CASE
WHEN BP.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE CR_USD.CONVERSION_RATE
END),
1
)
ELSE 0
END
),
0
),
'FM9999999990.0000'
)
END
),
'FM9999999990.0000'
) AS CEDUSD_c,
-- BB as % of HR TI
TO_CHAR(
CASE
WHEN NVL(parDetails.PD_HR_TI, 0) = 0 THEN 0
ELSE (
TO_NUMBER(
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_LATEST_HC
ELSE TO_CHAR(
NVL(
SUM(
CASE
WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR
ELSE 0
END
),
0
),
'FM9999999990.0000'
)
END
) / parDetails.PD_HR_TI
) * 100
END,
'FM9999999990.00'
) AS BBOfHRTI_c,
-- Paysheet Status
BG.PAYSHEET_STATUS AS PaysheetStatus_c,
-- Conversion Rate
BG.conversionrate AS ConversionRate_c,
-- Paysheet Payout (Local Currency)
BG.finalPayout_paysheet AS PaysheetPayoutLC_c,
-- Additional debugging/tracking fields
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS QueryExecutionTime_c,
-- Snapshot dates for debugging
TO_CHAR(DUAL_SNAP.PAY_DATE, 'YYYY-MM-DD') AS SnapshotPayDate_c,
TO_CHAR(DUAL_SNAP.PAYSHEET_CUTOFF_DT, 'YYYY-MM-DD') AS SnapshotCutoffDate_c
FROM BG
-- **NEW JOIN: Add PARTICIPANT_DUAL_SNAPSHOT**
LEFT JOIN PARTICIPANT_DUAL_SNAPSHOT DUAL_SNAP
ON DUAL_SNAP.PAYRUN_ID = BG.PAYRUN_ID
AND DUAL_SNAP.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND DUAL_SNAP.ORG_ID = BG.ORG_ID
-- Person Numbers
LEFT JOIN PERSON_NUMBERS PN
ON PN.participant_id = BG.CREDITED_PARTICIPANT_ID
-- Latest Plan Component
LEFT JOIN LATEST_PLAN_COMPONENT LPC
ON LPC.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND LPC.ORG_ID = BG.ORG_ID
AND LPC.PERIOD_ID = BG.PAY_PERIOD_ID
AND LPC.PAYRUN_ID = BG.PAYRUN_ID
-- Calendar
LEFT JOIN PAY_CAL
ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(BG.PAYRUN_NAME)
AND PAY_CAL.PAYGROUP_NAME = UPPER(BG.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.INCENTIVE_TYPE))
-- Participant Details (for date range)
LEFT JOIN PARTICIPANT_DETAIL_DATERANGE parDetails
ON TRUNC(BG.PAY_DATE) BETWEEN parDetails.PD_START_DATE
AND NVL(parDetails.PD_END_DATE, DATE '9999-12-31')
AND BG.CREDITED_PARTICIPANT_ID = parDetails.PARTICIPANT_ID
-- Multi-Currency Flags
LEFT JOIN MULTI_CURRENCY_FLAGS MC_FLAGS
ON MC_FLAGS.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND MC_FLAGS.PAYRUN_ID = BG.PAYRUN_ID
AND MC_FLAGS.PERIOD_ID = BG.PAY_PERIOD_ID
-- Beginning Balance Amount (Multi-Currency)
LEFT JOIN BB_AMT
ON BB_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND BB_AMT.PAYRUN_ID = BG.PAYRUN_ID
AND BB_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
-- PTD Earnings Amount (Multi-Currency)
LEFT JOIN PTD_EARNINGS_AMT PTD_AMT
ON PTD_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND PTD_AMT.PAYRUN_ID = BG.PAYRUN_ID
AND PTD_AMT.ORG_ID = BG.ORG_ID
AND PTD_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
-- Prorated Monthly TI
LEFT JOIN PM_TI
ON PM_TI.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND PM_TI.ORG_ID = BG.ORG_ID
AND PM_TI.PERIOD_ID = BG.PAY_PERIOD_ID
-- Base Payments (for single currency BB calculation)
LEFT JOIN BASE_PAYMENTS BP
ON BP.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND BP.PAYRUN_ID = BG.PAYRUN_ID
AND BP.ORG_ID = BG.ORG_ID
-- Conversion Rates (for single currency BB to USD)
LEFT JOIN CONVERSION_RATES_USD CR_USD
ON BP.HOME_CURRENCY_CODE != 'USD'
AND CR_USD.FROM_CURRENCY = BP.HOME_CURRENCY_CODE
AND TRUNC(BP.PAY_DATE) BETWEEN CR_USD.CONVERSION_START_DATE
AND CR_USD.CONVERSION_END_DATE
GROUP BY
BG.PAYRUN_ID,
BG.PAYSHEET_ID,
BG.PAY_PERIOD_ID,
BG.PAYRUN_NAME,
BG.PAY_DATE,
BG.CREDITED_PARTICIPANT_ID,
BG.ORG_ID,
BG.PAY_GROUP_NAME,
BG.LATEST_HOME_CURRENCY,
BG.PAYSHEET_STATUS,
BG.conversionrate,
BG.finalPayout,
BG.finalPayout_paysheet,
BG.PAY_AMT_PAY_CURR_USD,
PN.person_number,
PN.PARTICIPANT_NAME,
LPC.PLAN_COMPONENT_ID,
LPC.PLAN_COMPONENT_name,
LPC.INCENTIVE_TYPE,
LPC.ROLE_NAME,
PAY_CAL.PAYSHEET_CUTOFF_DT,
PAY_CAL.MPA_CUTOFF_START_DT,
PAY_CAL.MPA_CUTOFF_END_DT,
PAY_CAL.PAYROLL_SUBMISSION_END_CST,
parDetails.COUNTRY_CODE,
parDetails.ANALYST_PARTY_ID,
parDetails.PD_HR_TI,
DUAL_SNAP.HRStatus_c,
DUAL_SNAP.SalesRP_c,
DUAL_SNAP.EmploymentCountry_c,
DUAL_SNAP.PAY_DATE,
DUAL_SNAP.PAYSHEET_CUTOFF_DT,
MC_FLAGS.BBMultiCurrencyFlag,
MC_FLAGS.MULTI_CURR_YN,
BB_AMT.BB_IN_LATEST_HC,
BB_AMT.BB_IN_USD,
BB_AMT.BB_HOME_CURRENCIES,
BB_AMT.BB_BREAKDOWN_BY_CURRENCY,
BB_AMT.OLD_PAY_PERIOD_IDS,
PTD_AMT.PTD_EARNINGS_LC,
PTD_AMT.PTD_EARNINGS_USD,
PM_TI.Prorated_MonthlyTI
) base
) rules;
Comments
Post a Comment