MC-Fixeda and enhanced
select distinct rules.*
,
-- BBAction_c
DECODE(rules.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_1051', -- Pre-approved zero BB for active sale rep
NULL
) AS "BBAction_c",
-- BBReasonCode_c
DECODE(rules.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',-- Pre-approved by the system
NULL ) AS "BBReasonCode_c",
-- BBMPAComment_c (NEW BB/MPA Comment)
DECODE(rules.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%) or is Austria rep. PENDING analyst to review and action.',
NULL
) AS "BBMPAComment_c",
-- MPAReasonCode_c (MPA reason code)
DECODE(rules.Rule_Number,
1, NULL,
3, NULL,
11, NULL,
'MAV_DIR_MPA_CODE_1008'
) AS "MPAReasonCode_c",
-- MPAGenerated_c (MPA Auto prepopulate)
DECODE(rules.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,
11, NULL,
99, (-1)*BeginningBalance_c,
NULL
) AS "MPAGenerated_c",
-- ExpectedPaymentAmount_c (Expected Payout Amount)
DECODE(rules.Rule_Number,
1, NULL,
3, NULL,
11, NULL,
99, TO_NUMBER(RULES.PTDEarnings_c), -- Assuming you have PTDEarnings_c column
NULL -- Zero for all other rules (2,4,5,6,7,8,9,10)
) AS "ExpectedPaymentAmount_c",
-- MPAStatus_c (SCP/SPIF manager's approval)
DECODE(rules.Rule_Number,
1, NULL,
3, NULL,
11, NULL,
'Auto approved'
) AS "MPAStatus_c",
-- RecoverableFlagV1_c (Recoverable MPA? Y/N)
DECODE(rules.Rule_Number,
1, NULL,
3, NULL,
11, NULL,
'Y'
) AS "RecoverableFlagV1_c"
-- , Rule_Number
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 -0.05 AND 0.05
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 = '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) BETWEEN 0 AND 10
AND BASE.IncentiveType_c = 'COMMISSION'
AND BASE.EmploymentCountry_c = 'AT'
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 /*TO_NUMBER(BASE.BeginningBalance_c) != 0
AND*/
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
cspl.participant_id,
papf.person_number,
--hpp.PERSON_NAME PARTICIPANT_NAME
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)
AND ( papf.person_number IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')*/
--AND (P.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
( cspl.participant_id IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND ( cspl.participant_id IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
-- AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)
),
-- 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,
b.ATTRIBUTE_CHAR33
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)
--AND (P.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
( b.PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND ( b.PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
-- AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)
),
-- 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 IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
-- AND 1 =1
AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (P.PAYEE_PARTICIPANT_ID IN IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
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 ( nvl(p.HR_PRIMARY_WORKER_NUMBER, :P_BADGE) IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)*/
AND (P.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (P.PAYEE_PARTICIPANT_ID IN IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
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 ( nvl(p.HR_PRIMARY_WORKER_NUMBER, :P_BADGE) IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND PART_PAY.PAYMENT_BATCH_PERIOD = NVL(:period_id, PART_PAY.PAYMENT_BATCH_PERIOD)*/
AND (PART_PAY.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
AND PART_PAY.PAY_PERIOD_ID = NVL(:period_id, PART_PAY.PAY_PERIOD_ID)
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_CUR_YN,
-- New Field: Returns 'Y' if either flag is 'Y', otherwise 'N'
CASE
WHEN nvl(bb_MultiC.BBMultiCurrencyFlag, 'N') = 'Y' OR NVL(PRD_ML.MULTI_CURR_YN, 'N') = 'Y'
THEN 'Y'
ELSE 'N'
END AS COMBINED_MULTI_CURR_FLAG
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
PS.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
PS.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_PAYSHEETS_ALL PS
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = PS.PAYRUN_ID
AND PB.ORG_ID = PS.ORG_ID
INNER JOIN CN_TP_PARTICIPANT_PAY_EXP_V P
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 ( nvl(p.HR_PRIMARY_WORKER_NUMBER, :P_BADGE) IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)*/
AND (PB.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
AND (P.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (P.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
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)
*/
-- AND (PB.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
(PART_DTLS.PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (PART_DTLS.PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
-- AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
PB.PAY_PERIOD_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_EXP_V 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.PAY_DATE BETWEEN PDX.START_DATE
AND NVL(PDX.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 ( nvl(P.HR_PRIMARY_WORKER_NUMBER, :P_BADGE) IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)*/
AND (PB.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
AND (P.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (P.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
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
)
),
'FM9999999999990.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
)
)
),
'FM99999999999990.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
)
)
),
'FM99999999999990.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,
PB_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,
NVL(PB.REPORT_GROUP,'ALL') PB_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_EXP_V 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)*/
(PB.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
AND (p.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (p.PAYEE_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)
)
WHERE LATEST_PC_RECORD = 1
),
-- Materialize calendar data
PAY_CAL AS (
SELECT UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR010) "PAYMENT_BATCH_NAME",
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR009) "PAYGROUP_NAME",
--PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 "PERIOD_ID",
PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER001 "PERIOD_ID",
PAYRUN_CAL.CURRENCY_CODE "CURRENCY_CODE",
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR008) "INCENTIVE_TYPE",
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR007) COUNTRY,
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP004 "MPA_CUTOFF_START_DT",
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP003 "MPA_CUTOFF_END_DT",
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP011 "PAYSHEET_CUTOFF_START_DT",
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010 "PAYSHEET_CUTOFF_DT",
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008 "PAYROLL_SUBMISSION_END_CST"
FROM MOO_REF_ENTITIES PAYRUN_CAL
WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c'
AND (:period_id IS NULL OR PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER001 = :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,
--P.HOME_CURRENCY_CODE,
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, -- multicurrency
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) KEEP (DENSE_RANK LAST ORDER BY PD.START_DATE) 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 IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
AND (p.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (p.PAYEE_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)
-- 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, 'FM9999999999990.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),
'FM9999999999990.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),
'FM9999999999990.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, -- Multicuurrecy
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,
PB.PAY_DATE,
SUM(
P.PAY_AMT_PAY_CURR *
NVL(
(CASE
WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conv_inner.CONVERSION_RATE
END),
1
)
) AS PTD_EARNINGS_USD,
MAX(PD.CURRENCY_CODE) KEEP (DENSE_RANK LAST ORDER BY PD.START_DATE) AS LATEST_HOME_CURRENCY
FROM CN_TP_PARTICIPANT_PAY_EXP_V 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 conv_inner
ON TRUNC(
CASE
WHEN P.SOURCE_EVENT_DATE IS NOT NULL THEN P.SOURCE_EVENT_DATE
ELSE PB.PAY_DATE
END
) BETWEEN conv_inner.CONVERSION_START_DATE
AND conv_inner.CONVERSION_END_DATE
AND conv_inner.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.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ', 'PMTPLN')
-- AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND (P.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
-- AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID)
AND (p.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (p.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
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,
PB.pay_date
),
--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,
ptd_usd.LATEST_HOME_CURRENCY,
TO_CHAR(
ptd_usd.PTD_EARNINGS_USD * (
1 / (
NVL(
(CASE
WHEN ptd_usd.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
)
),
'FM9999999990.0000'
) AS PTD_EARNINGS_LC
FROM PTD_EARNINGS_USD ptd_usd
LEFT JOIN CONVERSION_RATES_USD conversion2
ON TRUNC(ptd_usd.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE
AND conversion2.FROM_CURRENCY = ptd_usd.LATEST_HOME_CURRENCY
),
-- 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.PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND SPFM.PERIOD_ID = NVL(:period_id, SPFM.PERIOD_ID)
)
WHERE LATEST_TI_RECORD = 1
)
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
|| '-' ||
DECODE(
UPPER(LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE)
)
) AS "RecordName",
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
|| '-' ||
DECODE(
UPPER(LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE)
)
) AS "RecordNumber",
TO_CHAR(BG.PAYRUN_ID) AS "PayrunID_c",
TO_CHAR(BG.PAYSHEET_ID) AS "PaysheetId_c",
--'Regularcycle' AS "PaymentCycle_c",
to_char(BG.PAY_PERIOD_ID) AS "PayPeriodID_c",
to_char(cast(BG.PAY_DATE as date), 'MM/DD/YYYY') AS "PayDate_c",
PN.person_number AS "BadgeIDWorkerNumber_c",
TO_CHAR(BG.CREDITED_PARTICIPANT_ID, 'FM999999999999999999') AS "ParticipantId_c",
to_char(LATEST_PLAN_COMPONENT.START_DATE, 'MM/DD/YYYY') AS "RoleStartDate_c",
to_char(LATEST_PLAN_COMPONENT.END_DATE, 'MM/DD/YYYY') AS "RoleEndDate_c",
BG.PAYRUN_NAME AS "PaymentBatch_c",
BG.PAY_GROUP_NAME AS "PayGroup_c",
NVL(TO_CHAR(
NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0),
'FM999999999990.0000'
), '0') AS "TotalPaysheetAmountInLHC_c",
NVL(TO_CHAR(BG.paysheet_payout_LC, 'FM9999999990.0000'), '0') AS "TotalPaysheetAmount_c",
BG.LATEST_HOME_CURRENCY AS "CurrencyCode",
TO_CHAR(LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID, 'FM999999999999999999') AS "PlanComponentId_c",
NVL(TO_CHAR(PM_TI.Prorated_MonthlyTI, 'FM9999999990.0000'), '0') AS "ProratedTI_c",
NVL(TO_CHAR(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000'), '0') AS "PTDUSD_c",
NVL(BB_AMT.BB_IN_USD, '0') AS "BBUSD_c",
PRD_ML.COMBINED_MULTI_CURR_FLAG AS "MultiCurrency_c",
TO_CHAR(pay_cal.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "PaysheetAutomatedRefreshEndCST_c",
TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_START_DT, 'MM/DD/YYYY HH:MM:SS PM') "PaysheetAutomatedRefreshStartCST_c",
NVL(TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000'), '0') AS "TotalPaysheetAmountUSD_c",
TO_CHAR((
SELECT DISTINCT HR_PRIMARY_WORKER_NUMBER
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
WHERE ANALYST.ORG_ID = BG.ORG_ID
AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID
), 'FM999999999999999999') AS "BadgeIdOfSCPAnalyst_c",
NVL(TO_CHAR((
SELECT DISTINCT B.RESOURCE_PROFILE_ID
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
JOIN JTF_RS_RESOURCE_PROFILES B
ON ANALYST.PARTY_ID = B.PARTY_ID
WHERE ANALYST.ORG_ID = BG.ORG_ID
AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID
), 'FM999999999999999999'), '0') AS "ResProfileIdOfSCPAnalyst_c",
BG.conversionrate AS ConversionRateHomeToUSD_c,
LATEST_PLAN_COMPONENT.plan_component_name AS "PlanComponentName_c",
(
SELECT DISTINCT B.EMAIL_ADDRESS
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
JOIN JTF_RS_RESOURCE_PROFILES B
ON ANALYST.PARTY_ID = B.PARTY_ID
WHERE ANALYST.ORG_ID = BG.ORG_ID
AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID
) AS "OwnerEmailId_c",
LATEST_PLAN_COMPONENT.ROLE_NAME AS RoleName_c,
parDetails.ATTRIBUTE_CHAR2 AS HRStatus_c,
parDetails.COUNTRY_CODE AS EmploymentCountry_c,
-- BB columns - FIXED NVL usage
-- NVL(BB_AMT.BB_HOME_CURRENCIES, '0') AS "BB Home Currencies",
-- NVL(BB_AMT.BB_HOME_CURRENCIES_WITH_AMOUNTS, '0') AS "BB Home Currencies with Amounts",
-- NVL(BB_AMT.BB_BREAKDOWN_BY_CURRENCY, '0') AS "BB Breakdown by Currency",
-- NVL(TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM9999999990.0000'), '0') AS "BB Total (Mixed Currencies Sum)",
NVL(TO_CHAR(BB_AMT.BB_IN_LATEST_HC, 'FM9999999990.0000'), '0') AS BeginningBalance_c,
NVL(TO_CHAR(
(NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0)) - NVL(BG.paysheet_payout_LC, 0),
'FM999999999990.0000'
), '0') AS MCAdjustment_c,
-- BB_AMT.LATEST_HOME_CURRENCY AS "BB Latest Home Currency",
-- BB_AMT.BB_CONVERSION_RATE_AVG AS "BB Avg Conversion Rate",
-- NVL(TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM999999999990.0000'), '0') AS "BEGINNING_BALANCE_LC",
NVL(TO_CHAR(
NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0),
'FM999999999990.0000'
), '0') AS CED_c,
NVL(TO_CHAR(parDetails.ATTRIBUTE_NUMBER1, 'FM9999999990.0000'), '0') AS HRTI_c,
NVL(CASE
WHEN parDetails.ATTRIBUTE_NUMBER1 IS NOT NULL
AND parDetails.ATTRIBUTE_NUMBER1 <> 0
THEN TO_CHAR(
(NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) / parDetails.ATTRIBUTE_NUMBER1) ,
'FM9999999990.0000'
)
ELSE '0'
END, '0') AS BBOfHRTI_c,
LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE AS IncentiveType_c,
NVL(TO_CHAR(
(NVL(TO_NUMBER(BB_AMT.BB_IN_USD), 0) + NVL(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 0)),
'FM9999999990.0000'
), '0') AS CEDUSD_c,
NVL(TO_CHAR(NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0), 'FM9999999990.0000'), '0') AS PTDEarnings_c,
PN.PARTICIPANT_NAME AS "ParticipantName_c",
-- parDetails.COUNTRY_CODE AS "HR Country",
/*
TO_CHAR((
SELECT PARTY_ID
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
WHERE ANALYST.ORG_ID = BG.ORG_ID
AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID
), 'FM999999999999999999') AS "PartyID_c",*/
parDetails.ATTRIBUTE_CHAR17 AS SalesRP_c,
parDetails.ATTRIBUTE_CHAR33 AS ActionCode_c,
-- TO_CHAR(pay_cal.PAYROLL_SUBMISSION_END_CST, 'MM/DD/YYYY HH:MI:SS AM') AS "PayrollSubmissionEndCST_c",
TO_CHAR((SELECT DISTINCT B.RESOURCE_PROFILE_ID
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
JOIN JTF_RS_RESOURCE_PROFILES B ON ANALYST.PARTY_ID = B.PARTY_ID
WHERE ANALYST.ORG_ID = BG.ORG_ID
AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID
), 'FM999999999999999999') as "OraZcxOwner_Id_c"
-- TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS AM') AS PAYSHEET_CUTOFF_START_DT,
-- TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS AM') AS PAYSHEET_CUTOFF_DT,
-- TO_CHAR(PAY_CAL.MPA_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "MPACutoffStartCST_c",
-- TO_CHAR(PAY_CAL.MPA_CUTOFF_END_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "MPACutoffEndCST_c",
-- PRD_ML.BBMultiCurrencyFlag,
-- NVL(TO_CHAR(BG.finalPayout, 'FM9999999990.0000'), '0') AS final_payout_LC,
-- NVL(TO_CHAR(BG.finalPayout_paysheet, 'FM9999999990.0000'), '0') AS final_payout_PS__LC
FROM BG
INNER JOIN MULTI_CURRENCY_FLAGS PRD_ML
ON BG.PAYRUN_ID = PRD_ML.PAYRUN_ID
AND BG.CREDITED_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID
AND BG.PAY_PERIOD_ID = PRD_ML.PERIOD_ID
INNER JOIN LATEST_PLAN_COMPONENT
ON LATEST_PLAN_COMPONENT.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND LATEST_PLAN_COMPONENT.ORG_ID = BG.ORG_ID
AND LATEST_PLAN_COMPONENT.PERIOD_ID = BG.PAY_PERIOD_ID
AND LATEST_PLAN_COMPONENT.PAYRUN_ID = BG.PAYRUN_ID
LEFT JOIN PTD_EARNINGS_USD
ON PTD_EARNINGS_USD.PAYRUN_ID = BG.PAYRUN_ID
AND PTD_EARNINGS_USD.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND PTD_EARNINGS_USD.ORG_ID = BG.ORG_ID
AND PTD_EARNINGS_USD.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
LEFT JOIN PTD_EARNINGS_AMT
ON PTD_EARNINGS_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND PTD_EARNINGS_AMT.ORG_ID = BG.ORG_ID
AND PTD_EARNINGS_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
AND PTD_EARNINGS_AMT.PAYRUN_ID = BG.PAYRUN_ID
LEFT JOIN BB_AMT
ON BB_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND BB_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
AND BB_AMT.PAYRUN_ID = BG.PAYRUN_ID
LEFT JOIN PAY_CAL
ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(LATEST_PLAN_COMPONENT.PAYRUN_NAME)
AND PAY_CAL.PAYGROUP_NAME = UPPER(LATEST_PLAN_COMPONENT.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(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE))
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
INNER JOIN PARTICIPANT_DETAILS_CACHE parDetails
ON BG.PAY_DATE BETWEEN parDetails.START_DATE
AND NVL(parDetails.END_DATE, BG.PAY_DATE)
AND BG.CREDITED_PARTICIPANT_ID = parDetails.PARTICIPANT_ID
INNER JOIN PERSON_NUMBERS PN
ON PN.participant_id = BG.CREDITED_PARTICIPANT_ID
WHERE 1 = 1
-- AND BG.payrun_id = NVL(:payrun_id, BG.payrun_id)
AND (BG.PAYRUN_ID IN (:PAYRUN_ID) OR COALESCE(:PAYRUN_ID,'ALL') = 'ALL')
-- AND BG.CREDITED_PARTICIPANT_ID = NVL(:participant_id, BG.CREDITED_PARTICIPANT_ID)
AND (BG.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND BG.PAY_PERIOD_ID = NVL(:period_id, BG.PAY_PERIOD_ID)
AND (BG.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
) base
) rules
Comments
Post a Comment